I have a data set with numerous SAS date columns, and each date column has a different SAS date format.
I'm looking for an easier way to extract the desired date format and convert into a macro variable to be referenced later in the program.
Is there an easier way to extract the date format without having to create and delete a contents dataset?
data have;
input units date1 :mmddyy10. date2 :yymmdd10. date3 :yyq6.;
format date1 mmddyy10. date2 yymmdd10. date3 yyq6.;
datalines;
2 01/04/2014 20140104 2014Q1
6 01/05/2014 20140105 2014Q1
7 01/06/2014 20140106 2014Q1
;
run;
proc contents data=have out=have_contents noprint;
run;
data _null_;
set have_contents;
if NAME='date3' then call symputx('want',strip(compress(FORMAT||put(FORMATL,2.)||'.')),'g');
run;
proc delete data=have_contents; run;
%put date format = &want;
I don't know if it exists, but envisioning something like a metadata function that can be referenced like: %let want = %sysfunc(datefmt(ds=have,col=date3));
Thanks
Hello @rah1992 and welcome to the SAS Support Communities!
Combining Reeza's suggestion (SASHELP.VCOLUMN) with the FMTINFO function you can even select the date variables without knowing their names:
data _null_;
set sashelp.vcolumn(where=(libname='WORK' & memname='HAVE' & format));
if fmtinfo(substr(format,1,anyfirst(format,-99)),'cat')='date';
call symputx(name,format);
run;
%put _user_;
Result for your sample data:
GLOBAL DATE1 MMDDYY10. GLOBAL DATE2 YYMMDD10. GLOBAL DATE3 YYQ6.
To rephrase (and perhaps augment) the correct advice from @Reeza
DO NOT FORMAT MACRO VARIABLES* for Boolean or arithmetic uses. Leave them unformatted, and your code works easily. Formatting them is just extra work, and then you have to either un-format them or start parsing text strings. SAS does not need formatted macro variables for this purpose.
* — the exception (which does not apply to Boolean or arithmetic uses) is when the macro variable needs to be used in human readable form, such as in a title or label or file name.
Hello @rah1992 and welcome to the SAS Support Communities!
Combining Reeza's suggestion (SASHELP.VCOLUMN) with the FMTINFO function you can even select the date variables without knowing their names:
data _null_;
set sashelp.vcolumn(where=(libname='WORK' & memname='HAVE' & format));
if fmtinfo(substr(format,1,anyfirst(format,-99)),'cat')='date';
call symputx(name,format);
run;
%put _user_;
Result for your sample data:
GLOBAL DATE1 MMDDYY10. GLOBAL DATE2 YYMMDD10. GLOBAL DATE3 YYQ6.
Thanks, @FreelanceReinh. It works great!
I found a global forum paper showing yet another way for capturing metadata without using proc contents. Both are improvements to the original. Thanks again.
%macro varexist(dsn=,varname=);
%local dsid vnum;
%let vnum=0;
%let dsid = %sysfunc(open(&dsn));
%if &dsid %then %do;
%let vnum = %sysfunc(varnum(&dsid,&varname));
%let dsid = %sysfunc(close(&dsid));
%end;
&vnum
%mend varexist;
/* Check for existence of timeid in dsn, and assign same date format as dsn and timeid provided in macro */
%macro dateformat(dsn=,timeid=);
%local dsid;
%let dsid= %sysfunc(open(&dsn));
%if %varexist(dsn=&dsn,varname=&timeid) %then %do;
%let fmt=%sysfunc(varfmt(&dsid,%sysfunc(varnum(&dsid,&timeid))));
%let dsid= %sysfunc(close(&dsid));
%end;
&fmt
%mend;
%put date format = %dateformat(dsn=have,timeid=date1);
data new;
set sashelp.air;
format date %dateformat(dsn=have,timeid=date3);
run;
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.