Modify Reading Date formats from a RDBMS/Cloud DWH table

[bhanucharan]

Hello SAS Experts,

I have a Redshift table(x) that has around 100 date columns out of 300 columns and we have  SAS/Access Redshift library in our environment to access Redshift tables  I am querying this Redshift table(x) in SAS Studio using SAS/Access Libname engine to generate a report. In this report, all 100 columns date values are appearing as Date9. formatted values, If I open Redshift table in SAS, Data is appearing as Date9. formatted values. SAS is auto assigning to Date9. format. I know it is the default behaviour.  Is there any way to change this behaviour of reading date values format from "Date9." to other Date formats for Ex: MMDDYY10. format or YYMMDD10. format. 

I am not creating any temporary dataset using the Redshift table to generate Reports to apply explicit date conversions using format statement or sasdatefmt= dataset option. Also, I don't want to write the format statement for all 100 date variables in reporting steps. Since it will hard for me to write for all variables. There is no pattern as well to identify Date variables with suffixes or prefixes.

It would be helpful if there is any way to change this behaviour using the global option/library option or any other method that changes this behaviour?

Thank you 

Bhanu

[ChrisNZ]

I don't know of such an option, but it would be a great idea. Add it the the sasware ballot?

You can easily change the format afterwards. See 

Change DATE formats to DDMMYYS10. for ALL (unknown number) date variables in a data set/LIBRARY

[bhanucharan]

@ChrisNZ  

Thanks for your response!

Sure i will post the idea in sasware ballot.

The reference link that you had shared will help if it is a SAS dataset Change DATE formats to DDMMYYS10. for ALL (unknown number) date variables in a data set/LIBRARY. 

Proc datasets modify option will not work with RDBMS/Cloud Dwh.

Thanks

Bhanu

[ChrisNZ]

You use the code on the link after you have read the data into SAS.

[bhanucharan]

My requirement is to generate a report from the Redshift table ie., Read from the DB table and generate a report. The shared code will not help in my scenario.

The below code (from the shared link) modifies the SAS Dataset formats. This below code will work in the scenario "if i am writing to SAS dataset from Redshift table and generate a report using SAS dataset", this code will modify all date variables stored in a SAS dataset.   

data _null_;
  set contents;
  where fmtinfo(format,'cat')='date';
  by libname memname ;
  if first.libname then call execute(catx(' ','proc datasets nolist lib=',libname,';')) ;
  if first.memname then call execute(catx(' ','modify',memname,';format',name)) ;
  else call execute(' '||trim(name)) ;
  if last.memname then call execute(' DDMMYYS10.; run;') ;
  if last.libname then call execute('quit;') ;
run;

[ChrisNZ]

If there is no global option to change the default format, you have 2 options:

1. Surely the report only includes a few variables, not 100 or 300. Change the formats in the report.

2. Modify the linked code slightly so it creates view that contains the wanted formats. You can use that view to create reports.

[bhanucharan]

@ChrisNZ The second option might work, Thanks for your help 🙂

[bhanucharan · Accepted solution]

View option worked! Below is the code that i have developed:

%macro apply_date_format(saslibref=,redshiftlibref=,sasformat=);

proc contents data=&redshiftlibref.._all_ noprint out=contents;
run;

data datecol;
set contents;
where fmtinfo(format,'cat')='date';
by libname memname ;
run;

proc sort data=datecol out= ds_name nodupkey;
by libname memname;
run;

%let opentable = %sysfunc(OPEN(ds_name , IS));
%if &openTable %then %do;
%let nobs = %sysfunc(attrn(&openTable, nobs));
%let closeTable=%sysfunc(CLOSE(&openTable));
%end;
%else %do;
%put No date variables in the library. Ending the sas program;
%goto program_end;
%end;
%put &nobs;

proc sql noprint;
select distinct lowcase(libname), lowcase(memname)
into: libname1 - :libname&nobs, :memname1 - :memname&nobs
from ds_name;
quit;

%do i=1 %to &nobs;
proc sql noprint;
select distinct name into:datecolnames separated by " "
from datecol
where lowcase(libname) = "&&libname&i"
and lowcase(memname) = "&&memname&i"
;
quit;
%put datasetname= &&libname&i...&&memname&i datevariables = &datecolnames;

data &saslibref..&&memname&i / view= &saslibref..&&memname&i;
set &&libname&i...&&memname&i;
;
format &datecolnames &sasformat;
run;

%end;
%program_end:
%put End of the program;
%mend;
%apply_date_format(saslibref=saslib,redshiftlibref=rslib,sasformat=mmddyy10.);

saslib => Target sas library where you want to store your sas view.

rslib => Redshift source library

mmddyy10. => Format to be applied

[ChrisNZ]

I think you over-complicate. This is more compact:

%macro apply_date_format(inlib=, viewlib=, dateformat=);

proc contents data=&inlib.._ALL_ out=CONTENTS noprint;
run;

data _null_;
  set CONTENTS;
  where fmtinfo(FORMAT,'cat')='date';
  by MEMNAME notsorted;
  if first.MEMNAME then 
    call execute("data &viewlib.."||MEMNAME||"/view=&viewlib.."||MEMNAME||";set &inlib.."||MEMNAME||";");
  call execute("format "||NAME||" &dateformat;"); 
run;
run;

%mend;

[bhanucharan]

Thanks, You are right. Missed the Call execute Option.