Get specific variables from all data sets in a library

[SAS_inquisitive]

Hello,

I am trying to get specified variables from all data sets in a library. The code shown is just for one data set. I wonder if this can be automated using CALL EXECUTE or by other means for all data sets.

proc sql noprint;
	select name into: list_var separated by ', '
	from dictionary.columns
	where upcase(libname) = 'X' and upcase(memname) = 'Y' and (strip(upcase(name)) like '%DAY' or strip(upcase(name)) like '%MON' or strip(upcase(name)) like '%YR');
quit;

%put &list_var;

proc sql noprint;

	create table t1 as
	select &list_var
		from X.Y
	;
quit;

[Reeza]

I wonder if this can be automated using CALL EXECUTE or by other means for all data sets.

Yes, it can be automated.

Use SASHELP.VTABLE which as the list of tables in each library similar to dictionary.columns. 

Personally I would recommend using PROC APPEND with a KEEP statement instead. This copies the data set and doesn't do it line by line so would be the fastest approach IMO. 

proc append base=newName data=oldName (keep = &list_var.);
run;

FYI - if you had used a naming convention that relied on prefixes rather than suffixes this would be an incredibly trivial solution since you could use short cuts. 

Here's a tutorial on doing this with macros. The last step of using CALL EXECUTE shows how to make it data driven.

https://github.com/statgeek/SAS-Tutorials/blob/master/Turning%20a%20program%20into%20a%20macro.md

[SAS_inquisitive]

@Reeza. I was trying to create individuals data sets containing only specified variables for respective data sets in a library.

[Reeza]

---

@SAS_inquisitive wrote:
@Reeza. I was trying to create individuals data sets containing only specified variables for respective data sets in a library.

---

That is what this code does, did you run the code and try it?

Then generating that code dynamically from a data step is trivial. 

[SAS_inquisitive]

@Reeza, I will try that as well. Thanks !

[Tom · Accepted solution]

You will need to make some decisions about what code you want to create.  

First get the data.

proc sql noprint;
create table varlist as
  select libname,memname,varnum,name
  from dictionary.columns
  where libname=%upcase('X')
   and (upcase(name) like '%DAY' 
     or upcase(name) like '%MON'
     or upcase(name) like '%YR'
       )
  order by libname,memname,varnum
;
quit;

Then use the data to generate code. For example here is code to make the subsets into WORK library with the same member name as the dataset had in the X library.

filename code temp;
data _null_;
  file code;
  set varlist;
  by memname;
  if first.memname then put
    'data work.' memname ';'
  / '  set ' libname +(-1) '.' memname '(keep=' @
  ;
  put name @;
  if last.memname then put 
    ');'
  / 'run;'
  ;
run;

Then run the code

%include code / source2;

[SAS_inquisitive]

Works great. Thanks, @Tom.