topic Re: Reading a csv file in SAS Programming

Reading a csv file

Nimish_Vaddiparti — Tue, 26 Jan 2016 16:01:49 GMT

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.

Re: Reading a csv file

RW9 — Tue, 26 Jan 2016 16:10:15 GMT

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

Re: Reading a csv file

FreelanceReinh — Tue, 26 Jan 2016 16:16:31 GMT

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

Re: Reading a csv file

Reeza — Tue, 26 Jan 2016 16:21:23 GMT

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

Re: Reading a csv file

ballardw — Tue, 26 Jan 2016 16:21:58 GMT

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.

Re: Reading a csv file

PGStats — Tue, 26 Jan 2016 16:35:54 GMT

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.

Re: Reading a csv file

FreelanceReinh — Tue, 26 Jan 2016 17:11:42 GMT

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.