Desktop productivity for business analysts and programmers

Using a macro var list within another macro

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

Using a macro var list within another macro

 

I was wondering if I could get SAS code to put a list of values (created into a SQL macro) into a SAS macro.  For example below, I want to format all of the date variables in a dataset. I'm in SAS EG (I believe on 9.3)

 

1) Create a list of variables named "var_list"

 

 

proc sql;
   select distinct date_variables into : var_list separated by ' '
   from dataset
;quit;

 This creates a list of date variables, for example:

 

date_1, date_2, … , date_n

 

2) Set up your macro to format a new variable (with ‘_fmt’ at the end, then rename it as original variable name)

%macro dateformat(var);
   &var._fmt = input(&var., MMDDYY10.) ;
   drop &var.; 
   rename &var._fmt = &var.;
%macro;

3) Now I want to put all the variables created in step 1) into step 2) in one step instead of doing this multi-step process:

%dateformat(date_1);

%dateformat(date_2);

.

.

.

%dateformat(date_n);

 

Is there a way to do just one macro for the entire list?  I’m guessing it something like %dateformat(var_list)? But that doesn’t work.

 

Thanks.


Accepted Solutions
Solution
‎03-21-2017 09:18 AM
Respected Advisor
Posts: 3,786

Re: Using a macro var list within another macro

[ Edited ]

mikepep21 wrote:

Hello,

 

My example of the variables (date1, date2, ..., daten) should actually be more like (date1, second_date, the_third_date, dt_exited...).  In other words, the variables wouldn't necessarily end with a number nor look the same.  Would this affect the code you have?  Sorry I haven't had a chance to try it out, I changed jobs and waiting to get a SAS license. 

 

What I'm really looking for is to have some sort of loop in a data step, not necessarily just formats  

 

Thanks.


You create a blank delimited list of variable names with PROC SQL INTO.  This list of names when passed to the macro will be scanned to generate the code to create the new variable.  One thing that could be a problem is your macro used the old-name+_FMT as the name of the new variable which could depending on the length of old name produce an invalid SAS name.

 

This modification uses _&i as the temporary name.

 

%macro dateformat(arg);
   %local i var;
   %let i = 1;
   %let var = %scan(&arg,&i,%str( ));
   %do %while(%superq(var) ne);
      _&i = input(&var., MMDDYY10.) ;
      drop &var.; 
      rename _&i = &var.;
      %let i = %eval(&i + 1);
      %let var = %scan(&arg,&i,%str( ));
      %end;
   %mend;

View solution in original post


All Replies
Respected Advisor
Posts: 3,786

Re: Using a macro var list within another macro

[ Edited ]

Let step 2 process the list.

 

%macro dateformat(arg);
   %local i var;
   %let i = 1;
   %let var = %scan(&arg,&i,%str( ));
   %do %while(%superq(var) ne);
      &var._fmt = input(&var., MMDDYY10.) ;
      drop &var.;
      rename &var._fmt = &var.;
      %let i = %eval(&i + 1);
      %let var = %scan(&arg,&i,%str( ));
      %end;
   %mend;

 2 

New Contributor
Posts: 2

Re: Using a macro var list within another macro

Hello,

 

My example of the variables (date1, date2, ..., daten) should actually be more like (date1, second_date, the_third_date, dt_exited...).  In other words, the variables wouldn't necessarily end with a number nor look the same.  Would this affect the code you have?  Sorry I haven't had a chance to try it out, I changed jobs and waiting to get a SAS license. 

 

What I'm really looking for is to have some sort of loop in a data step, not necessarily just formats  

 

Thanks.

Solution
‎03-21-2017 09:18 AM
Respected Advisor
Posts: 3,786

Re: Using a macro var list within another macro

[ Edited ]

mikepep21 wrote:

Hello,

 

My example of the variables (date1, date2, ..., daten) should actually be more like (date1, second_date, the_third_date, dt_exited...).  In other words, the variables wouldn't necessarily end with a number nor look the same.  Would this affect the code you have?  Sorry I haven't had a chance to try it out, I changed jobs and waiting to get a SAS license. 

 

What I'm really looking for is to have some sort of loop in a data step, not necessarily just formats  

 

Thanks.


You create a blank delimited list of variable names with PROC SQL INTO.  This list of names when passed to the macro will be scanned to generate the code to create the new variable.  One thing that could be a problem is your macro used the old-name+_FMT as the name of the new variable which could depending on the length of old name produce an invalid SAS name.

 

This modification uses _&i as the temporary name.

 

