topic Streamline datepart for all dates in SAS Programming

Streamline datepart for all dates

Data_Detective_23219 — Thu, 20 Dec 2012 15:26:40 GMT

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_)?

Re: Streamline datepart for all dates

bnarang — Thu, 20 Dec 2012 17:53:55 GMT

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.

Re: Streamline datepart for all dates

Reeza — Thu, 20 Dec 2012 18:17:57 GMT

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

Re: Streamline datepart for all dates

bnarang — Thu, 20 Dec 2012 18:22:19 GMT

, 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.

Re: Streamline datepart for all dates

SASKiwi — Thu, 20 Dec 2012 19:08:27 GMT

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");

Re: Streamline datepart for all dates

SASKiwi — Thu, 20 Dec 2012 22:12:50 GMT

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;

%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;

Re: Streamline datepart for all dates

Peter_C — Fri, 21 Dec 2012 00:04:20 GMT

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