Hi! I want to change formats of some date variables. I use this syntax:
*Dates in same format;
DATA m.want;
SET m.have;
array _char date1 date2 date3 date4 date5 date6;
array _num date11 date12 date13 date14 date15 date16;
do i=1 to dim(_char);
_num(i)=input(_char(i),ddmmyy10.);
END;
RUN;
This doesn't work because I get the error: Invalid argument to function INPUT at this line '_num(i)=input(_char(i),ddmmyy10.);' What can I do to properly change the formats of the dates? Thanks!
There is no error in your array statements, but maybe one of the string does not contain a date.
data work.have;
length date1-date6 $ 10;
array _chars date1 date2 date3 date4 date5 date6;
do i = 1 to 6;
_chars(i) = put(today()-i, ddmmyyp10.);
end;
drop i;
run;
data want;
set have;
length dateN1-dateN6 8;
format dateN1-dateN6 date9.;
array _chars date1 date2 date3 date4 date5 date6;
array _nums dateN1-dateN6;
do i = 1 to 6;
_nums(i) = input(_chars(i), ddmmyy10.);
end;
drop i;
run;
Look at the syntax of the array statement, you have it wrong. Also, post some test data in a datastep so we have something to work with. This should work, but only guessing:
data want; set have; array c{6} date1 date2 date3 date4 date5 date6; array n{6} date11--date16; do i=1 to 6; n{i}=input(c{i},ddmmyy10.); end; run;
Although the question arises why do you have so many date variables which are not dates to start with - this is something often seen by bad import processes - i.e from Excel. Fix your importing and this problem no longer exists.
Thanks for your suggestion, it had something to do with bad import process from Excel. So I fixed that.
@hovliza wrote:
Thanks for your suggestion, it had something to do with bad import process from Excel. So I fixed that.
One recurring problem I have with people entering data into Excel and causing "date" problems is that the individual cells are sometimes actual Excel date values and sometimes character strings. And depending on how the "date" was entered the displayed value will be in different Excel display formats.
So if you have problems it may help to go back to original Excel file and force each date column into the same display format by highlighting the entire column and then setting the same format. If you see any cells that don't match, those are likely entered as character and need help.
If you have too many records/files for manual treatment such as this to be practical then
1) Save the Excel files to CSV
2) Use proc import with a large Guessingrows option
3) modify the code generated by proc import (copy from log and paste into editor) to us ANYDTDTE32. informat for dates.
Which will read most of the junk (30 Feb and such will still be invalid data though).
You might try the ANYDTDTE informat with your example code.
There is no error in your array statements, but maybe one of the string does not contain a date.
data work.have;
length date1-date6 $ 10;
array _chars date1 date2 date3 date4 date5 date6;
do i = 1 to 6;
_chars(i) = put(today()-i, ddmmyyp10.);
end;
drop i;
run;
data want;
set have;
length dateN1-dateN6 8;
format dateN1-dateN6 date9.;
array _chars date1 date2 date3 date4 date5 date6;
array _nums dateN1-dateN6;
do i = 1 to 6;
_nums(i) = input(_chars(i), ddmmyy10.);
end;
drop i;
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.