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;
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;
So you want all observations where id is missing in each of those three data sets?
Yes, I want all observations where id is missing in each of those three data sets
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 . .
Thank you. Any other way without using call execute?
There are several ways to do this. Is there some way you prefer?
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;
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.
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.