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.
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"?
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).
Change the extension on your CSV to .txt and upload that file please.
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.
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.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.