BookmarkSubscribeRSS Feed
Nimish_Vaddiparti
Calcite | Level 5

Hi,

 

I'm trying to use the in-file statement to read a csv file.

 

the code I've written is :

data NV; infile 'path\NVM_Data.csv' dsd firstobs =2 ; input Item_Id Market_Id $ date mmddyy10. Sales Season XX YY ZZZ Age PlugIn D DM Scrap FPR RPR CP EP GP FP ST LC; format date mmddyy10.; run;

 

But when I run the above program, the data doesn't get read properly.

I've attached the input data (test.csv) and the output (output.csv) for your reference. The output clearly shows that SAS isn't reading the data correctly. Can you help?

Thanks,

Nimish

 

PS - the files that I'm trying to read are in csv format. But since, I'm unable to upload them, I've saved them in excel format.

6 REPLIES 6
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Hi,

 

Its probab;y a good idea to explain what is "not right" about the output.  For your code:

data NV; 
  infile 'path\NVM_Data.csv' dsd firstobs =2 ; 
  input Item_Id Market_Id $ date mmddyy10. Sales Season XX YY ZZZ Age PlugIn D DM Scrap FPR RPR CP EP GP FP ST LC; 
format date mmddyy10.; run;

You could try something like this:

data NV; 
  infile 'path\NVM_Data.csv' dsd firstobs=2;
informat date mmddyy10.; input Item_Id Market_Id $ date Sales Season XX YY ZZZ Age PlugIn D DM Scrap FPR RPR CP EP GP FP ST LC;
format date mmddyy10.; run;

But its hard to say without knowing what "is wrong"? 

FreelanceReinh
Jade | Level 19

Hi Nimish,

 

I think, the primary issue is that you forgot to specify the delimiter in the INFILE statement: dlm=';' (if semicolon is in fact the delimiter).

Reeza
Super User

Change the extension on your CSV to .txt and upload that file please. 

 

ballardw
Super User

Since CSV generally means at least some of the variables are differing lengths you seldom want to have the formats on the input statement as then SAS reads the length  specified by the format for every field. This behavior was very typical and the desired default when one of the main ways to deal with data was in fixed columns.

So specify the INFORMATS before the INPUT and leave them out of the input statement:

 

data NV; 
   infile 'path\NVM_Data.csv' dsd firstobs =2 ; 
   informat Market_Id $10. ;  /* You want to make sure that the width is set long enough for your largest expected value*/
   informat date mmddyy10. ;
   input Item_Id Market_Id  date  Sales Season XX YY ZZZ Age PlugIn D DM Scrap FPR RPR CP EP GP FP ST LC; 
   format date mmddyy10.; 
run;

Another possibly problem is using $ as that defaults to 8 and you may have needed more characters. If I'm told that a field is supposed to be a specified length I usually add 1 or 2 to the informat "just in case" and if the value is name or address then often lots more additional characters.

 

PGStats
Opal | Level 21

As @FreelanceReinh pointed at, DSD considers the comma as the default list delimeter, even if it's not the case in your culture. Check your CSV file and tell SAS what the list delimiter character is in your file, if not the comma. 

PG
FreelanceReinh
Jade | Level 19

Even if your date field had exactly length 10 in all rows, so that your formatted input "date mmddyy10." would work, it would cause an issue with the next field to be read (Sales): After reading DATE, the pointer would stop immediately in front of the next delimiter, causing a missing value for SALES. Then, sales values would go into variable SEASON, ...

 

So, please use the colon modifier "date :mmddyy10." or, alternatively, use the INFORMAT statement (and omit the informat specification in the INPUT statement), as suggested by @RW9 and @ballardw, to indicate modified list input.

 

 

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
  • 6 replies
  • 1340 views
  • 1 like
  • 6 in conversation