Streamline datepart for all dates

[Data_Detective_23219]

Chapter 15 of Cody/Smith's text illustrate several methods to make changes to all numeric and/or character variables in a data set.  What I'm looking to do is to datepart and format date9. on all datetime variables in my dataset.  What is the alias for all datetime variables in a dataset to reference in an array (i.e. all numeric: _NUMERIC_)?

[bnarang]

I think Date is numeric and there s no differentiator ( Correct me if I am wrong @all SAS Experts).

To streamline dates, I would use Dictionary.columsn to list out all the possible date related variable and format them to streamlined them basically as per your objective.

For example, you have data:

data temp;

input x report_date date9. deal_date ddmmyy10.;

format report_date date9. deal_date ddmmyy10.;

datalines;

1 22jan2001 01/04/08

2 15Mar2002 05/09/01

;

proc sql;

select name into : list separated by ' '

from dictionary.columns

where libname = "WORK" and memname = "TEMP" and format in ("DDMMYY10.","DATE9.");

quit;

data temp1;

set temp;

format &list. date9.;

run;

I listed out the possible date format, you can provide a complete list. Then with Format statement, I streamlined.

[Reeza]

But if you had a datetime variable that wasn't formatted with a datetime format you'd miss it.

Basically you need to know your data here, I don't think there's shortcuts unless you have a naming convention that specifies variables that are datetime, ie start_datetime end_datetime

[bnarang]

, Basically, By looking at the query, I assume that datset has some date format or informat applied since he talks about streamlining that is standardizing like applying the same set of formats. For character data, definitely , we need to know data and there can be no shortcuts.

[SASKiwi]

I have a suspicion that the reason for this requirement may be because the source data is coming from an external database such as Oracle or SQL Server. Typically these databases hold all of their dates as datetimes and they consistently import into SAS with a DATETIME format and informat. If this is the case then identifying them like this would be quite reliable:

proc sql;

select name into : list separated by ' '

from dictionary.columns

where libname = "WORK" and memname = "TEMP" and index(format,"DATETIME");

[Peter_C]

how about applying the format DTDATE. instead of DATETIME.?

then you don't need to change the values stored

could do this in a PROC DATASETS step instead of re-writing the data

[SASKiwi]

I've had to do this a lot myself so here is a macro you can feed the list from the above SQL into and it doesn't use arrays, just statements using the DATEPART function:

macro Util_Convert_DT_Columns ( column_list =

                                ,conversion_method = DATE

                               );

%let i = 0;

%let column = START; %* Dummy value do get past DO WHILE ;

%do %while ((&column ne ) or (&i > 99));

  %let i = %eval(&i+1);

  %let column = %upcase(%scan(&column_list., &i));

  %if (%length(&column) ne 0) %then %do;

    %if (&conversion_method = DATE) %then %do;

      if not missing(&column) then &column = datepart(&column);

      attrib &column format = date9. informat = date9. ;

    %end;

    %else %if (&conversion_method = TIME) %then %do;

      if not missing(&column) then &column = timepart(&column);

      attrib &column format = time9. informat = time9. ;

    %end;

    %else %if (&conversion_method = DATETIME) %then %do;

      %if %index(&column, DATETIME) %then %let column_date = %substr(&column, 1, %eval(%index(&column, DATETIME) -1))Date;

      if not missing(&column) then &column_date = datepart(&column);

      attrib &column_date format = date9. informat = date9. ;

      %if %index(&column, DATETIME) %then %let column_time = %substr(&column, 1, %eval(%index(&column, DATETIME) -1))Time;

      if not missing(&column) then &column_time = timepart(&column);

      attrib &column_time format = time9. informat = time9. ;

    %end;  

  %end;

%end;

%mend Util_Convert_DT_Columns;

And to use it:

Data out;

  Set in;

  %Util_Convert_DT_Columns (column_list = &list); <== This is the macro variable containing the list from PROC SQL.

Run;