topic Re: Capture Date Format in a Macro Variable in SAS Programming

Capture Date Format in a Macro Variable

rah1992 — Fri, 01 Oct 2021 20:34:45 GMT

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

Re: Capture Date Format in a Macro Variable

Reeza — Fri, 01 Oct 2021 20:46:36 GMT

Why? Date calculations and references don't typically rely on the specific date format so if you store the data the format that's fine and usually more efficient for further usages.

Re: Capture Date Format in a Macro Variable

Reeza — Fri, 01 Oct 2021 20:50:38 GMT

And SASHELP.VCOLUMN has the formats that can be queried or VFORMAT will return a format of a variable within the data step.

VFORMAT()
https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/lefunctionsref/p0gph5udn7gkvdn1cnc641x4212a.htm

Re: Capture Date Format in a Macro Variable

PaigeMiller — Fri, 01 Oct 2021 21:07:42 GMT

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.

Re: Capture Date Format in a Macro Variable

FreelanceReinh — Fri, 01 Oct 2021 21:47:18 GMT

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.

Re: Capture Date Format in a Macro Variable

rah1992 — Sat, 02 Oct 2021 04:28:41 GMT

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;