Data manipulations in proc sql sas

Pdf File 2,077.56 KByte, 34 Pages

*DATA MANIPULATIONS

IN PROC SQL

Horizontal Table Joins With Matching and/or Non-Matching Conditions

*Horizontal Table Joins in PROC SQL

Advantages of PROC SQL joins:

*It executes on the server and is faster for

large data pulls;

*It does not require sorting or indexing; *It does not require the same name for the

columns in join expressions;

*It permits joining multiple tables in one

step;

*Business Intelligence team understands

PROC SQL code. 2

*Horizontal Table Joins in PROC SQL

Disadvantages of PROC SQL joins:

*It permits joining of the maximum of 256

tables in one step (SAS 9.3);

*Its longer queries sometimes are very

difficult to troubleshoot;

*It can create only one table from a query; *It may not be possible to incorporate

complex logic into the PROC SQL join.

3

*Part I. Joining Tables with Date

Specific Records

Only active accounts are required (Activity=`A') as of October 2, 2013

New accounts should be 4

considered active

*Part I. Joining Tables with Date

Specific Records

The end result should look like this. A nice bonus is to have any kind of product information.

5

*Part I. Joining Tables with Date

Specific Records

/*one side merge using left join*/; proc sql; create table leftmerge1 as select a.*,

b.* from account1 a left join activity1 b on a.acct_id=b.acct_id where a.date='02oct2013'd and b.date='02oct2013'd and activity in('A',' '); quit;

6