topic Re: merge in SAS Programming

merge

HeatherNewton — Thu, 10 Mar 2022 10:45:23 GMT

data fruit;
  input up down sub_product fav $;
datalines;
2 5	apple	applepie
3 2 lemon	lemontart
4 0 melon	melonmilk
7 11 lemon	lemontart
19 33 apple	applejuice
run;

data box;
input up down anyy okok $;
3 6	app	appl
5 9 lem	lemo
4 0 mel	melilk
7 11 lem	lemotrt
21 33 appl	appleju


data new;
merge fruit box;
by up down;

I am merging two data sets by two variables how can I write it in terms of sql? should I use full join?

Re: merge

Kurt_Bremser — Thu, 10 Mar 2022 11:38:16 GMT

I had to fix BOTH of your DATA steps. Please test your code before posting it, so we don't have to do it for you. See it as basic courtesy with regards to those who are intended to help you.

data fruit;
input up down sub_product $ fav $;
datalines;
2 5 apple applepie
3 2 lemon lemontart
4 0 melon melonmilk
7 11 lemon lemontart
19 33 apple applejuice
;

data box;
input up down anyy $ okok $;
datalines;
3 6 app appl
5 9 lem lemo
4 0 mel melilk
7 11 lem lemotrt
21 33 appl appleju
;

proc sql;
create table want as
  select
    coalesce(t1.up,t2.up) as up,
    coalesce(t1.down,t2.down) as down,
    t1.sub_product,
    t1.fav,
    t2.anyy,
    t2.okok
  from fruit t1 full join box t2
  on t1.up = t2.up and t1.down = t2.down
;
quit;

But always keep in mind that SQL behaves differently from a data step when you have a many-to-many relationship.

Re: merge

HeatherNewton — Sat, 12 Mar 2022 05:53:57 GMT

thanks, I tried but they are not merging..

anyway what does it mean to merge by two variables?

Re: merge

HeatherNewton — Sat, 12 Mar 2022 06:00:49 GMT

seem missing something.. fav column is showing okok

Re: merge

Kurt_Bremser — Sat, 12 Mar 2022 06:34:20 GMT

Your result does not show column okok, and it has less observations than mine.

Post the whole code you ran.

Re: merge

HeatherNewton — Sat, 12 Mar 2022 06:44:50 GMT

data fruit;
input up down sub_product $ fav $;
datalines;
2 5 apple applepie
3 2 lemon lemontart
4 0 melon melonmilk
7 11 lemon lemontart
19 33 apple applejuice
;

proc sort data=fruit;
run;

data box;
input up down anyy $ okok $;
datalines;
3 6 app appl
5 9 lem lemo
4 0 mel melilk
7 11 lem lemotrt
21 33 appl appleju
;

proc sort data=box;
run;
proc sql;
create table want as
  select
    coalesce(t1.up,t2.up) as up,
    coalesce(t1.down,t2.down) as down,
    t1.sub_product,
    t1.fav,
    t2.anyy,
    t2.okok
  from fruit t1 full join box t2
  on t1.up = t2.up and t1.down = t2.down
;
quit;

results:

here you go...

Re: merge

Kurt_Bremser — Sat, 12 Mar 2022 07:26:51 GMT

Your picture shows incoming dataset fruit, not the resulting table want.

PS to view the correct dataset, use the drop-down of the Output tab.

Re: merge

HeatherNewton — Sat, 12 Mar 2022 07:31:27 GMT

thanks I now get it the above in results, but what I get from output is the below which is a mixture of two tables if we look at the what is circled.. and also why the table is displayed in output, supposed all data set will show in output?

Re: merge

Kurt_Bremser — Sat, 12 Mar 2022 07:58:25 GMT

Your second picture shows the table view of the fruit dataset. The first looks like a PROC PRINT of dataset want, which is shown in the Results tab.

You must select the WANT dataset in the table viewer (since all three datasets were created during a single submit, all will show up there).