Read specific columns of a delimited file in SAS

问题:

This seems like it should be straightforward, but I can't find how to do this in the documentation. I want to read in a comma-delimited file, but it's very wide, and I just want to read a few columns.

I thought I could do this, but the @ pointer seems to point to columns of the text rather than the column numbers defined by the delimiter:

data tmp;
 infile 'results.csv' delimiter=',' MISSOVER DSD lrecl=32767 firstobs=2;
 @1 id
 @5 name$
run;

In this example, I want to read just what is in the 1st and 5th columns based on the delimiter, but SAS is reading what is in position 1 and position 5 of text file. So if the first line of the input file starts like this

1234567, "x", "y", "asdf", "bubba", ... more variables ...

I want id=1234567 and name=bubba, but I'm getting name=567, ".

I realize that I could read in every column and drop the ones I don't want, but there must be a better way.

回答1:

Indeed, @ does point to column of text not the delimited column. The only method using standard input I've ever found was to read in blank, ie

input
id
blank $
blank $
blank $ 
name $
;

and then drop blank.

However, there is a better solution if you don't mind writing your input differently.

data tmp;
 infile datalines;
 input @;
 id = scan(_INFILE_,1,',');
 name = scan(_INFILE_,5,',');
 put _all_;
 datalines;
12345,x,y,z,Joe
12346,x,y,z,Bob
;;;;
run;

It makes formatting slightly messier, as you need put or input statements for each variable you do not want in base character format, but it might be easier depending on your needs.

回答2:

You can skip fields fairly efficiently if you know a bit of INPUT statement syntax, note the use of (3*dummy)(:$1.). Reading just one byte should also improve performance slightly.

data tmp;
   infile cards DSD firstobs=2;
   input id $ (3*dummy)(:$1.) name $;
   drop dummy;
   cards;
id,x,y,z,name
1234567, "x", "y", "asdf", "bubba", ... more variables 
1234567, "x", "y", "asdf", "bubba", ... more variables 
   run;
proc print;
   run;

回答3:

One more option that I thought of when answering a related question from another user.

filename  tempfile temp;

data _null_;
  set sashelp.cars;
  file tempfile dlm=',' dsd lrecl=32767;
  put (Make--Wheelbase) ($);
run;


data mydata;
  infile tempfile dlm=',' dsd truncover lrecl=32767;
  length _tempvars1-_tempvars100 $32;
  array _tempvars[100] $;
  input (_tempvars[*]) ($);
  make=_tempvars[1];
  type=_tempvars[3];
  MSRP=input(_tempvars[6],dollar8.);
  keep make type msrp;
run;

Here we use an array of effectively temporary (can't actually BE temporary, unfortunately) variables, and then grab just what we want specifying the columns. This is probably overkill for a small file - just read in all the variables and deal with it - but for 100 or 200 variables where you want just 15, 18, and 25, this might be easier, as long as you know which column you want exactly. (I could see using this in dealing with census data, for example, if you have it in CSV form. It's very common to just want a few columns most of which are way down 100 or 200 columns from the starting column.)

You have to take some care with your lengths for the temporary array (has to be as long as your longest column that you care about!), and you have to make sure not to mess up the columns since you won't get to know if you mess up unless it's obvious from the data.