topic Re: How to pass the dataset name in set statement from a variable? in SAS Programming

How to pass the dataset name in set statement from a variable?

David_Billa — Fri, 15 Nov 2019 12:10:48 GMT

Assume I've a variable (Var_Name) which has the data as follows. Values of this variable is actually the dataset name. I'd like to know how can I pass that value as a dataset name in SET statement to test for missing values? Datasets Customer, company and Employee are in same structure.

Var_Name

Customer

Company

Employee

Operation which I want to perform now is,

data output;
set <read value from Var_Name variable>; /*like set customer then set company followed by set employee */
if missing (id) then output;
run;

Re: How to pass the dataset name in set statement from a variable?

PeterClemmensen — Fri, 15 Nov 2019 12:12:57 GMT

So you want all observations where id is missing in each of those three data sets?

Re: How to pass the dataset name in set statement from a variable?

David_Billa — Fri, 15 Nov 2019 12:18:21 GMT

Yes, I want all observations where id is missing in each of those three data sets

Re: How to pass the dataset name in set statement from a variable?

PeterClemmensen — Fri, 15 Nov 2019 12:22:57 GMT

Try this.

data one;
input Var_Name $;
datalines;
Customer
Company
Employee
;

data Customer;
id=.; var=1; 
run;

data Company;
id=1; var=1;  
run;

data Employee;
id=.; var=.; 
run;

data _null_;
    set one end=lr;
    if _N_=1 then call execute ('data want; set ');
    call execute(cat(Var_Name, '(where=(missing(id)))'));
    if lr then call execute(';run;');
run;

Result:

id  var
.   1
.   .

Re: How to pass the dataset name in set statement from a variable?

David_Billa — Fri, 15 Nov 2019 12:28:25 GMT

Thank you. Any other way without using call execute?

Re: How to pass the dataset name in set statement from a variable?

PeterClemmensen — Fri, 15 Nov 2019 12:47:43 GMT

There are several ways to do this. Is there some way you prefer?

Re: How to pass the dataset name in set statement from a variable?

Tom — Fri, 15 Nov 2019 14:35:15 GMT

Are the datasets compatible (have same, or at least not conflicting, structures)? Sound like you want to run this program.

data WANT ;
  set Customer Company Employee ;
  where missing(id);
run;

But that would only work if the datasets are compatible.

How long is the list datasets? If the list is short enough you can just put it into a macro variable and use that in the SET statement above. Where is the list of dataset names now? If it is just in your head then just put it into a macro variable.

%let dslist= Customer Company Employee ;
....
data WANT ;
  set &dslist ;
  where missing(id);
run;

If the list of dataset names is in a dateset then you can put the list into a macro variable. Do if it is in a dataset named DSLIST that has a variable named DSNAME then you can make the macro variable.

proc sql noprint;
select dsname into :dslist separated by ' ' from dslist;
quit;

Re: How to pass the dataset name in set statement from a variable?

hashman — Fri, 15 Nov 2019 18:58:10 GMT

@David_Billa:

Compose a list of dsn names, then feed the macro variable containing it into SET with the INDSNAME= option. The value of the latter will indicate in the output data set to which original data sets the records belong:

data customer company employee ;                                                                                                        
  do id = 11, . , 12, 13 ; output customer ; end ;                                                                                      
  do id = 21, 22, 23, 24 ; output company  ; end ;                                                                                      
  do id = 31, . , 23, .  ; output employee ; end ;                                                                                      
  retain data1 1 data2 2 data3 3 ;                                                                                                      
run ;                                                                                                                                   
                                                                                                                                        
data dsn ;                                                                                                                              
  input dsn $32. ;                                                                                                                      
  cards ;                                                                                                                               
customer                                                                                                                                
company                                                                                                                                 
employee                                                                                                                                
;                                                                                                                                       
run ;                                                                                                                                   
                                                                                                                                        
proc sql noprint ;                                                                                                                      
  select dsn into :dsn separated by " " from dsn ;                                                                                      
quit ;                                                                                                                                  
                                                                                                                                        
data miss ;                                                                                                                             
  set &dsn indsname = dsn ;                                                                                                            
  where cmiss (id) ;                                                                                                                    
  DSNAME = dsn ;                                                                                                                          
run ;

Kind regards

Paul D.