Loop though different datasets in library and extract values for a variable

[chetan3125]

Hi All,

I am trying to figure out a way to create a dataset which extracts values of a same variable from all datasets in library.

I have many datasets in the library and want to pick up ones having specific variable to generate dataset with their values. 

Can be explained in the code below. 

Thanks for your help . 

/* Have these 3 datasets in work  */

data   xxx_have1 ;
subject=1 ;output;
environment='prod' ;output;
run;

data   abc_have2 ;
subject=1 ;output;
environment='test' ;output;
run;

data   xyz_have3 ;
subject=1 ;output;
environment='test2' ;output;
run;

/* get ENVIRONMENT variable from above datasets  */

data meta    ; 
set sashelp.vcolumn;
where libname='WORK' and find (upcase (memname) ,'HAVE') and find (upcase (name) ,'ENVIRONMENT') ; 
run;

/* objective - loop through above dataset and create want dataset which has dataset name appended to var name.  */

data  want;
environment_xxx_have1='prod' ; 
environment_abc_have2='test' ;
environment_xyz_have3='test2' ;
run;

[ballardw]

How sure are you that all of the datasets with the variable name of interest have a dataset name of 20 or fewer characters?

Since you are naming a variable starting with "environment_" that takes up 12 of the 32 possible characters of a data set name. If your data set name exceeds 20 characters then you need to provide some rule for what to do.

Is that "want" data set supposed to have all the values of the variable? All the unique values of the variable? And are you sure that the variable you want is always the same type (character or numeric)?

It might also help to say exactly how you expect to use that want data set as it could be considered more than a bit "clunky" for many uses. If the purpose is just to write some report then what the report should look like may be a good idea to show.

[chetan3125]

I can adjust the variable/dataset name. currently it is just for testing. 

Want dataset can have all values for now but unique values are better.  yes. this is a character variable only. 

Yes. the want dataset can be clunky but will modify later to make it more compact. 

I was just thinking that this is easy using call execute. But not sure how. 

[mkeintz]

What ifyou have 10 datasets with the common variable?  In your example that would be 10 variables in the new dataset.

But what if the first dataset has 1 observation, the second has 2 obs, ..., and the tenth has 10 observations?  Does that mean the desired result will have one observation with 55 variables  (1+2+3+...+9+10)?  If so, what would be the naming convention?   Or will the result have 10 variables with multiple observations?