07-28-2017 10:27 AM
I've created a very basic macro to bring in several datasets. I only want to keep variables in the datasets that contain the word "PID" (it's typically the last 3 characters in the variable name but that's not always the case). I created the following macro, but I get an error saying that "variable VARNAME is not on file". Any help would be greatly appreciated, thanks!
%macro datain (old,new); data &new; set dataset.&old; where VARNAME contains 'PID'; run; %mend;
07-28-2017 10:45 AM
There is no such variable called varname in your dataset, where did you get the idea there was? How are you getting the data, as if you used a good methodology - text file and write datastep - then you would just omit these from the datastep import process.
proc sql; select distinct NAME into :L separated by " " from SASHELP.VCOLUMN where LIBNAME="WORK" and MEMNAME="HAVE" and index(NAME,"PID") > 0; quit; data want; set have (drop=&L.); run;
07-28-2017 10:52 AM
Slight change from the code suggested by @RW9 as that dropped rather than kept the variable(s) of interest, and the following keeps it as a macro:
%macro datain (old,new); proc sql noprint; select name into :keeps separated by ' ' from dictionary.columns where libname eq 'DATASET' and memname eq upcase("&old.") and upcase(name) contains 'EX' ; quit; data &new; set dataset.&old (keep=&keeps.); run; %mend datain; libname dataset '/folders/myfolders'; data dataset.test; set sashelp.class; run; %datain(test,newtest)
Art, CEO, AnalystFinder.com
07-28-2017 02:47 PM - edited 07-28-2017 03:20 PM
Thank you all for your help. I was able to piece together a program that works. Thanks!!
%macro datain (old, new); data &new; set dataset.&old; run; proc sql noprint; select trim(compress(name)) into :keep_vars separated by ' ' from sashelp.vcolumn where libname = upcase('work') and memname = upcase('&new.') and upcase(name) like '%PID%'; quit; %k; data &new; set &new; keep &keep_vars.; run; %mend;