topic Help Merging Datasets in New SAS User

Help Merging Datasets

JJ_83 — Wed, 20 Mar 2024 18:33:29 GMT

I haven't used SAS in a while so this is probably a simple request, but I am having issues creating the dataset I want.

data have1;
input id group $;
cards;
1 a
1 b
1 c
;
run;

data have2;
input id samplevalue;
cards;
1 0.25
1 0.50
1 0.75
;
run;

data want;
input id samplevalue group $;
cards;
1 0.25 a
1 0.50 a
1 0.75 a
1 0.25 b
1 0.50 b
1 0.75 b
1 0.25 c
1 0.50 c
1 0.75 c
;
run;

Thank you!

Re: Help Merging Datasets

ballardw — Wed, 20 Mar 2024 18:47:15 GMT

Not the only way. Proc SQL will allow joins on multiple values of matching variables where a data step merge typically doesn't yield the result desired when there are multiples of BY variables in both data sets.

proc sql;
   create table want as
   select a.*,b.samplevalue
   from have1 as a
        full join
        have2 as b
        on a.id=b.id
   order by a.id, a.group, b.samplevalue
   ;
quit;

Re: Help Merging Datasets

veronicasalmaso — Wed, 20 Mar 2024 18:48:34 GMT

Hello! You can do so with a simple proc sql.

Select the variables you want from each dataset and merge the datasets by a variable that both datasets have (in this case that's the id)

proc sql;
    create table want as
    select a.id, b.samplevalue, a.group
    from have1 as a, have2 as b
    where a.id = b.id;
quit;

Hope this helps!

Re: Help Merging Datasets

JJ_83 — Wed, 20 Mar 2024 19:54:06 GMT

Thank you so much, this is exactly what I needed and it works perfectly!