BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
mikepep21
Calcite | Level 5

 

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.

1 ACCEPTED SOLUTION

Accepted Solutions
data_null__
Jade | Level 19

@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

6 REPLIES 6
data_null__
Jade | Level 19

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 

mikepep21
Calcite | Level 5

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.

data_null__
Jade | Level 19

@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;
Reeza
Super User

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;

Kurt_Bremser
Super User

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;
rogerjdeangelis
Barite | Level 11
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


sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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