%macro dateformat(arg);
   %local i var;
   %let i = 1;
   %let var = %scan(&arg,&i,%str( ));
   %do %while(%superq(var) ne);
      _&i = input(&var., MMDDYY10.) ;
      drop &var.; 
      rename _&i = &var.;
      %let i = %eval(&i + 1);
      %let var = %scan(&arg,&i,%str( ));
      %end;
   %mend;
Super User
Posts: 19,047

Re: Using a macro var list within another macro

Create two macro variable lists, one with the old variable names and one with the new variable names. 

 

Then use an array to do the conversion NOT macros.

 

array old_vars(*) &list_old_vars;
array new_vars(*) &list_new_vars;

do i=1 to dim(old_vars);

    new_vars(i) = input(old_vars(i), mmddyy10.);

end;

drop &list_old_vars;

Super User
Posts: 7,400

Re: Using a macro var list within another macro

When you have a dataset with variable names, you can create the conversion step dynamically from that dataset:

data _null_;
set varnames end=done;
if _n_ = 1 then call execute('data want;set have;');
call execute('%dateformat(`!!trim(variable_name)!!');`);
if done then call execute('run;');
run;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Valued Guide
Posts: 505

Re: Using a macro var list within another macro

Convert character dates to numeric dates with the same name

HAVE
====

 -- CHARACTER --
DATE1           C    9
DATE2           C    9
DATE3           C    9

 -- NUMERIC --
XN              N    8

Up to 40 obs WORK.HAVE total obs=8

Obs    XN      DATE1         DATE2         DATE3

 1     10    01/01/2000    02/21/2000    02/28/2000
 2     20    01/02/2000    02/08/2000    02/25/2000
 3     30    01/03/2000    03/29/2000    03/26/2000
 4     40    01/04/2000    01/15/2000    01/30/2000
 5     50    01/05/2000    02/08/2000    01/15/2000
 6     60    01/06/2000    02/19/2000    03/09/2000
 7     70    01/07/2000    01/21/2000    03/16/2000
 8     80    01/08/2000    02/17/2000    04/03/2000


WANT  (Dates are numeric)
=========================

 -- NUMERIC --
DATE1         N    8
DATE2         N    8
DATE3         N    8
XN            N    8

Obs    XN         DATE1         DATE2         DATE3

 1     10    01/01/2000    02/21/2000    02/28/2000
 2     20    01/02/2000    02/08/2000    02/25/2000
 3     30    01/03/2000    03/29/2000    03/26/2000
 4     40    01/04/2000    01/15/2000    01/30/2000
 5     50    01/05/2000    02/08/2000    01/15/2000
 6     60    01/06/2000    02/19/2000    03/09/2000
 7     70    01/07/2000    01/21/2000    03/16/2000
 8     80    01/08/2000    02/17/2000    04/03/2000


WORKING CODE
============

   ,input( DATE1 ,mmddyy10.) as DATE1
   ,input( DATE2 ,mmddyy10.) as DATE2
   ,input( DATE3 ,mmddyy10.) as DATE3

FULL SOLUTION
=============

* create some data;
data have(keep=xn date1 date2 date3);
  set sashelp.GNGSMP2;
  date1=put(date,mmddyy10.);
  date2=put(date + int(100*uniform(5831)),mmddyy10.);
  date3=put(datepart(datetime) + int(100*uniform(5831)),mmddyy10.);
run;quit;


data _null_;
   length sql $4096.;
   set have;
   array dte date:;
   do over dte;
      nam=vname(dte);
      cmd=catx(' ','input(',nam,',mmddyy10.) as',nam);
      sql=catx(',',sql,cmd);
   end;
   call symputx('sql',sql);
run;quit;

/*
%put &=sql;

SQL=input( DATE1 ,mmddyy10.) as DATE1
,input( DATE2 ,mmddyy10.) as DATE2
,input( DATE3 ,mmddyy10.) as DATE3
*/

proc sql;
   create
      table want as
   select
      xn
     ,&sql
   from
      have
;quit;

proc print data=want;
format date: mmddyy10.;
run;quit;

Up to 40 obs from want total obs=8

Obs    XN         DATE1         DATE2         DATE3

 1     10    01/01/2000    02/21/2000    02/28/2000
 2     20    01/02/2000    02/08/2000    02/25/2000
 3     30    01/03/2000    03/29/2000    03/26/2000
 4     40    01/04/2000    01/15/2000    01/30/2000
 5     50    01/05/2000    02/08/2000    01/15/2000
 6     60    01/06/2000    02/19/2000    03/09/2000
 7     70    01/07/2000    01/21/2000    03/16/2000
 8     80    01/08/2000    02/17/2000    04/03/2000


☑ This topic is solved.

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

Discussion stats
  • 6 replies
  • 214 views
  • 1 like
  • 5 in conversation