topic Re: Issues with VARNAME when bringing in data in SAS Programming

Issues with VARNAME when bringing in data

glcoolj12 — Fri, 28 Jul 2017 14:27:45 GMT

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;

Re: Issues with VARNAME when bringing in data

RW9 — Fri, 28 Jul 2017 14:45:36 GMT

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;

Re: Issues with VARNAME when bringing in data

art297 — Fri, 28 Jul 2017 14:52:23 GMT

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

Re: Issues with VARNAME when bringing in data

Astounding — Fri, 28 Jul 2017 17:08:46 GMT

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

CUPID

STUPID

PIDDLE

RAPID

Re: Issues with VARNAME when bringing in data

glcoolj12 — Fri, 28 Jul 2017 17:31:43 GMT

Yes.

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

Re: Issues with VARNAME when bringing in data

glcoolj12 — Fri, 28 Jul 2017 19:20:45 GMT

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;