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.
@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;
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
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.
@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;
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;
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;
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.