topic Re: PROC SQL question in SAS Programming

PROC SQL question

unnati — Wed, 28 Feb 2018 22:28:01 GMT

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

Re: PROC SQL question

ChrisBrooks — Wed, 28 Feb 2018 22:52:14 GMT

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;

Re: PROC SQL question

novinosrin — Wed, 28 Feb 2018 22:52:56 GMT

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;

Re: PROC SQL question

novinosrin — Wed, 28 Feb 2018 23:07:07 GMT

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;

Re: PROC SQL question

unnati — Wed, 28 Feb 2018 23:27:07 GMT

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

Re: PROC SQL question

unnati — Wed, 28 Feb 2018 23:28:46 GMT

Thank you Chris!!

Re: PROC SQL question

unnati — Wed, 28 Feb 2018 23:29:46 GMT

Thank you Novin!!

Re: PROC SQL question

novinosrin — Thu, 01 Mar 2018 03:11:14 GMT

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

Re: PROC SQL question

unnati — Thu, 01 Mar 2018 03:16:45 GMT

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