topic Re: sas input selected columns in SAS Procedures

sas input selected columns

abcd123 — Tue, 04 Aug 2015 17:38:02 GMT

Hello, All

I have a csv file "abc.csv", it has 5000 variables (var1-var5000, column1 through column5000), and nearly million rows. However I am only interested in var1, var1390,var3782, and var4703. I know I can do it using following code:

filename myfile "abc.csv";

data want(keep=var1 var1390 var3782 var4703);

infile myfile;

input var1-var5000;

run;

However the code do waste time inputing those un-wanted columns.

My question is: is there a way to input only wanted columns, i.e. var1, var1390,var3782, and var4703?

Re: sas input selected columns

Tom — Tue, 04 Aug 2015 18:29:05 GMT

It is probably not worth the effort. Most of the time will be spent waiting for the disk to move the file into memory. The process of reading in the text version of 5000 variables would be small next to that.

Re: sas input selected columns

ballardw — Tue, 04 Aug 2015 20:27:33 GMT

IF the file were fixed column then it would be worth determining the columns and reading just those but CSV is going to yield no joy.

Though I would only input variables Var1-var4703;

I suspect you'll need a large value for lrecl as well.

Re: sas input selected columns

Tom — Tue, 04 Aug 2015 20:38:58 GMT

If you think it is worth the effort then look into some the unix based tools for subsetting CSV files.

http://stackoverflow.com/questions/7857090/awk-extract-specific-columns-from-delimited-file

You could wrap your solution inside of a PIPE and read it in SAS.

filename myfile pipe 'csvfilter -f 1,1390,3782,4703 abc.csv' ;

data want ;

infile myfile dsd truncover firstobs=2;

input var1 var1390 var3782 var4703;

run;

Re: sas input selected columns

abcd123 — Tue, 04 Aug 2015 21:09:03 GMT

Thanks, Tom. This is very helpful.