Help using Base SAS procedures

proc sql "union":Way of clarify original dataset

Accepted Solution Solved
Reply
Contributor
Posts: 35
Accepted Solution

proc sql "union":Way of clarify original dataset

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"

after_union.JPG

 

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.

after_union_XX1.JPG

after_union_XX2.JPG

 

Thank you in advance.


Accepted Solutions
Solution
‎09-20-2016 11:55 PM
Respected Advisor
Posts: 4,644

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

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;
PG

View solution in original post


All Replies
Super User
Posts: 17,801

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

Use a data step?

 

data want;
Set a b indsname=source;
Dset=source;
Run;
Contributor
Posts: 35

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

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.

Solution
‎09-20-2016 11:55 PM
Respected Advisor
Posts: 4,644

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

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;
PG
Contributor
Posts: 35

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

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

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

Discussion stats
  • 4 replies
  • 376 views
  • 5 likes
  • 3 in conversation