topic Re: Mass apply mmddyy10. format to all date variables in SAS Programming

Mass apply mmddyy10. format to all date variables

mmm7 — Tue, 23 Nov 2021 16:49:44 GMT

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?

Re: Mass apply mmddyy10. format to all date variables

AMSAS — Tue, 23 Nov 2021 16:58:40 GMT

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.

Re: Mass apply mmddyy10. format to all date variables

Tom — Tue, 23 Nov 2021 17:12:07 GMT

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?

Re: Mass apply mmddyy10. format to all date variables

mmm7 — Tue, 23 Nov 2021 17:16:04 GMT

Thanks @Tom ! Your code worked great and will save me SO much time!

Re: Mass apply mmddyy10. format to all date variables

mmm7 — Thu, 30 Dec 2021 13:53:26 GMT

@Tom Do you have syntax to clear all formatting EXCEPT for formatted date and time variables?

Re: Mass apply mmddyy10. format to all date variables

Tom — Thu, 30 Dec 2021 15:06:38 GMT

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;

Re: Mass apply mmddyy10. format to all date variables

Tom — Thu, 30 Dec 2021 15:14:24 GMT

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;