topic Re: If header consist "date" then convert value in date9. format in SAS Programming

If header consist "date" then convert value in date9. format

Srigyan — Wed, 21 Mar 2018 12:43:16 GMT

I have a dataset, I want to check all the column name and if that consists "Date" in the header then convert that column in date9. format.

the already available date field can be in text format or date format, but they are date.

Re: If header consist "date" then convert value in date9. format

Kurt_Bremser — Wed, 21 Mar 2018 13:14:11 GMT

For an automatic conversion, you have to determine the following

- is the variable numeric or character?

- if the first:

are the contents typical for SAS dates -> only apply a format
are the contents typical for SAS datetimes -> use the datepart() function and apply a format
are the contents actually numbers like 20032018 or 20180320 -> convert with put() and input() functions and apply a format

if the latter:

find out which format is used
convert using the input() function with a proper informat
apply a format

Metadata (column name, type, currently applied format) can be retrieved from dictionary.columns (or sashelp.vcolumn)

This will be a very tedious task. It is much better to do this type of "cleaning" upon import of data into the SAS system.

Where does your dataset come from?

Re: If header consist "date" then convert value in date9. format

gamotte — Wed, 21 Mar 2018 13:29:39 GMT

Hello,

Assuming you have character dates as input :

data have;
    length date_1 date_2 $10;
    input id date_1 $ date_2 $;
    cards;
1 01/01/2018 05/16/2017
2 02/03/2018 06/25/2016
3 12/21/2017 03/01/2015
;
run;

data _NULL_;
    set have end=eof;

    call execute('data want; set have(rename=(');

    length colname $32;

    do while (upcase(colname) ne "COLNAME");
        call vnext(colname);
        if colname=:"date" then do;
            call execute(cats(colname,'=old_',colname));
        end;
    end;

    call execute('));');

    colname="";

    do while (upcase(colname) ne "COLNAME");
        call vnext(colname);
        if colname=:"date" then do;
            call execute(catx(' ','format',colname,'date9.;',colname,'=input(old_'||colname,', mmddyy10.);'));
        end;
    end;

    call execute('drop old_:; run;');

    stop;
run;