DATA Step, Macro, Functions and more

problem facing while infile the CSV file.

Accepted Solution Solved
Reply
Contributor
Posts: 20
Accepted Solution

problem facing while infile the CSV file.

Hi Guys.

 

I want to import the CSV file through infile but i am getting below mentioned alert while doing so . Please help out in solving the error.

 

FOllowing is the CSV file i need to import:

csv file.JPG

 

After using infile getting following error

err.JPG

 

Code Tested:

data a;

infile "C:\documets\2.csv" dlm="" dsd firstobs=2;

input cc dob enroll;

informat dob mmddyy10. ;

informat enroll date9.;

format dob mmddyy10.;

format enroll date9.;

run;

 

Please help in rectifyin the error.

 


Accepted Solutions
Solution
‎07-02-2017 10:55 AM
Super User
Super User
Posts: 7,039

Re: problem facing while infile the CSV file.

Posted in reply to ROHINISDAS

From the photograph of the SAS log that you posted your source data is a CSV file, that is a delited file of text with commas as the delimiter used between the values.  But your SAS program is using something else as the delimiter. So your program is reading the full line as the value to use for the first variable.  And since you did not include the TRUNCOVER option on your INFILE statement it tried to read the first line in CC , the second line into DOB and the third line into ENROLL.

 

Here is a corrected program that should work given the one line of data that you showed in the photograph of your log.

 

data a;
  infile "C:\documets\2.csv" dlm=',' dsd firstobs=2 truncover;
  length cc dob enroll 8 ;
  informat dob mmddyy10. enroll date9.;
  format dob enroll yymmdd10. ;
  input cc dob enroll;
run;

View solution in original post


All Replies
Super User
Posts: 7,766

Re: problem facing while infile the CSV file.

Posted in reply to ROHINISDAS

Posting a screenshot from Excels tells us next to nothing about the contents of your text file.

Either attach the csv file to your post, or copy/paste its contents into a {i} window (very important to preserve contents and formatting!).

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 20

Re: problem facing while infile the CSV file.

Posted in reply to KurtBremser

Hi ,

 

Please see the whole post , i have mentioned everything then and there itself which includes the csv file that i need to import, error after i use infile as well as the code that i have tried.

 

Thanks

Rohini 

 

Super User
Posts: 7,766

Re: problem facing while infile the CSV file.

Posted in reply to ROHINISDAS

Theres no csv file, neither as text nor as attachment. There's just a screenshot off some data in Excel. Read my post again.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
PROC Star
Posts: 7,468

Re: problem facing while infile the CSV file.

Posted in reply to ROHINISDAS

You said that it's a csv file, and your log shows that it is, but you didn't specify dlm=',' in your infile statement.

 

Art, CEO, AnalystFinder.com

 

Solution
‎07-02-2017 10:55 AM
Super User
Super User
Posts: 7,039

Re: problem facing while infile the CSV file.

Posted in reply to ROHINISDAS

From the photograph of the SAS log that you posted your source data is a CSV file, that is a delited file of text with commas as the delimiter used between the values.  But your SAS program is using something else as the delimiter. So your program is reading the full line as the value to use for the first variable.  And since you did not include the TRUNCOVER option on your INFILE statement it tried to read the first line in CC , the second line into DOB and the third line into ENROLL.

 

Here is a corrected program that should work given the one line of data that you showed in the photograph of your log.

 

data a;
  infile "C:\documets\2.csv" dlm=',' dsd firstobs=2 truncover;
  length cc dob enroll 8 ;
  informat dob mmddyy10. enroll date9.;
  format dob enroll yymmdd10. ;
  input cc dob enroll;
run;
Contributor
Posts: 20

Re: problem facing while infile the CSV file.

Thank you so much tom.
U solved my problem.
Valued Guide
Posts: 765

Re: problem facing while infile the CSV file.

[ Edited ]
Posted in reply to ROHINISDAS

Hi, you can cut down on the amount of SAS code ...

 

#1 the DSD option implies a comma delimiter so the DLM option is not needed

#2 your dates are in standard forms so the ANYDTDTE informat works for both

#3 the default length of numeric variables is 8, so the LENGTH statement is not needed

#4 it's just LIST input, so you can add the INFORMAT in the INPUT statement and leave out the INFORMAT statement

 

data a;
infile "z:\test.csv" dsd firstobs=2;
format dob enroll yymmdd10. ;
input cc (dob enroll) (:anydtdte.);
run;

Super User
Super User
Posts: 7,039

Re: problem facing while infile the CSV file.


MikeZdeb wrote:

Hi, you can cut down on the amount of SAS code ...

 

#1 the DSD option implies a comma delimiter so the DLM option is not needed

#2 your dates are in standard forms so the ANYDTDTE informat works for both

#3 the default length of numeric variables is 8, so the LENGTH statement is not needed

#4 it's just LIST input, so you can add the INFORMAT in the INPUT statement and leave out the INFORMAT statement

 


Mike -

#1 - I purposely included the DLM= option because the original poster had used this option wrong and I wanted to highlight what delimiter they should be using.

#2 - I puposely used the informats that the user used since they matched the formats in the sample line of data. Note that the ANYDTDTE can make the wrong decision about whether ambigous values should be treated as M/D/Y or D/M/Y.

#3 - The LENGTH statement is not optional in my opinion. Without it you have to rely on SAS making the decision on both the position of the variable is the data set and also the variables type and length based on how they first appear in the code.  Not a good way to define your data.

#4 - You could add the informats in the INPUT statement, but then you need to remember to use the : modifier or else you risk that the formatted input will "eat" delimiters and cause the values to be read into the wrong variables.  Also it preludes using a variable list in the INPUT statement.  Normally for a CSV file the INPUT statement is simply "input firstvar -- lastvar;" .

#5 - You remove the TRUNCOVER option from the INFILE statement, another mistake that caused the original program to not work.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 173 views
  • 1 like
  • 5 in conversation