Desktop productivity for business analysts and programmers

date format in proc sql

Reply
Occasional Contributor
Posts: 12

date format in proc sql

[ Edited ]

I execute the proc sql pass through (Teradata to SAS).

 

%let sql=select current_date from sys_calendar.calendar 

 

proc sql noprint;

CONNECT TO TERADATA(Authdomain= SERVER= mode=);

CREATE table &number AS SELECT * FROM CONNECTION TO TERADATA (&sql.);

DISCONNECT FROM TERADATA; QUIT;

 

I have a problem because I am seeing date format as DATEw. but I need format 'YYYY-MM-DD'.

I know I can do that:

select a, period_date format=yymmdd10. as c, b FROM CONNECTION TO TERADATA

 

I have a date format: 13MAY2016, but I need: '2016-05-12' but I have about 150 sqls, so I prefer a more global solution than a new format for each sql. Can I use some options? I need the same for each date, so I wolud like do that in global way.

Frequent Contributor
Posts: 96

Re: date format in proc sql

Hello @awojtaszek;

 

You can use SASDATEFMT option like that in your query;

( sasdatefmt=( date1='datetime21.') ) 

 

This is the link for more info:

http://support.sas.com/documentation/cdl/en/acreldb/63647/HTML/default/viewer.htm#a001371624.htm

Grand Advisor
Posts: 9,576

Re: date format in proc sql

You can always change these format after importing into SAS.

Assuming all the table stored at WORK library.

 

%let lib=work;

data _null_;
 set sashelp.vcolumn(where=(libname="%upcase(&lib)" and format='DATE9.')) end=last;
 by memname;
 if _n_=1 then call execute(catt("proc datasets library=&lib nolist nodetails;"));
 if first.memname then call execute(catx(" ","modify ",memname,"; format"));
 call execute(catx(" ",name," yymmdd10."));
 if last.memname then call execute(";");
 if last then call execute("quit;");
run;
Occasional Contributor
Posts: 12

Re: date format in proc sql

Thank you, you're amazing Smiley Happy

Ask a Question
Discussion stats
  • 3 replies
  • 337 views
  • 1 like
  • 3 in conversation