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
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
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
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;
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;
Thank you Chris!!
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;
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;
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
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.