topic Re: Date informat problem? in SAS Programming

Date informat problem?

Aar684 — Thu, 13 Aug 2009 11:48:19 GMT

Hi all.

I am trying to import a data set with multiple variables. I have no problem importing everything up until my date field. The data file is a .csv with an account number and a date per row of data followed by 24 columns (hours) of data. I am getting invalid data errors starting with date all the way through the 24 hours. I used yvar1-yvar24 for the hours in the day and date for my date field. My code looks like this:

data a;
length rate$4. account $11. meter $11. channel $13. lrid $16. name $33. units $5. legacy $11.;
informat date mmddyy10.; array yvar yvar1-yvar24;

infile 'K:\LOAD RESEARCH\CLP Rate Case Test Year 07-01-08 to 06-30-09\CSV Files Dist Run\w Legacy\input\1.csv' lrecl=350 missover dlm=',';

input account meter legacy channel lrid name units date mmddyy10. yvar1-yvar24;

The date in the csv file looks like this 07/18/2008. What am I doing wrong here?

Re: Date informat problem?

deleted_user — Thu, 13 Aug 2009 13:09:04 GMT

Try removing the "mmddyy10." from the INPUT statement (you shouldn't need this given the INFORMAT).

Do all of your date fields fill out exactly 10 characters (i.e., have their leading zeros)? If not (e.g., 08/5/2009), that might be the problem, but removing the "mmddyy10." should fix it.

Re: Date informat problem?

Aar684 — Thu, 13 Aug 2009 13:27:30 GMT

Unfortunately that did not fix it. Any other ideas? And yes, all of the dates fill out 10 characters.

Re: Date informat problem?

deleted_user — Thu, 13 Aug 2009 13:39:11 GMT

Not off the top of my head. I could only force an error message by keeping your original code (i.e., before removing the extra informat specification) and having a date value less than 10 bytes long.

I wonder if there might be some other kind of problem that it just showing up as a date problem. I often use the DSD option on the INFILE statement (assuming that the CSV file fully conforms to standards, i.e., if a character field has an embedded comma, it is enclosed in double quotes).

Re: Date informat problem?

Aar684 — Thu, 13 Aug 2009 13:44:13 GMT

Well it isn't necessarily a date problem. I am just getting the invalid data error.

Shoooooot. This is bugging me big time.

Re: Date informat problem?

sbb — Thu, 13 Aug 2009 15:15:43 GMT

You will need to share more about the SAS execution log, as well as suggesting you add some self-diagnostic code:

PUTLOG _ALL_;

...AND/OR...

PUTLOG _INFILE_;

...AND/OR...

LIST;

Using these statements in your SAS program for the DATA step will help you learn more about the current data record's and how SAS is interpreting them.

Scott Barry
SBBWorks, Inc.

Re: Date informat problem?

ballardw — Tue, 18 Aug 2009 22:10:40 GMT

By any chance are you looking at the CSV file in Excel and not a text editor? I have had occasions where saving a CSV from Excel results in dates converting from something 10/01/2008 to an Excel date offset integer value that usually would not be acceptable to the SAS informat.

Re: Date informat problem?

ChrisNZ — Wed, 19 Aug 2009 03:16:57 GMT

Also, when you look at the file in a text editor, are all variables always aligned in a one neat column?

Re: Date informat problem?

statadm — Wed, 19 Aug 2009 19:51:55 GMT

Try this way, it worked for me:

data a;

infile 'K:\LOAD RESEARCH\CLP Rate Case Test Year 07-01-08 to 06-30-09\CSV Files Dist Run\w Legacy\input\1.csv' lrecl=350 missover dlm=',' ;

input account :$11. meter :$11. legacy :$11. channel :$13. lrid :$16. name :$33. units date :MMDDYY10. yvar1-yvar24;

format date mmddyy10.;

run;

proc print;
run;

I didn't see the variable RATE in the input, so I left it out. I couldn't get the date to format properly without the format statement.