Hello,
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;
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;
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
If your data set were to contain these variables, would you want to keep them?
CUPID
STUPID
PIDDLE
RAPID
Yes.
'dataset' is the libname I assigned to the folder that holds all 50+ datasets (.sas7bdat)
Thank you all for your help. I was able to piece together a program that works. Thanks!!
%macro k;
%put &keep_vars.;
%mend k;
%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;
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!
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.