topic Re: date format in proc sql in SAS Enterprise Guide

date format in proc sql

aola — Fri, 13 May 2016 06:55:20 GMT

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.

Re: date format in proc sql

Kalind_Patel — Fri, 13 May 2016 08:57:16 GMT

Hello @aola;

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

Re: date format in proc sql

Ksharp — Fri, 13 May 2016 09:20:09 GMT

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;

Re: date format in proc sql

aola — Fri, 13 May 2016 09:25:48 GMT

Thank you, you're amazing 🙂