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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.