topic Re: How merge two datasets using macro in SAS Data Management

How merge two datasets using macro

aaaaa34 — Tue, 12 Nov 2019 00:31:40 GMT

Hi,

I'm very new in SAS and I have problem which is difficult to me to settle.

I have two datasets.

First is:

name	age
cole	12
emma	13
lisa	15

Second is:

answer

lisa_my_name

what??

How can I merge them by macro only for cases in which answer has values of name variable. The results should be:

answer	age
lisa_my_name	15

Re: How merge two datasets using macro

novinosrin — Tue, 12 Nov 2019 00:09:41 GMT

Hi @aaaaa34 All you need is a simple inner join or in other words a match merge

proc sql;
create table want as
select a.*,b.*
from second a inner join first b
on answer=name;
quit;

Re: How merge two datasets using macro

aaaaa34 — Tue, 12 Nov 2019 00:21:08 GMT

Hi thanks a lot. But I wonder if it's possible to put find condition into do until loop and the make join procedure?
%macro xxx(data_out=);
%let namelist=;
proc sql noprint;
select name
into :namelist separated by "!"
from bbb
;
run;
quit;

data &data_out;
set &data_out;
%let i_1 =1;
%do %until (%qscan(&namelist,&i_1,'!') = %str());
%let zm1=%scan(&namelist,&i_1,'!');
if find(namelist, "&zm1", "i")>0 then answer= answer;
%let i_1=%eval(&i_1+1);
%end;
run;

Re: How merge two datasets using macro

aaaaa34 — Tue, 12 Nov 2019 00:32:50 GMT

I edited my post to show you more complex cases.