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.
For an automatic conversion, you have to determine the following
- is the variable numeric or character?
- if the first:
if the latter:
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?
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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.