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

[Srigyan]

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.

[Kurt_Bremser]

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?

[gamotte]

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;