SQL Union and data Step

[SasPro]

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.

[stat_sas]

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;

[Tom · Accepted solution]

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;

[SasPro]

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,