need to dynamically call all available SDTM domain (including SUPP) names into a macro parameter

[ssv]

Hi Everyone,

I am trying to merge SDTM parent domain with supp domain by using macro. Each time I call the macro I need to pass one SDTM domain name and create a New dataset which is the merged dataset(Parent and Supp), Which is making me to call the macro multiple times and which is not dynamic. I need some help to make this macro dynamic which will automatically check and call all the domains available in my library and check if that domain has a SUPP domain or not, If there is a SUPP domain, then I want to merge Parent and SUPP domains and if there is no SUPP domain, it will keep the Parent domain as is.

For Example: if I have DM, SE, SV, AE,  DS domains in my library. Macro need to dynamically call all these 5 available domains one by one automatically available in that library. As DM, AE, DS will have SUPP domains, then it should merge Parent domain (Ex: DM) with SUPP domain (Ex: SUPPDM) into a new dataset named XX_MERG (Ex: DM_MERG). If domain doesn't have SUPP domain (Ex: SE, SV), it should leave as is. 

Please find my macro below and help to make it dynamic.

%macro combine(in=, out=, dst=);

        proc sort data=&in.         out=&in.;
                  by usubjid &in.seq;
         run;

         proc sort data=supp&in.  out=supp&in.;
                   by usubjid idvarval qnam;
          run;

         proc transpose data=supp&in. out=supp&in._ (drop=_NAME_ _LABEL_);
                    by usubjid idvarval;
                    id qnam;
                    idlabel qlabel;
                    var qval;
          run;

         data supp&in._;
                    set supp&in._;
                    &in.seq=input(idvarval, best.);
                   drop idvarval;
         run;

          proc sort data=supp&in._;
                      by usubjid &in.seq;
          run;

         data &out.;
                      merge &in.(in=a) supp&in._;
                      by usubjid &in.seq;
                       if a;
          run;

%mend;

Instead of calling macro multiple times with each domain name per call as shown below. I want macro to check all the domains available in my library and call them one by one  if it has SUPP domain.

%combine(in=dm, out=dm_merg);
%combine(in=ae, out=ae_merg);
%combine(in=ds, out=ds_merg);

[s_lassen]

The simplest solution is to modify the original macro so that it checks if the data exists, and exits if there are no data, e.g.:

%macro combine(in=, out=, dst=);
  %if not %sysfunc(exist(&in.)) %then %do;
     %put Table &in not found;
     %return;
     %end;
  %if not %sysfunc(exist(supp&in.)) %then %do;
     %put Table SUPP&in not found;
     %return;
     %end;   
/* and the rest of the macro as before */

Then you can just call the macro for all the domains, even if some of them do not exist:

%combine(in=dm, out=dm_merg);
%combine(in=se, out=se_merg);
%combine(in=sv, out=sv_merg);
%combine(in=ae, out=ae_merg);
%combine(in=ds, out=ds_merg);

[hashman]

@ssv:

Generate a list of all data set names where a supp domain name matches a parent domain name, then go through the names in the list in a loop. Sort of like:

proc sql noprint ;                                                                                                                                                                                                                                              
  select memname into :supplist separated by " " from dictionary.tables where libname = "WORK" and memname not eqt "SUPP"                                                                                                                                       
  and    memname in (select substr (memname, 5)  from dictionary.tables where libname = "WORK" and memname     eqt "SUPP")                                                                                                                                      
  ;                                                                                                                                                                                                                                                             
quit ;                                                                                                                                                                                                                                                          
                                                                                                                                                                                                                                                                
%macro combine (list=, dst=) ;                                                                                                                                                                                                                                  
  %local i ;                                                                                                                                                                                                                                                    
  %do i = 1 %to sysfunc (countw (&list)) ;                                                                                                                                                                                                                      
    %let in = %scan (&list, &i) ;                                                                                                                                                                                                                               
    proc sort data = &in out = &in ;                                                                                                                                                                                                                            
      by usubjid &in.seq ;                                                                                                                                                                                                                                      
    run ;                                                                                                                                                                                                                                                       
    proc sort data = supp&in  out = supp&in ;                                                                                                                                                                                                                   
      by usubjid idvarval qnam ;                                                                                                                                                                                                                                
    run ;                                                                                                                                                                                                                                                       
    proc transpose data = supp&in out = supp&in._ (drop = _:) ;                                                                                                                                                                                                 
      by usubjid idvarval ;                                                                                                                                                                                                                                     
      id qnam ;                                                                                                                                                                                                                                                 
      idlabel qlabel ;                                                                                                                                                                                                                                          
      var qval ;                                                                                                                                                                                                                                                
    run ;                                                                                                                                                                                                                                                       
    data supp&in._ (drop = idvarval) ;                                                                                                                                                                                                                          
      set supp&in._ ;                                                                                                                                                                                                                                           
      &in.seq = input (idvarval, best.) ;                                                                                                                                                                                                                       
    run ;                                                                                                                                                                                                                                                       
    proc sort data = supp&in._ ;                                                                                                                                                                                                                                
      by usubjid &in.seq ;                                                                                                                                                                                                                                      
    run ;                                                                                                                                                                                                                                                       
    data &in._merg ;                                                                                                                                                                                                                                            
      merge &in (in = _in) supp&in._ ;                                                                                                                                                                                                                          
      by usubjid &in.seq ;                                                                                                                                                                                                                                      
      if _in ;                                                                                                                                                                                                                                                  
    run ;                                                                                                                                                                                                                                                       
  %end ;                                                                                                                                                                                                                                                        
%mend ;                                                                                                                                                                                                                                                         
                                                                                                                                                                                                                                                                
%combine (list=&supplist)             

This way, you don't have to call the same macro a bunch of times and/or determine which domains to use as an argument by eyeballing your library. Leave this tedious and error-prone work to the computer - you're using it anyway.

Kind regards

Paul D.