Eliminate Cookie-Cutter Code with % wordLoop

Eliminate Cookie-Cutter Codewith %wordLoop David.Abbott@va.gov

Def. of CCC – How It Is Created • Write a short chunk of SAS code • Copy, paste, edit • Copy, paste, edit • Copy, paste, edit • … (until the list in mind is exhausted)

Why diss Cookie-Cutter Code? Now vs. later Competence Excellence vs.

%wordLoop Signature %wordLoop( wordList=<>, contentMacro=<>); Where: wordList => values to loop over, e.g. data set names contentMacro => code applied on each loop

Example Code • Select a subset of observations from each of N datasets • Create N new suitably-named datasets • Subset specified by finderFileDs • Why? subset analysis, data transfer, removal of patients from study

DATA patientDs_subset; MERGE patientDs(in=in1) finderFileDs(in=in2);BY id; IF in1 and in2; RUN; DATA consultDs_subset; MERGE consultDs(in=in1) finderFileDs(in=in2);BY id; IF in1 and in2; RUN; DATA hospStayDs_subset; MERGE hospStayDs(in=in1) finderFileDs(in=in2);BY id; IF in1 and in2; RUN; DATA eventsDs_subset; MERGE eventsDs(in=in1) finderFileDs(in=in2);BY id; IF in1 and in2; RUN; DATA drugsDs_subset; MERGE drugsDs(in=in1) finderFileDs(in=in2);BY id; IF in1 and in2; RUN; Example Cookie-Cutter Code

DATA patientDs_subset; MERGEpatientDs(in=in1) finderFileDs(in=in2);BY id; IF in1 and in2; RUN; DATA consultDs_subset; MERGEconsultDs(in=in1) finderFileDs(in=in2);BY id; IF in1 and in2; RUN; DATA hospStayDs_subset; MERGEhospStayDs(in=in1) finderFileDs(in=in2);BY id; IF in1 and in2; RUN; DATA eventsDs_subset; MERGEeventsDs(in=in1) finderFileDs(in=in2);BY id; IF in1 and in2; RUN; DATA drugsDs_subset; MERGEdrugsDs(in=in1) finderFileDs(in=in2);BY id; IF in1 and in2; RUN; Ex. CCC showing Substitutions

Repeating Pattern DATA <Ds from list>_subset; MERGE <Ds from list>(in=in1) finderFileDs(in=in2); BY id; IF in1 and in2; RUN; Ds list: patientDs consultDs hospStayDs eventDs drugDs

Liabilities of CCC • Cut-paste-edit errors • Bulky and hard to read • Tedious to change implementation • List is hidden • List tedious to change Make a macro of repeating element?

Ex. with Macro Definition %MACRO getSubset(ofDs=); DATA &ofDs._subset; MERGE &ofDs(in=in1) finderFileDs (in=in2); BY id; IF in1 and in2; RUN; %MEND; %getSubset(ofDs=patientDs ); %getSubset(ofDs=consultDs ); %getSubset(ofDs=hospStayDs); %getSubset(ofDs=eventsDs ); %getSubset(ofDs=drugsDs ); Still got cookies! Still list elements are dispersed.

Ex. with %wordLoop %MACRO getSubset(); DATA &word._subset; MERGE &word.(in=in1) finderFileDs(in=in2); BY id; IF in1 and in2; RUN; %MEND; %LET ds=patientDs consultDs hospStayDs eventsDs drugsDs; %wordLoop(wordList=&ds,contentMacro=getSubset( )); Oops! What about sort order?

Again with sort %MACRO getSubset(); proc sort DATA=&word; BY id; RUN; DATA &word._subset; MERGE &word.(in=in1) finderFileDs(in=in2); BY id; IF in1 and in2; RUN; %MEND; %LET ds=patientDs consultDs hospStayDs eventsDs drugsDs; %wordLoop(wordList=&ds,contentMacro=getSubset( )); SQL better?

Again with SQL %MACRO getSubset(); PROC sql; create table &word._subset as select * from &word, finderFileDs where &word..id = finderFileDs.id; QUIT; %MEND; %LET ds=patientDs consultDs hospStayDs eventsDs drugsDs; %wordLoop(wordList=&ds,contentMacro=getSubset( )); Change localized!

%MACRO wordLoop(wordList=, contentMacro=); %LOCAL word cnt; %LET cnt=0; %DO%WHILE(1 eq 1); %LET cnt = %eval(&cnt+1); %LET word= %scan(&wordList, &cnt, %str( )); %IF &word= %THEN %RETURN; %&contentMacro; %END; %MEND wordLoop; %wordLoop code ContentMacro must not %LOCAL word!

Example 2 Code • Recode 99 to “.” for selected variables • 5 variables need to be recoded • 3 variables might have valid 99s • CCC occurs within a DATA step

Ex. 2 Cookie-Cutter Code DATA data99sFixed; SETdataWith99sDs; IF eyeColor eq 99THEN eyeColor = .; IF hairColor eq 99THEN hairColor = .; IF bloodtype eq 99THEN bloodtype = .; IF prevCardiac eq 99THEN prevCardiac = .; IF prevCancer eq 99THEN prevCancer = .; RUN;

Ex. 2 with Array Solution %LET varsWith99 = eyeColor hairColor bloodtype prevCardiac prevCancer; DATA data99sFixedDs; SET dataWith99sDs; ARRAY vars99arr{*} &varsWith99; DO i=1to dim(vars99arr); IF vars99arr(i) eq 99THEN vars99arr(i) = .; END; DROP i; RUN; This works but so does …

Ex. 2 with %wordLoop %LET varsWith99 = eyeColor hairColor bloodtype prevCardiac prevCancer; %MACRO cnvt99s(); IF &word eq 99 THEN &word= .; %MEND; DATA data99sFixed; SETdataWith99sDs; %wordLoop(wordList=&varsWith99, contentMacro=cnvt99s()); RUN;

Misuses of %wordLoop • Avoid use with datasets representing partitions of DATA (e.g. sites) • Combine and processBY site, instead • Avoid use with procswhenBY or CLASS can be used (e.g. PROC MEANS)

Take Aways • Avoid cookie-cutter-code! • %wordLoop() can help you do this • SAS macros allow you to adapt the SAS language to your purposes. David.Abbott@va.gov

Eliminate Cookie-Cutter Code with % wordLoop