BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
t_ar_taat
Quartz | Level 8

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.

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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

4 REPLIES 4
Reeza
Super User

Use a data step?

 

data want;
Set a b indsname=source;
Dset=source;
Run;
t_ar_taat
Quartz | Level 8

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.

PGStats
Opal | Level 21

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
t_ar_taat
Quartz | Level 8
Hi,PGStats
Great! This is what I want.
Have a nice day,t_ar_taat

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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