PROC SQL question

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 10
Accepted Solution

PROC SQL question

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

 


Accepted Solutions
Solution
‎02-28-2018 06:28 PM
Valued Guide
Posts: 595

Re: PROC SQL question

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


All Replies
Solution
‎02-28-2018 06:28 PM
Valued Guide
Posts: 595

Re: PROC SQL question

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;
Occasional Contributor
Posts: 10

Re: PROC SQL question

Posted in reply to ChrisBrooks

Thank you Chris!!

PROC Star
Posts: 1,846

Re: PROC SQL question

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;
PROC Star
Posts: 1,846

Re: PROC SQL question

Posted in reply to novinosrin

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;
Occasional Contributor
Posts: 10

Re: PROC SQL question

Posted in reply to novinosrin

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

 

Occasional Contributor
Posts: 10

Re: PROC SQL question

Posted in reply to novinosrin
Thank you Novin!!
PROC Star
Posts: 1,846

Re: PROC SQL question

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
Occasional Contributor
Posts: 10

Re: PROC SQL question

Posted in reply to novinosrin
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
☑ This topic is solved.

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

Discussion stats
  • 8 replies
  • 221 views
  • 0 likes
  • 3 in conversation