topic Re: Data merge in SAS Programming

Data merge

kajal_30 — Fri, 08 Feb 2019 19:26:07 GMT

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

Re: Data merge

ebowen — Fri, 08 Feb 2019 20:24:52 GMT

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;

Re: Data merge

Cynthia_sas — Fri, 08 Feb 2019 20:37:41 GMT

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

Re: Data merge

kajal_30 — Fri, 08 Feb 2019 21:17:41 GMT

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.

Re: Data merge

ChrisBrooks — Fri, 08 Feb 2019 23:35:44 GMT

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;

Re: Data merge

Cynthia_sas — Fri, 08 Feb 2019 23:51:23 GMT

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

Re: Data merge

Ksharp — Sat, 09 Feb 2019 14:06:13 GMT

<pre>

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

</pre>

Re: Data merge

kajal_30 — Sun, 10 Feb 2019 00:42:40 GMT

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

Re: Data merge

kajal_30 — Sun, 10 Feb 2019 00:42:00 GMT

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;

Re: Data merge

Ksharp — Sun, 10 Feb 2019 11:29:41 GMT

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;