BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
hovliza
Obsidian | Level 7

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!

1 ACCEPTED SOLUTION

Accepted Solutions
error_prone
Barite | Level 11

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;

View solution in original post

4 REPLIES 4
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

hovliza
Obsidian | Level 7

Thanks for your suggestion, it had something to do with bad import process from Excel. So I fixed that.

ballardw
Super User

@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.

error_prone
Barite | Level 11

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 10453 views
  • 2 likes
  • 4 in conversation