DATA Step, Macro, Functions and more

Issues with VARNAME when bringing in data

Reply
Occasional Contributor
Posts: 18

Issues with VARNAME when bringing in data

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;
Super User
Super User
Posts: 7,942

Re: Issues with VARNAME when bringing in data

Posted in reply to glcoolj12

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;
PROC Star
Posts: 7,467

Re: Issues with VARNAME when bringing in data

Posted in reply to glcoolj12

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

Super User
Posts: 5,497

Re: Issues with VARNAME when bringing in data

Posted in reply to glcoolj12

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

 

CUPID

STUPID

PIDDLE

RAPID

Occasional Contributor
Posts: 18

Re: Issues with VARNAME when bringing in data

Posted in reply to Astounding

Yes.  

 

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

Occasional Contributor
Posts: 18

Re: Issues with VARNAME when bringing in data

[ Edited ]
Posted in reply to 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;

 

Ask a Question
Discussion stats
  • 5 replies
  • 199 views
  • 3 likes
  • 4 in conversation