How to pass list of dataset names into a macro as a loop

[chinna0369]

Hi,

I have a below code but I have around 100 input datasets as a macro variable &dslist. I would like pass all these dataset into below macro called data_comp as a loop. How can I do it?

data _o;
	set sashelp.vtable;
	where libname = upcase("OLD");
	keep memname;
run;
proc sort; by memname; run;

data _n;
	set sashelp.vtable;
	where libname = upcase("NEW");
	keep memname;
run;
proc sort; by memname;run;

data both onlyold onlynew;
	merge _o(in=a) _n(in=b);
	by memname;
	if a and b then output both;
	if a and not b then output onlyold;
	if b and not a then output onlynew;
run;

data both;
	set both onlynew;
run;

Proc SQL NoPrint;	
	Select Distinct(memname) into :dslist Separated by "|" From Both;
	Select Distinct(memname) into :onlynew Separated by "|" From OnlyNew;
Quit;

%put &dslist.;

%let varsdrop=StudySiteId siteid site sitenumber folderid StudyEnvSiteNumber RecordDate MinCreated MaxUpdated SaveTS;
%macro data_comp(inds=, sort=);
proc sort data=old.&inds.(encoding=any) 
out=old1(drop=&varsdrop.); 
by _all_; 
run;

proc sort data=new.&inds.(encoding=any)
out=new1(drop=&varsdrop.); 
by _all_; 
run;

proc sort data=old1; by &sort.; run;
proc sort data=new1; by &sort.; run;

data both_new;
	merge new1(in=a) old1(in=b keep=&sort.);
	by &sort.;
	if a and b;
run;

data only_new;
	length flag $50.;
	merge new1(in=a) old1(in=b keep=&sort.);
	by &sort.;
	if a and not b;
	flag="New";
run;

proc compare b=old1 c=both_new out=check1 outnoequal outbase outcomp outdif;
run;

data check;
	set check1;
	where _type_="COMPARE";
run;

data updated_new;
	length flag $50.;
	merge both_new(in=a) check(in=b keep=&sort.);
	by &sort.;
	if a and b then flag="Updated";
run;

data nlib.&inds.;
	set only_new updated_new;
run;
proc sort; by &sort.; run;
%mend;

%data_comp(inds=ae, sort=recordid);

Thanks,

Adithya

[maguiremq · Accepted solution]

The way I always do that is like this:

PROC SQL;
SELECT
  memname
    INTO :dslist SEPARATED BY "|"
FROM
  dictionary.tables
WHERE
  UPCASE(libname) = "<YOUR LIBRARY HERE>"
  /* NOTE: if your datasets follow a pattern, use the CONTAINS or LIKE operator instead of = */
  AND UPCASE(memname) = "<YOUR DATASET HERE>"
;
QUIT;

/* Then your macro */

%MACRO test ();
%DO i = 1 %TO %SYSFUNC(COUNTW(&dslist., %STR(|)));
%LET ds = %SCAN(&dslist., &i., %STR(|));

/* USE local macro variable &ds. from here on out */
PROC SORT DATA = &ds. OUT = want;
  BY id;
RUN;

%END;

%MEND test;

%test;

Not tested. Let us know if that gets you anywhere.

[chinna0369]

Thanks for your reply @maguiremq , I am using below code and it is not working. Am I missing anything?

data _o;
	set sashelp.vtable;
	where libname = upcase("OLD");
	keep memname;
run;
proc sort; by memname; run;

data _n;
	set sashelp.vtable;
	where libname = upcase("NEW");
	keep memname;
run;
proc sort; by memname;run;

data both onlyold onlynew;
	merge _o(in=a) _n(in=b);
	by memname;
	if a and b then output both;
	if a and not b then output onlyold;
	if b and not a then output onlynew;
run;

data both;
	set both onlynew;
run;

Proc SQL NoPrint;	
	Select Distinct(memname) into :dslist Separated by "|" From Both;
	Select Distinct(memname) into :onlynew Separated by "|" From OnlyNew;
Quit;

%put &dslist.;

%MACRO test;
%DO i = 1 %TO %SYSFUNC(COUNTW(&dslist., %STR(|)));
%LET inds = %SCAN(&dslist., &i., %STR(|));

%let varsdrop=StudySiteId siteid site sitenumber folderid StudyEnvSiteNumber RecordDate MinCreated MaxUpdated SaveTS;
%macro data_comp(inds=, sort=);
proc sort data=old.&inds.(encoding=any) 
out=old1(drop=&varsdrop.); 
by _all_; 
run;

proc sort data=new.&inds.(encoding=any)
out=new1(drop=&varsdrop.); 
by _all_; 
run;

proc sort data=old1; by &sort.; run;
proc sort data=new1; by &sort.; run;

data both_new;
	merge new1(in=a) old1(in=b keep=&sort.);
	by &sort.;
	if a and b;
run;

data only_new;
	length flag $50.;
	merge new1(in=a) old1(in=b keep=&sort.);
	by &sort.;
	if a and not b;
	flag="New";
run;

proc compare b=old1 c=both_new out=check1 outnoequal outbase outcomp outdif;
run;

data check;
	set check1;
	where _type_="COMPARE";
run;

data updated_new;
	length flag $50.;
	merge both_new(in=a) check(in=b keep=&sort.);
	by &sort.;
	if a and b then flag="Updated";
run;

data nlib.&inds.;
	set only_new updated_new;
run;
proc sort; by &sort.; run;
%mend;

%END;

%MEND;

%test;

Thanks,

Adithya

[Kurt_Bremser]

NEVER say "not working" without supplying any details. Show the log, describe where the results do not meet your expectations, but never just say "it doesn't work". Such a statement immediately qualifies you as an idiot, which I positively hope you're not.