I have a large dataset with several hundred variables that have a YYMMDD10. informat. Is there an easy way to mass apply a MMDDYY10. format to all of these variables without individually listing them each out in a format statement?
NO. But that doesn't mean that YOU have to list them yourself. Let SAS do it for you.
First let's make a couple of macro variables so we don't have to keep re-typing the name of the dataset. Then we can ask SAS to create another macro variable with the variables we want to change. Then use PROC DATASETS to change the formats.
%let lib=WORK;
%let ds=HAVE;
proc contents data=&lib..&ds noprint out=contents; run;
proc sql noprint;
select nliteral(name) into :datevars separated by ' '
from contents
where format='YYMMDD'
;
quit;
proc datasets nolist lib=&lib;
modify &ds ;
format &datevars mmddyy10.;
run;
quit;
PS Why do you want to convert from a date style that everyone will understand to one that will confuse 50% of the world? Does 10/12/2021 mean October twelfth or the tenth of December?
It will come down to how your variables are named, for example in the example below I have assigned DDMMYY10. to date2 without having to specify date2.
data have ;
do date1="23NOV2021"d to "28NOV021"d ;
date2=date1+7 ;
date3=date2+7 ;
output ;
end ;
run ;
data want ;
set have ;
format date1-date3 ddmmyy10. ;
run ;
Now if your date variables have non-sequential variable names e.g. dateOne, dateTwo, dateThree then that's going to be more challenging.
First question would be how do you know any of these variables are date variables and need to be formatted DDMMYY10.
NO. But that doesn't mean that YOU have to list them yourself. Let SAS do it for you.
First let's make a couple of macro variables so we don't have to keep re-typing the name of the dataset. Then we can ask SAS to create another macro variable with the variables we want to change. Then use PROC DATASETS to change the formats.
%let lib=WORK;
%let ds=HAVE;
proc contents data=&lib..&ds noprint out=contents; run;
proc sql noprint;
select nliteral(name) into :datevars separated by ' '
from contents
where format='YYMMDD'
;
quit;
proc datasets nolist lib=&lib;
modify &ds ;
format &datevars mmddyy10.;
run;
quit;
PS Why do you want to convert from a date style that everyone will understand to one that will confuse 50% of the world? Does 10/12/2021 mean October twelfth or the tenth of December?
Thanks @Tom ! Your code worked great and will save me SO much time!
@Tom Do you have syntax to clear all formatting EXCEPT for formatted date and time variables?
Use the same code, just add an extra FORMAT statement in the last step.
proc datasets nolist lib=&lib;
modify &ds ;
format _all_ ;
format &datevars mmddyy10.;
run;
quit;
To find DATE, TIME or DATETIME variables to exclude or re-attach formats to use the FMTINFO() function with the 'CAT' info type.
proc sql noprint;
select nliteral(name) into :othervars separated by ' '
from contents
where not (fmtinfo(format,'cat') in ('date','time','datetime'))
;
proc datasets nolist lib=&lib;
modify &ds ;
format &othervars;
run;
quit;
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!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.