topic Help in my macro pgm in SAS Programming

Help in my macro pgm

knveraraju91 — Mon, 16 Jul 2018 15:17:55 GMT

Dear,

I am running the below program to find out duplicates in all data sets in sdtm folder. There are (approximately 100) several data sets i think my code works fine but i just want to know if the can be written better way. Please suggest. Thank you

libname sdtm "";
proc contents data= sdtm._all_ noprint out= sdtmall ; run;
data sdtmall1(keep=memname name );
set sdtmall;
run;

%macro duplicates;;
data _null_;
    do i=1 to 100;
         set sdtmall1;
         call symputx(cats('in',i),memname);
         call symputx(cats('seq',i),name);
		
     end;
run;
%do i=1 %to 100;
proc sort data=sdtm.&&in&i. out=_null_ dupout=dups__&&in&i. nodupkey;
       by usubjid &&seq&i.;
  run;
  ODS PDF FILE="..pdf";  

  proc print data=dups__&&in&i. width=min ;
  	   title " Dups in sdtm.&&in&i.";
  	   var usubjid &&seq&i.;
  run;	 
  ods pdf close;
  %end;
%mend;
%duplicates;

Re: Help in my macro pgm

Reeza — Mon, 16 Jul 2018 15:42:18 GMT

Things I'd change:

Store the results into a data set and reporting it after all the comparisons are complete. If you need to change things it's easier and it gives you more flexibility.
Change the hard code of 100 to be dynamic based on the number of your data sets
Clean up - drop datasets after you're done with them, no need to keep them lying around for mistakes. This means you can also reduce the && usage and use temp data sets instead with the same name. In the long run this approach is easier to debug and maintain.
Consider switching a macro loop to a full macro and use CALL EXECUTE to run it. Then you don't have to worry about any of the above as well....

But if it works, it works so you don't actually have to change anything.

Re: Help in my macro pgm

s_lassen — Tue, 17 Jul 2018 08:13:44 GMT

One possibility is to write your code a temporary file and then execute that:

proc contents data= sdtm._all_ noprint out= sdtmall ; run;

filename tempsas temp;
data _null_;
  set sdtmall;
  where upcase(name) ne 'USUBJID';
  file tempsas;
  put 
    'Proc sort data=sdtm.' memname 'out=_null_ dupout=dups__' memname 'nodupkey;' /
    '  by usubjid ' name ';' /
    'run;' /
    'ODS PDF FILE="..pdf";' /
    'Title "Dups in sdtm.' memname '";' /
    'Proc print data=dups__' memname Width=min; /
    '  var usubjid ' name ';'/
    'run;'/
   'ODS PDF close;' /
   ;
run;
%include tempsas /source2;

Before actually submitting the %INCLUDE statement, you may want to have a look at the TEMPSAS file, and perhaps submit the first block of code to see if everything goes well. A lot easier to debug than macros.

Edit note: after submitting the post, I realised that the code (and your original code as well) will generate a lot of statements like

proc sort;
  by usubjid usubjid;
run;

which is probably not what you want, so I added the WHERE clause to avoid that.