SAS Data

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

 

IF

 

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