DATA Step, Macro, Functions and more

SQL Union and data Step

Accepted Solution Solved
Reply
Contributor
Posts: 22
Accepted Solution

SQL Union and data Step

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;
1a
1a
1b
2c
3v
4e
6g
Run;

Data TWO;
input x A$;
datalines;
1x
2y
3z
3v
5w
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.


Accepted Solutions
Solution
‎07-05-2014 11:21 AM
Super User
Super User
Posts: 7,076

Re: SQL Union and data Step

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;

View solution in original post


All Replies
Trusted Advisor
Posts: 1,230

Re: SQL Union and data Step

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;

Solution
‎07-05-2014 11:21 AM
Super User
Super User
Posts: 7,076

Re: SQL Union and data Step

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;

Contributor
Posts: 22

Re: SQL Union and data Step

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,

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 1469 views
  • 4 likes
  • 3 in conversation