DATA Step, Macro, Functions and more

Reading a csv file

Reply
Occasional Contributor
Posts: 18

Reading a csv file

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.

Super User
Super User
Posts: 7,942

Re: Reading a csv file

Posted in reply to Nimish_Vaddiparti

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"? 

Trusted Advisor
Posts: 1,117

Re: Reading a csv file

Posted in reply to Nimish_Vaddiparti

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

Super User
Posts: 19,767

Re: Reading a csv file

Posted in reply to Nimish_Vaddiparti

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

 

Super User
Posts: 11,335

Re: Reading a csv file

Posted in reply to Nimish_Vaddiparti

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.

 

Respected Advisor
Posts: 4,918

Re: Reading a csv file

Posted in reply to Nimish_Vaddiparti

As @FreelanceReinhard 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
Trusted Advisor
Posts: 1,117

Re: Reading a csv file

Posted in reply to Nimish_Vaddiparti

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.

 

 

Ask a Question
Discussion stats
  • 6 replies
  • 436 views
  • 1 like
  • 6 in conversation