BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
David_Billa
Rhodochrosite | Level 12

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;

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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;

View solution in original post

7 REPLIES 7
PeterClemmensen
Tourmaline | Level 20

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

David_Billa
Rhodochrosite | Level 12

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

PeterClemmensen
Tourmaline | Level 20

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
.   .
David_Billa
Rhodochrosite | Level 12

Thank you. Any other way without using call execute?

PeterClemmensen
Tourmaline | Level 20

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

Tom
Super User Tom
Super User

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;
hashman
Ammonite | Level 13

@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.

SAS Innovate 2025: Call for Content

Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!

Submit your idea!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 4484 views
  • 3 likes
  • 4 in conversation