BookmarkSubscribeRSS Feed
Aar684
Calcite | Level 5
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?
8 REPLIES 8
deleted_user
Not applicable
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.
Aar684
Calcite | Level 5
Unfortunately that did not fix it. Any other ideas? And yes, all of the dates fill out 10 characters.
deleted_user
Not applicable
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).
Aar684
Calcite | Level 5
Well it isn't necessarily a date problem. I am just getting the invalid data error.

Shoooooot. This is bugging me big time.
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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.
ballardw
Super User
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.
ChrisNZ
Tourmaline | Level 20
Also, when you look at the file in a text editor, are all variables always aligned in a one neat column?
statadm
Fluorite | Level 6
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.

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
  • 8 replies
  • 1011 views
  • 0 likes
  • 6 in conversation