topic Re: Proc import - varnames and data in unusual rows in SAS Procedures

Proc import - varnames and data in unusual rows

jdsdog10 — Tue, 12 Oct 2021 21:00:14 GMT

I have my variable names in line 8, and my data starts at line 14. I'm not able to figure out how to import the data while skipping those other lines. Currently I am importing line 8 exclusively, using getnames, creating an empty table with variable names (over 120). Then importing all the data to a temporary table. Then adding the data from the temporary table to the empty table that has the variable names. It seems like there should be a way to designate the header row and designate a different starting row.

*Update: the variable names are important. I have about 20 different excel files, all with the same format, and each file has different variable names (all 120+) that are important for the task at hand. Editing the file isn't an option at the moment.

Re: Proc import - varnames and data in unusual rows

Tom — Tue, 12 Oct 2021 20:38:08 GMT

What kind of file are you reading?

Re: Proc import - varnames and data in unusual rows

ballardw — Tue, 12 Oct 2021 20:39:33 GMT

Either edit the file so that the variable names are on row 1 and data starts on row 2

Write a data step to read data. The Infile statement will allow you to tell which row the data starts on by using the Firstobs= option and you pick/set your variable names with Input/informat/attribute statements.

Proc Import is designed for reading simple data designed for interchange. Multiple row headings and blank rows are not what it is designed to read.

Re: Proc import - varnames and data in unusual rows

Kurt_Bremser — Tue, 12 Oct 2021 20:41:28 GMT

Why go through all that? Write your own data step with FIRSTOBS=14 and be done with it. As a benefit, you also get consistent results.

Re: Proc import - varnames and data in unusual rows

jdsdog10 — Tue, 12 Oct 2021 21:00:28 GMT

xlsx

Re: Proc import - varnames and data in unusual rows

Tom — Tue, 12 Oct 2021 23:00:14 GMT

How is it you are reading only the name row?

Any way here is way that involves reading the file twice. Once starting with the name row and one starting with the data row. Then using PROC DATASETS to rename the variables in the second one with the first row from the first one.

So let's assume you have the name of the XLSX file in the macro variable FNAME.

And also that you are reading from a sheet named TEST.

So this code will create a dataset named VALUES with the data from row 14 using the names from row 8.

proc import datafile="&fname" dbms=xlsx out=names replace ;
   getnames=no;
   range="test$A8:";
run;
proc import datafile="&fname" dbms=xlsx out=values replace ;
   getnames=no;
   range="test$A14:";
run;

proc transpose data=names(obs=1) out=name_list;
  var _all_;
run;
proc sql noprint;
  select catx('=',_name_,nliteral(col1))
    into :renames separated by ' '
    from name_list
    where upcase(_name_) ne upcase(col1)
  ;
quit;

proc datasets nolist lib=work;
  modify values ;
    rename &renames;
  run;
quit;

Re: Proc import - varnames and data in unusual rows

Kurt_Bremser — Wed, 13 Oct 2021 06:21:17 GMT

@jdsdog10 wrote:
xlsx

Save it to csv and read it with a data step.