DATA Step, Macro, Functions and more

Change formats in array statement

Accepted Solution Solved
Reply
Contributor
Posts: 32
Accepted Solution

Change formats in array statement

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!


Accepted Solutions
Solution
‎12-06-2017 10:20 AM
Regular Contributor
Posts: 195

Re: Change formats in array statement

[ Edited ]

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


All Replies
Super User
Super User
Posts: 9,194

Re: Change formats in array statement

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.

Contributor
Posts: 32

Re: Change formats in array statement

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

Super User
Posts: 13,008

Re: Change formats in array statement


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.

Solution
‎12-06-2017 10:20 AM
Regular Contributor
Posts: 195

Re: Change formats in array statement

[ Edited ]

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;

☑ This topic is solved.

Need further help from the community? Please ask a new question.

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