Capture Date Format in a Macro Variable

[rah1992]

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

[Reeza]

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.

[Reeza]

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.h...

[PaigeMiller]

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.

[FreelanceReinh · Accepted solution]

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.

[rah1992]

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;