converting datetime20 to yyyymmdd

[alepage]

Hello,

I using a pass through connection to connect to an oracle Table as follow:

%macro CXNOracle(user=, password=, path=, schema=);

proc sql ;

CONNECT to oracle as oradb

(user=&user. password=&password. path=&path. );

create table &GFileName. as

SELECT *

FROM connection to oradb

(SELECT *

FROM &schema..&GFileName.) ;

DISCONNECT from oradb;

quit;

run;

%Mend CXNOracle;

%CXNOracle(user=*****, password=****, path=*****, schema=****);

As you can see, I input my user name, password, the place where is the oracle table then a schema.

The FileName is defined as a global variable as GFileName.

I have let's six date variable with the datetime20 format.

The challenge is to convert all date variables from a datetime20 format to YYYYMMDD without naming the variable.

Is there a way to set all date variables to a default format ex: YYYYMMDD?

Date variable as the following format = 31JAN1966:00:00:00

Thanks in advance for your help.

[Kurt_Bremser]

Use

datevar = datepart(datevar);
format datevar yymmddn8.;

or in a SQL select

datepart(datevar) as datevar format=yymmddn8.

[alepage]

Hello Kurt,

As I have mentioned, I calling the oracle table only by their name.  Some table may have two date variables other six date variables and so on.

What I am looking for is a way to convert all date variables to YYYYMMDD in one shot without having to format all date variable one by one, like a default format for date variable.

Does your code will do that?

[Shmuel]

Please check, either by proc contents or by sql, selecting dictionary.columns - do the datetime variables have a format of datetime20. as you mentioned.

If positive, it is possible to identify those variables through their format, convert then to date variables and reformat them,

to be done either by data step or by a macro, without renaming them.

[Kurt_Bremser · Accepted solution]

There is no silver bullet for this. You need to identify the columns (eg by looking for certain attributes in dictionary.columns) and then apply the suggested method in dynamically created code, if you want to automate this conversion.

[abqdiane]

Wonderful - Thanks

[Tom]

Use the FMTINFO() function to find the DATETIME variables. 

You could use something similar to the answer to this question. 

https://communities.sas.com/t5/SAS-Procedures/Change-DATE-formats-to-DDMMYYS10-for-ALL-unknown-numbe...

%macro CXNOracle(user=, password=, path=, schema=);
%if %symexist(gfilename) %then %do;
  %if %length(&gfilename) %then %do;  

libname oradb oracle user=&user password=&password path=&path schema=&schema access=readonly ;
proc contents data=oradb.&gfilename noprint out=_contents; run;
filename oracode temp;
data _null_;
  if 0=nobs then put "ERROR: No variables found for &gfilename..";
  file oracode ;
  set _contents obs=nobs end=eof;
  if _n_=1 then put
      "date &gfilename(label='From &path..&schema..&gfilename')"
    / " set oradb." &gfilename ';'
  ;
  if fmtinfo(format,'cat')='datetime' then put
      name '=datepart(' name ');'
    / 'format ' name 'yymmdd10.;'
  ;
  if eof then put 'run;' ;
run;
%include oracode / source2 ;

%else %put ERROR: Macro variable GFILENAME is empty.;
%else %put ERROR: Macro variable GFILENAME does not exist.;
libname oradb clear ;
%mend CXNOracle;