topic Re: proc sql "union":Way of clarify original dataset in SAS Procedures

proc sql "union":Way of clarify original dataset

t_ar_taat — Mon, 19 Sep 2016 01:32:10 GMT

Hi,all.

Is there any way of making a variable for catch "original dataset" after union statement when proc sql.
Though value which exisit both dataset is replaced to final dataset.
("value which exisit both dataset" is "paper2 b1" in the below example.)

For example

data Dataset1;
input A$ B$ ;
cards;
paper1 a1
paper2 b1
paper2 b1
;
run;

data Dataset2;
input A$ C$;
cards;
paper2 b1
paper3 c1
;
run;

proc sql;
   create table Dataset3 as
   select * from Dataset1
      union
   select * from Dataset2;
quit;

Image of "Dataset3"

I want to get the result like below,in short,variable "XX" have the value of the observation's mother or parent dataset.

Any value is ok(character or number) if we can grasp the diffenrence of parent dataset.

Image of my hoping.

Thank you in advance.

Re: proc sql "union":Way of clarify original dataset

Reeza — Mon, 19 Sep 2016 02:46:57 GMT

Use a data step?

data want;
Set a b indsname=source;
Dset=source;
Run;

Re: proc sql "union":Way of clarify original dataset

PGStats — Mon, 19 Sep 2016 03:18:42 GMT

If you insist on using SQL UNION. Please note that unless you specify UNION CORRESPONDING, variables from both tables are matched simply on their position in the variable list. In your example, B is matched with C.

I suggest to try:

proc sql;
   create table Dataset3 as
   select "Dataset1" as source, A, B from Dataset1
      union corr
   select "Dataset2" as source, A, C as B from Dataset2;
quit;

Re: proc sql "union":Way of clarify original dataset

t_ar_taat — Wed, 21 Sep 2016 03:52:49 GMT

Hi,Reeza

Thank you for your helping.

Actually I insist on using proc sql,but your information is useful.

Because I never hard "indsname".I'll use it soon.Thanks.

Re: proc sql "union":Way of clarify original dataset

t_ar_taat — Wed, 21 Sep 2016 03:55:33 GMT

Hi,PGStats
Great! This is what I want.
Have a nice day,t_ar_taat