DATA Step, Macro, Functions and more

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

Reply
Occasional Contributor
Posts: 11

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


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.

 

Super User
Posts: 9,870

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

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?

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super Contributor
Posts: 331

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

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;
Ask a Question
Discussion stats
  • 2 replies
  • 87 views
  • 0 likes
  • 3 in conversation