Issues with VARNAME when bringing in data

[glcoolj12]

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;

[RW9]

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;

[art297]

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

[Astounding]

If your data set were to contain these variables, would you want to keep them?

CUPID

STUPID

PIDDLE

RAPID

[glcoolj12]

Yes.  

'dataset' is the libname I assigned to the folder that holds all 50+ datasets (.sas7bdat)

[glcoolj12]

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;