Macro for min max dates with different date formats

[mtee]

Hello, I am trying to run a macro for min max dates for a few tables but because the dates are in different formats for each table (date9. and datetime9.) my final output is a correct or a number. Is there a way I can fix this so that each variable is correctly formatted in the table? (see code below).

%macro date(variable,dataset,order);

proc sql;
create table all_dates_&order as
select min(&variable) as min format=date9., max(&variable) as max format=date9., "&dataset" AS Table, "&variable" AS VARIABLE , &ORDER AS ORDER, "All HCS" AS NAME
from &dataset a
quit;

%mend date;

%hc_date(date_birth,demo_table, 1)
%hc_date(effective_datetime, event_table, 2)

[PaigeMiller]

If you SAS variables are valid SAS datetime values, you can use the DATEPART function on the datetime values, and that converts them to dates, so you can compare minimums and maximums.

Please note: it appears from your description, if I am understanding properly, the problem is not FORMATs of the variable, the problem is that one variable has SAS date values, and the other variable has SAS datetime values. Please let me know if this is not correct.

[mtee]

Yes that is correct the problem is that one variable has SAS date values, and the other variable has SAS datetime values. So would i need to change my code from sql to sas? 

[PaigeMiller]

---

@mtee wrote:

So would i need to change my code from sql to sas? 

---

No. As I said: "... you can use the DATEPART function on the datetime values, and that converts them to dates, so you can compare minimums and maximums."

[Sajid01]

Hello
Have a look at this simple solution. I hope it will work for you (some tables have date values and some have datetime. )

data test;
input id date1 ;
informat date1  date9.;
format date1  date9.;
datalines;
1 21MAR2011 
2 22MAR2009
3 22MAR2020 
4 21MAR2018 21:33:22.55
;
run;

%macro hc_date(variable,dataset, order);
proc sql;
create table all_dates as
select min(&variable) as min format=date9., max(&variable) as max format=date9., "&dataset" AS Table, "&variable" AS VARIABLE, 
"&Order." as ORDER,"All HCS" AS NAME
from &dataset.;
quit;
%mend hc_date;

%hc_date(date1,test,1)

The output would be some thing like this

[Tom]

Just tell the macro you want the datepart() for any datetime variable.

It is easiest if you force the caller to do this.

%macro date(variable,dataset,order);
proc sql;
create table all_dates_&order as
  select min(&variable) as min format=date9.
       , max(&variable) as max format=date9.
       , "&dataset" AS Table length=41
       , "&variable" AS VARIABLE length=42
       , &ORDER AS ORDER
       , "All HCS" AS NAME
  from &dataset a
;
quit;
%mend date;

%hc_date(date_birth,demo_table, 1)
%hc_date(datepart(effective_datetime), event_table, 2)

[Tom]

If you really need the macro to be smart enough to test the format attached to the variable and figure out for itself whether the variable has date or datetime values you could query the metadata of the input dataset. 

For example you could use this %VAREXIST() macro.

%macro date(variable,dataset,order);
%local fmtspec fmtcat date;
%let fmtspec=%varexist(&dataset,&variable,fmt);
%let fmtcat=%sysfunc(fmtinfo(%substr(&fmtspec,1,%sysfunc(findc(&fmtspec,.,-40,dk))),cat));
%if &fmtcat=date then date=&variable;
%else %if &fmtcat=datetime then date=datepart(&variable);
%else %do;
  %put ERROR: &=variable is neither a DATE nor a DATETIME varaible in &=dataset ;
  %return;
%end;
proc sql;
create table all_dates_&order as
  select min(&date) as min format=date9.
       , max(&date) as max format=date9.
       , "&dataset" AS Table length=41
       , "&variable" AS VARIABLE length=42
       , &ORDER AS ORDER
       , "All HCS" AS NAME
  from &dataset a
;
quit;
%mend date;

%hc_date(date_birth,demo_table, 1)
%hc_date(effective_datetime, event_table, 2)