topic Re: Using a macro var list within another macro in SAS Enterprise Guide

Using a macro var list within another macro

mikepep21 — Fri, 17 Mar 2017 18:03:45 GMT

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.

Re: Using a macro var list within another macro

data_null__ — Fri, 17 Mar 2017 18:12:16 GMT

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;

Re: Using a macro var list within another macro

Reeza — Fri, 17 Mar 2017 18:10:24 GMT

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;

Re: Using a macro var list within another macro

Kurt_Bremser — Fri, 17 Mar 2017 18:11:54 GMT

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;

Re: Using a macro var list within another macro

rogerjdeangelis — Fri, 17 Mar 2017 20:34:42 GMT

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

Re: Using a macro var list within another macro

mikepep21 — Mon, 20 Mar 2017 15:10:15 GMT

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.

Re: Using a macro var list within another macro

data_null__ — Mon, 20 Mar 2017 15:22:54 GMT

@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;