1.

What are the different joins in proc SQL and unlike data step why SQL don’t require sorting before joins?

Answer»

SET and Merge are two very vital statements in SAS. Let US UNDERSTAND by taking 2 tables ‘one’ and ‘two’ and perform SET and Merge Operation on them.

Code -

 DATA one;
  INPUT id v1 v2;
  DATALINES;
  1 10 100
  2 15 150
  3 20 200
  ;
  PROC SORT Data=one;
   BY id;
 RUN;

Output

Obs
id
v1
v2
1
1
10
100
2
2
15
150
3
3
20
200



Code -

DATA two;

  INPUT id v3 v4;

  DATALINES;

  1 1000 10000

  2 1500 15000

  3 2000 20000

  4 800 30000

  ;

 PROC SORT Data=two;

  BY id;

 RUN;

Output

Obs
id
v3
v4
1
1
1000
10000
2
2
1500
15000
3
3
2000
20000
4480030000

The SET statement is the main tool to read observations from a SAS data set. The basics are so obvious you might not EVEN consider them. Just SET and a data set name and there you go. Through SET statement the dataset gets appended while with merge the two datasets get merged on the basis of ID and extra variables are created if needed. Example below.

Code -

DATA TestSet;

  Set one two;

RUN;

Output

Obs
id
v1
v2
v3
v4
1110100..
2215150..
3320200..
41..100010000
52..150015000
63..200020000
74..80030000

Code -          

 DATA TestMerge;

  MERGE one two;

  BY id;

 PROC PRINT DATA= TestMerge; title "TestMerge";

 RUN;

Output

Obs
id
v1
v2
v3
v4
1110100100010000
2215150150015000
3320200200020000
44..80030000


Discussion

No Comment Found

Related InterviewSolutions