topic Re: How to have proc import use column headings located in row-2? in SAS Programming

How to have proc import use column headings located in row-2?

lloydc — Thu, 29 May 2014 22:03:04 GMT

We have a utility that creates a large number of CSV files. The utility puts the type of data in that CSV file in row-1 (for example: 'PDEVINFO' or 'LDEVINFO') and the column headings are in row-2.

What I have is:

proc import

datafile = 'where it is'

out=Work.PDEVINFO

dbms = csv;

getnames = yes;

datarow = 3;

guessrows = 1000;

run;

proc print data=Work.PDEVINFO uniform;

run;

quit;

What it is doing is getting the value 'PDEVINFO' from row-1 and treating that as a column header and then all other columns it generates variable names as VAR1, VAR2,....

Is there a simple way of telling it to get the column headings from row-2?

Re: How to have proc import use column headings located in row-2?

RichardinOz — Thu, 29 May 2014 23:06:55 GMT

One solution is to remove the first line before Proc Import. It means that the files get read twice, which for many large files can be an issue.

filename have '<existing CSV>' ;

filename want '<edited CSV>' ;

data _Null_ ;

infile have ;

file want ;

input ;

if _N_ = 1 then return ;

put _infile_ ;

Run ;

If you are using base SAS rather then EG it is possible to do a Proc Import, then press F4 to retrieve the submitted code (which is just a SAS datastep) and edit it to skip the first line. Then use the edited datastep instead of Proc Import. Only worth doing if all your files have the same layout and column names, and the layout/names are unlikely to change..

Richard

Re: How to have proc import use column headings located in row-2?

RichardinOz — Thu, 29 May 2014 23:08:14 GMT

Also, you probably will need to set LRECL= 9999 or some large number on the filename statements to avoid truncation.

Richard

Re: How to have proc import use column headings located in row-2?

data_null__ — Thu, 29 May 2014 23:30:10 GMT

You could use FIRSTOBS=2 on the INFILE statement.

Re: How to have proc import use column headings located in row-2?

RichardinOz — Fri, 30 May 2014 01:35:50 GMT

It would be nice if the Filename statement supported the FIRSTOBS= option. Then you would not need to data _null_ step.

(No offense!)

Richard

Re: How to have proc import use column headings located in row-2?

lloydc — Fri, 30 May 2014 03:23:20 GMT

Fortunately these files are fairly small, just a couple of thousand records. This solution worked for me, thank you.

Re: How to have proc import use column headings located in row-2?

RichardinOz — Fri, 30 May 2014 04:12:11 GMT

You did remember to remove

datarow = 3;

didn't you?

Richard