topic Re: Importing CSV file with datafile in SAS Programming

Importing CSV file with datafile

92568466 — Tue, 19 Apr 2022 18:16:27 GMT

I'm trying to import csv files with a datafile step. There are many rows in the csv file; I'm interested to get only a few. However, when I run the following code, I get the first few rows -- not the ones I'm looking for. Any help is appreciated:

data master_10;
%let _EFIERR_ = 0;
infile 'C:\Users\bmf.bm1812.csv'
delimiter = ',' MISSOVER DSD lrecl=32767 firstobs=2 ;

informat EIN best32. ; informat SEC_NAME $33. ; informat NAME $60. ;
informat STATE $2. ; informat nteeFinal $5. ; informat NTEECC $4. ;

format EIN best12. ; format SEC_NAME $33. ; format NAME $60. ;
format STATE $2. ; format nteeFinal $5. ; format NTEECC $4. ;

input EIN SEC_NAME $ NAME $ STATE $ nteeFinal $
NTEECC $;

if _ERROR_ then call symputx('_EFIERR_',1); /* set ERROR detection macro variable */
run;

Re: Importing CSV file with datafile

Tom — Tue, 19 Apr 2022 18:49:13 GMT

Why would that code not read all of the lines from the CSV file? What do the notes in the SAS log say? It should say how many lines read, and the min and max length of the lines. It should say how many observations were written to the dataset. There might be other notes also.

How did you create that ugly code? It looks like the code generated by PROC IMPORT.

If you really only have 6 variables just write your own data step to read the file and skip the guessing procedure. None of the variables your code describes need to have an informat nor a format attached.

data bm1812 ;
  infile 'C:\Users\bmf.bm1812.csv' dsd firstobs=2 truncover ;
  length EIN 8 SEC_NAME $33 NAME $60 STATE $2 nteeFinal $5 NTEECC $4 ;
  input EIN -- NTEECC ;
run;

If you don't want to read all of the lines then which lines do you want to read? In general you will have to read all of the lines in order to decide which ones to write out to the dataset. Unless you just want to read the top nn lines from the file. In which case add the OBS= option to the INFILE statement in addition to the FIRSTOBS= option.

Re: Importing CSV file with datafile

92568466 — Tue, 19 Apr 2022 19:05:25 GMT

My bad; I meant columns, not rows. I have many columns (variables) in the csv file, but I need only these. I can't get these columns using the code. It gives me first few variables, instead of the selected ones.

Re: Importing CSV file with datafile

92568466 — Tue, 19 Apr 2022 19:07:22 GMT

There is no error in the log though. A file is created.

Re: Importing CSV file with datafile

Kurt_Bremser — Tue, 26 Apr 2022 08:22:57 GMT

The columns in a csv file are sequential, so you need to read over the columns you do not want. I usually define a character variable called dummy, which I use repeatedly to read the unwanted columns and DROP it from the dataset.

dara want;
infile "...." dlm="," dsd truncover firstobs=2;
length
  want1 ....
  want2 ...
  .....
  dummy $1
;
input
  want1
  dummy
  dummy
  dummy
  want2
  .....
;
drop dummy;
run;

Re: Importing CSV file with datafile

Tom — Tue, 19 Apr 2022 19:52:38 GMT

To make it easier you can use syntax that means to read the same variable multiple times.

input want1 3*dummy want2 ...  ;