SAS Data, AnalyticalWay

It is a useful exercise to solve the same problems in SAS as we did in SQL using the dataset procedure.

Below is an example is the same as in the SQL section. It is a database containing two tables, customers and purchase order. The Customer table has customerid (the unique identifier for the customer), name (The customer’s name) and age (the customer’s age). The PurchaseOrder table has POID (the unique id for the purchase order), customerid (which refers back to the customer who made the purchase) and Purchase (what the purchase was).

Example:

Customer
CustomerID	Name	Age
1	Joe	23
2	Mika	45
3	Lin	34
4	Sara	56
5	Susan	18

PurchaseOrder
POID	CustomerID	Purchase
1	3	Fiction
2	1	Biography
3	1	Fiction
4	2	Biography
5	3	Fiction
6	4	Fiction

SET

DATA output

SET MyDataBase.Customer;

RUN:

CustomerID	Name	Age
1	Joe	23
2	Mika	45
3	Lin	34
4	Sara	56
5	Susan	18

PROC SORT

DATA output

SET MyDataBase.Customer;

RUN:

PROC SORT DATA = output

BY age;

RUN;

CustomerID	Name	Age
5	Susan	18
1	Joe	23
3	Lin	34
2	Mika	45
4	Sara	56

DATA output

SET MyDataBase.Customer;

IF age eq 18;

RUN:

CustomerID	Name	Age
5	Susan	18

MERGE

PROC SORT DATA = MyDataBase.Customer

BY CustomerID;

RUN;

PROC SORT DATA = MyDataBase.PurchaseOrder

BY CustomerID;

RUN;

DATA output

MERGE MyDataBase.Customer (in = a)

MyDataBase.PurchaseOrder (in = b);

By CustomerID;

if a and b;

RUN:

CustomerID	Name	Age	POID	Purchase
1	Joe	23	2	Biography
1	Joe	23	3	Fiction
2	Mika	45	4	Biography
3	Lin	34	1	Fiction
3	Lin	34	5	Fiction
4	Sara	56	6	Fiction

MERGE with a condition

PROC SORT DATA = MyDataBase.Customer

BY CustomerID;

RUN;

PROC SORT DATA = MyDataBase.PurchaseOrder

BY CustomerID;

RUN;

DATA output

MERGE MyDataBase.Customer (in = a)

MyDataBase.PurchaseOrder (in = b);

By CustomerID;

if a;

RUN:

CustomerID	Name	Age	POID	Purchase
1	Joe	23	2	Biography
1	Joe	23	3	Fiction
2	Mika	45	4	Biography
3	Lin	34	1	Fiction
3	Lin	34	5	Fiction
4	Sara	56	6	Fiction
5	Susan	18	NULL	NULL

FIRST (Group BY)

proc freq data = mydata_lkup;

tables col2;

______________or__________________

PROC SORT DATA = output;

BY CustomerID;

RUN;

DATA outputSummary;

Do UNTIL (LAST.CustomerID);

SET output ;

BY CustomerID;

orders = orders + 1;

IF LAST.CustomerID THEN OUTPUT;

END;

KEEP Name Age Orders;

RUN;

Name	Age	Orders
Joe	23	1
Mika	45	2
Lin	34	2
Sara	56	1

UPDATE

DATA Customer;SET Customer;

If CustomerID = 1

Age = 26;

Run;

Customer
CustomerID	Name	Age
1	Joe	26
2	Mika	45
3	Lin	34
4	Sara	56
5	Susan	18

INSERT

DATA temp;

DO i = 1 to 1;

CustomerID = 6;

Name = Terry ;

Age = 50;

OUTPUT;

END;

RUN;

DATA Customer;

SET Customer temp;

RUN;

Customer
CustomerID	Name	Age
1	Joe	23
2	Mika	45
3	Lin	34
4	Sara	56
5	Susan	18
6	Terry	50

DELETE

DATA Customer;SET Customer;

If CustomerID = 1 DELETE;

Run;

Customer
CustomerID	Name	Age
2	Mika	45
3	Lin	34
4	Sara	56
5	Susan	18