Data merge

[kajal_30]

Hi Channel ,

I have a data set as 

data one ;

set two three;

run;

I want to put this into proc SQL

data set two has columns a,b,c,d,e

data set three has columns a,b,x,y,z,m

thanks in advance 

kajal

[ebowen]

If you just want to stack the datasets, I would say using the data step code you have above or proc append is going to be easier and faster. But if you want to use proc sql, you can use the INSERT and SELECT statements:

proc sql;

INSERT INTO one

SELECT * from two;

quit;

This requires all of the columns to be the same. You can also choose which columns from the second dataset to insert into their corresponding columns in the first dataset:

INSERT INTO one (a,b)

SELECT a,b FROM two;

quit;

[Cynthia_sas]

Hi:

  I'm not sure what you mean by your question. Your posting asks about a MERGE, but your code shows a SET. I don't understand what you mean by "put this into PROC SQL".

  For example, with some FAKE data, this is what your SET is doing.

in the above output, the yellow highlighted output is from data set TWO and the green highlights are from data set THREE. A SET statement will concatenate or "stack" data sets vertically. But a MERGE, like an SQL JOIN will bring the data sets together horizontally.

  Can you clarify your question??

Cynthia

[kajal_30]

Thanks for reaching out in short 

I need exactly the same results using proc SQL as we are getting using set statement as shown by you 

hope this helps.

[ChrisBrooks]

Hi @kajal_30 you can do it with Proc SQL but it's a little more long-winded than @Cynthia_sas solution.

In order to append files in SQL you can use UNION or UNION ALL but the column names must be identical in both tables. In your case they aren't so we need to select missing values (dots) as the missing column names from the respective tables. Here is the code using @Cynthia_sas data sets

data two;
	infile datalines;
	input a $ b c d e;
return;
datalines;
AA 1 2 3 4
BB 11 12 13 14
BB 15 16 17 18
;
run;

data three;
	infile datalines;
	input a $ b x y z m;
return;
datalines;
AA 21 22 23 24 25
AA 26 27 28 29 30
BB 31 32 33 34 35
BB 36 37 38 39 40
;
run;

proc sql;
	create table one as
	select a, b, c, d, e, . as x, . as y, . as z, . as m
	from two
	union all
	select a, b, . as c, . as d, . as e, x, y, z, m
	from three;
quit;

[Cynthia_sas]

Hi:

 Not sure why you need SQL when you have very elegant SET syntax. However, you'd need to use OUTER UNION CORRESPONDING with SQL:

Cynthia

[kajal_30]

thanks a lot for ur help could you also please advice of the second issue as follows i need to convert this to sql as well

data merge (drop = w);

merge x (in = a rename = ( new = old)) 

y (in= b) ;

by t ;

if a and b then d = "e" ;

if a and not b then d = "f" ;

if b and not a then d = "g" ;

run;

[Ksharp · Accepted solution]

<pre>

proc sql;
create table want as
select *
from sashelp.class
outer union corr
select *
from sashelp.heart;
quit;

</pre>

[kajal_30]

Thanks a lot sir but got stuck in second merge could you please advice 

data merge (drop = w);

merge x (in = a rename = ( new = old)) 

y (in= b) ;

by t ;

if a and b then d = "e" ;

if a and not b then d = "f" ;

if b and not a then d = "g" ;

run;

please convert this to sql 

[Ksharp]

You should use JOIN .

proc sql;

create table want as

select *,case when not missing(y.t) and not missing(x.t) then 'e'

 when missing(y.t) then 'f'

 when missing(x.t) then 'g'

 else ' '

end as flag

 from x(rename = ( new = old)) full join y

  on x.t=y.t

;

quit;