topic Re: problem facing while infile the CSV file. in SAS Programming

problem facing while infile the CSV file.

ROHINISDAS — Sat, 01 Jul 2017 05:52:51 GMT

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:

After using infile getting following error

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.

Re: problem facing while infile the CSV file.

Kurt_Bremser — Sat, 01 Jul 2017 06:26:34 GMT

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

Re: problem facing while infile the CSV file.

ROHINISDAS — Sat, 01 Jul 2017 07:45:05 GMT

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

Re: problem facing while infile the CSV file.

Kurt_Bremser — Sat, 01 Jul 2017 09:05:56 GMT

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

Re: problem facing while infile the CSV file.

art297 — Sat, 01 Jul 2017 13:19:53 GMT

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

Re: problem facing while infile the CSV file.

Tom — Sun, 02 Jul 2017 14:11:59 GMT

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;

Re: problem facing while infile the CSV file.

ROHINISDAS — Sun, 02 Jul 2017 14:59:31 GMT

Thank you so much tom.
U solved my problem.

Re: problem facing while infile the CSV file.

MikeZdeb — Sun, 02 Jul 2017 16:48:49 GMT

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;

Re: problem facing while infile the CSV file.

Tom — Sun, 02 Jul 2017 18:48:00 GMT

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