topic SQL Union and data Step in SAS Programming

SQL Union and data Step

SasPro — Sat, 05 Jul 2014 04:15:32 GMT

Hi All,

I was wondering is there any alternate way to use data step to compare and execute SQL unions techinques. I want to write data step to perform SAS sql unions, like except, interest, union

For example: i have two datasets

Data one;
input x A$;
datalines;
1	a
1	a
1	b
2	c
3	v
4	e
6	g
Run;

Data TWO;
input x A$;
datalines;
1	x
2	y
3	z
3	v
5	w

Run;

SQL Except set operator to combine both table vertically. How to get same output in SAS data Step.

proc sql ;

select * from one

except

select * from two;

Quit;

SQL Intersect set operator to combine both table vertically. How to get same output in SAS data Step.

proc sql;
select *   from one
   intersect
select *   from two;

Quit;

Thanks in advance.

Re: SQL Union and data Step

stat_sas — Sat, 05 Jul 2014 04:41:08 GMT

proc sort data=one nodupkey;
by x a;
run;

proc sort data=two nodupkey;
by x a;
run;

data except;
merge one(in=b) two(in=c);
by x a;
if b=1 and c=0;
run;

data intersect;
merge one(in=b) two(in=c);
by x a;
if b and c;
run;

Re: SQL Union and data Step

Tom — Sat, 05 Jul 2014 15:21:23 GMT

Not sure the value of thinking of datastep operations as set operations by it is not hard, but will require that the data is sorted so that the data step can find the matches. (note that SQL will implicitly sort the data to achieve the same thing.

data one;

input x a $ @@;

cards;

1 a 1 a 1 b 2 c 3 v 4 e 6 g

run;

proc sort; by _all_; run;

data two;

input x a $ @@;

cards;

1 x 2 y 3 z 3 v 5 w

run;

proc sort; by _all_; run;

data except;

merge one (in=inone) two (in=intwo);

by _all_ ;

if inone and not intwo;

run;

data intercept;

merge one (in=inone) two (in=intwo);

by _all_ ;

if inone and intwo;

run;

data union;

merge one two ;

by _all_ ;

run;

Re: SQL Union and data Step

SasPro — Tue, 29 Jul 2014 15:04:18 GMT

Hi,

I mean Can we do Same SQL Unions operations by using Set statement? Using merge it may copy all variables but we can use Keep or drop statement. As Set statement is a concatenating operator

so i am looking to perform Unions by SET statement,