topic Re: Get specific variables from all data sets in a library in SAS Programming

Get specific variables from all data sets in a library

SAS_inquisitive — Fri, 30 Aug 2019 16:02:04 GMT

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;

Re: Get specific variables from all data sets in a library

Reeza — Fri, 30 Aug 2019 16:28:23 GMT

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

Re: Get specific variables from all data sets in a library

Tom — Fri, 30 Aug 2019 16:31:37 GMT

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;

Re: Get specific variables from all data sets in a library

SAS_inquisitive — Fri, 30 Aug 2019 16:50:18 GMT

Works great. Thanks, @Tom.

Re: Get specific variables from all data sets in a library

SAS_inquisitive — Fri, 30 Aug 2019 16:55:47 GMT

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

Re: Get specific variables from all data sets in a library

Reeza — Fri, 30 Aug 2019 17:05:54 GMT

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

Re: Get specific variables from all data sets in a library

SAS_inquisitive — Fri, 30 Aug 2019 17:34:21 GMT

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