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

Hello All,

 

I am trying to create one dataset by combining two dataset. I would like to know how to  use PROC SQL to combine two dataset where two variable have same name. Two dataset i have is as below

 

SAS dataset.JPG

 

and a table i would like to create is as below. I know how to create this table wit data/set joining in method. but i would like to use PROC SQL 

SAS dataset.JPG

 

I right SAS code couple of code but i don't understand that what is wrong in my code.  Can anyone explain me what is wrong in this code and how i can use PROC SQL to create desire table ???

proc sql feedback;
create table ab3 as
select groupa,groupb
where groupa.id=groupb.id and groupa.product=group.product;
quit;
proc sql;
create table ab2 as
select coalesce (groupa.ID, groupb.id)as id, product, size
from groupa full join groupb on groupa.id=groupb.id;
quit;

Thank you

 

1 ACCEPTED SOLUTION

Accepted Solutions
ChrisBrooks
Ammonite | Level 13

What you need is UNION ALL with an ORDER BY statement like so

 

data one;
	infile datalines dlm=",";
	length id $1 product $10;
	input id product;
	datalines;
A,Apple
B,Grapes
B,Orange
C,Banana
E,Apple
;
run;


data two;
	infile datalines dlm=",";
	length id $1 product $10 size $1;
	input id product size;
	datalines;
B,Shirt,S
C,Pants,M
D,jacket,L
E,Tie,M
E,Wallet,L
;
run;

proc sql;
	create table result
	as select *
	from one
	union all
	select *
	from two
	order by id,product;
quit;

View solution in original post

8 REPLIES 8
ChrisBrooks
Ammonite | Level 13

What you need is UNION ALL with an ORDER BY statement like so

 

data one;
	infile datalines dlm=",";
	length id $1 product $10;
	input id product;
	datalines;
A,Apple
B,Grapes
B,Orange
C,Banana
E,Apple
;
run;


data two;
	infile datalines dlm=",";
	length id $1 product $10 size $1;
	input id product size;
	datalines;
B,Shirt,S
C,Pants,M
D,jacket,L
E,Tie,M
E,Wallet,L
;
run;

proc sql;
	create table result
	as select *
	from one
	union all
	select *
	from two
	order by id,product;
quit;
unnati
Obsidian | Level 7

Thank you Chris!!

novinosrin
Tourmaline | Level 20
data one;
input id $ product $;
datalines;
A Apple
B Grapes
B Orange
C Banana
E Apple
;

data two;
input id $ product $ size $;
datalines;
B Shirt S
C Pants M
D Jacket L
E Tie M
E Wallet L
;


proc sql;
create table want as
select * from one
   outer union corr
select * from two
order by id,product;
quit;
novinosrin
Tourmaline | Level 20

More fun:

 

data one;
input id $ product $;
datalines;
A Apple
B Grapes
B Orange
C Banana
E Apple
;

data two;
input id $ product $ size $;
datalines;
B Shirt S
C Pants M
D Jacket L
E Tie M
E Wallet L
;

proc sql;
create table want as
select COALESCEC(a.id, b.id) as id,COALESCEC(a.product, b.product) as product,size
from one a
full join two b
on  a.id=b.id and a.product=b.product
order by 1,2;
quit;
unnati
Obsidian | Level 7

Thank you for your help but now i am trying to understand your code i have created same  code but with little change, can you please explain me why my code not work  and what is difference in process(i know you created some temporary variable but is it necessary to create temp variable )

Following is my code and i get Error message

/* my code*/
 PROC SQL;
 create table me as
 select COALESCEC(Groupa.id, groupb.id) as ID, COALESCEC(groupa.product, groupb.prodcut) as prodcut, size
 from groupa full join groupb 
 on groupa.id=groupb.id and groupa.product=groupb.product
 order by 1,2;
 quit;
 


/*error message*/         
826  proc SQL;
827   create table me as
828   select COALESCEC(Groupa.id, groupb.id) as ID, COALESCEC(groupa.product, groupb.prodcut) as
828! prodcut, size
829   from groupa full join groupb
830   on groupa.id=groupb.id and groupa.product=groupb.product
831   order by 1,2;
ERROR: Column prodcut could not be found in the table/view identified with the correlation name
       GROUPB.
ERROR: Column prodcut could not be found in the table/view identified with the correlation name
       GROUPB.
832   quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds

 

unnati
Obsidian | Level 7
Thank you Novin!!
novinosrin
Tourmaline | Level 20

is this a typo error in your code?. Do you really have a column by the name prodcut rather than product

 

select COALESCEC(Groupa.id, groupb.id) as ID, COALESCEC(groupa.product, groupb.prodcut) as prodcut, size
unnati
Obsidian | Level 7
Thank you so much for bring my attention. Dum me, i have spend my whole day to figure out this code, without realizing spelling error.

Thank you
Unnati

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 8 replies
  • 1778 views
  • 0 likes
  • 3 in conversation