BookmarkSubscribeRSS Feed
verleger
Calcite | Level 5

Does anyone knows how to read the .XLS file using proc import method when the excel file has two headers and not one.  If the file contain only one header SAS 9.3 is perfectly able to read it, but unfortunately with two

Headers it fails every time. 

example: This works if the excel sheet has only one header,

%macro inp(dat,dsn);

proc import datafile="/project/myfiles/data/&dat"

  out=&dsn

  dbms=xls replace;

  DATAROW=2;

  getnames=yes;

run;

I tried everything(below) that I knew for two headers file,but SAS 9.3 can not read it.

First failedTrial,

%macro inp(dat,dsn);

proc import datafile="/project/myfiles/data/&dat"

  out=&dsn

  dbms=xls replace;

  DATAROW=3;

  getnames=yes;

run;

Second failed Trial,

%macro inp(dat,dsn);

proc import datafile="/project/myfiles/data/&dat"

  out=&dsn

  dbms=xls replace;

  startrow=2;

  DATAROW=3;

  getnames=yes;

run;

Second failed Trial,

%macro inp(dat,dsn);

proc import datafile="/project/myfiles/data/&dat"

  out=&dsn

  dbms=xls replace;

  startrow=2;

  DATAROW=3;

  getnames=NO;

run;

if Anyone can share their experience with this , if had one, it will be definitely appreciated.

Verleger

4 REPLIES 4
Tom
Super User Tom
Super User

You should be able to use both rows for names.

SAS/ACCESS(R) 9.2 Interface to PC Files: Reference, Second Edition

proc import datafile="&fname" dbms=xls out=test4 replace;

  startrow=3 ; namerow=1; endnamerow=2;

run;

verleger
Calcite | Level 5

Thanks Tom for your Input. 

But unfortunately those methods do not work with SAS 9.3 version and also I have Office 2010 which I am not sure if it contributes to the failure as being compatible or not.

I believe those method such as namerow or endnamerow will be only useful with earlier version of SAS.

IF there was a way that I could  skip the first row and only read the second row as my column names and third row as start of data , then that will do it also for me. but for now SAS 9.3 refuses

to accept my second row as column names and my data starting at third row.  Just trying to tell SAS that my first row is garbage ! do not read it.

Tom
Super User Tom
Super User

Works fine for me with 9.3 on Unix. 

%let wdir=%sysfunc(pathname(work));

data test ;

  input (x y z) ($) ;

cards;

a b c

1 2 3

;;;;

proc export data=test outfile="&wdir/test.xls"

  dbms=xls replace ;

run;

proc import datafile="&wdir/test.xls" out=test1

  dbms=xls replace ;

run;

proc import datafile="&wdir/test.xls" out=test2

  dbms=xls replace ;

  namerow=2; startrow=3;

run;

data _null_;

set test1 ;

put (_all_) (=);

run;

data _null_;

set test2;

put (_all_) (=);

run;

Ksharp
Super User

Try RANGE=  option with proc import + excel .

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 952 views
  • 0 likes
  • 3 in conversation