DATA Step, Macro, Functions and more

how can i automate list of variables and call them in proc report

Accepted Solution Solved
Reply
Regular Contributor
Posts: 161
Accepted Solution

how can i automate list of variables and call them in proc report

I have a list of variables like PRC1, PRC2, SRC1 and SRC2. can i macrovise it so that if in some time there is no SRC2 and only have  PRC1, PRC2, SRC1 - i can use in proc report


Accepted Solutions
Solution
‎08-07-2017 08:20 AM
PROC Star
Posts: 7,487

Re: how can i automate list of variables and call them in proc report

Here is another way that simply removes any variables that aren't on your list:

%macro mylist(dataset);
  %global namelist;

  %if %sysfunc(countw(&dataset.)) eq 2 %then %do;
    %let libnme=%scan(&dataset.,1);
    %let dataset=%scan(&dataset.,2);
  %end;
  %else %do;
    %let libnme=work;
  %end;

  proc sql noprint;
    select name into :namelist
      separated by ' '
        from dictionary.columns
          where libname=%upcase("&libnme.") and
                memname=%upcase("&dataset.") and
                %upcase(name) in ('PRC1', 'PRC2', 'SRC1', 'SRC2')
    ;
  quit;
%mend mylist;

data test;
  input PRC1 PRC2 SRC1;
  cards;
1 2 3
4 5 6
;

%mylist(test);

proc print data=test;
  var &namelist.;
run;

Art, CEO, AnalystFinder.com

 

View solution in original post


All Replies
PROC Star
Posts: 763

Re: how can i automate list of variables and call them in proc report

Where do you have this list of variables? In a dataset? Please elaborate further on what you want us to do Smiley Happy

Super User
Super User
Posts: 7,981

Re: how can i automate list of variables and call them in proc report

Your question is a bit vague.  As always however there is no need to go down the route of macros, loops over lists of variables etc.  Base SAS provides the answer:

proc report data=have;
  columns _all_;
run;

If SRC2 is not in the dataset, it doesn't get reported, if it is then it does.

Super User
Super User
Posts: 7,074

Re: how can i automate list of variables and call them in proc report

Here is one way to go from a list of potential variable names to a list of actual variable names.

%let dsn=sashelp.class ;
%let varlist=PRC1 PRC2 SRC1 SRC2 AGE h:;
%let optsave=%sysfunc(getoption(dkricond));
options dkricond=nowarn;
proc transpose data=&dsn(obs=0 keep=&varlist) ;
  var _all_;
run;
options dkricond=&optsave;
proc sql noprint ;
%let newlist=;
select _name_ into :newlist separated by ' '
from &syslast;
drop table &syslast;
quit;
%put &=newlist;
NEWLIST=Age Height

So basically it works by eliminating the warnings about variables listed in KEEP statement not existing and then using PROC TRANSPOSE with OBS=0 and KEEP= options to generate a dataset with just the _NAME_ column.  It then uses PROC SQL to generate the list of actual names into a new macro variable and deletes the dataset it created.

Solution
‎08-07-2017 08:20 AM
PROC Star
Posts: 7,487

Re: how can i automate list of variables and call them in proc report

Here is another way that simply removes any variables that aren't on your list:

%macro mylist(dataset);
  %global namelist;

  %if %sysfunc(countw(&dataset.)) eq 2 %then %do;
    %let libnme=%scan(&dataset.,1);
    %let dataset=%scan(&dataset.,2);
  %end;
  %else %do;
    %let libnme=work;
  %end;

  proc sql noprint;
    select name into :namelist
      separated by ' '
        from dictionary.columns
          where libname=%upcase("&libnme.") and
                memname=%upcase("&dataset.") and
                %upcase(name) in ('PRC1', 'PRC2', 'SRC1', 'SRC2')
    ;
  quit;
%mend mylist;

data test;
  input PRC1 PRC2 SRC1;
  cards;
1 2 3
4 5 6
;

%mylist(test);

proc print data=test;
  var &namelist.;
run;

Art, CEO, AnalystFinder.com

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 77 views
  • 1 like
  • 5 in conversation