BookmarkSubscribeRSS Feed
robby_beum
Quartz | Level 8

Good Afternoon,

I'm running EG 4.3 from my desktop on a 64-bit Windows Server and I'm trying to import an excel spreadsheet THAT DOESN'T HAVE HEADERS.

When I run the following, the first line in the spreadsheet (data) imports into SAS as my header columns. Apparently, there is no headers=no option for EXCELCS.

proc import out=P01.needed_output

            datafile= "C:\data\excel_spreadsheet_without_headers.xls"

            dbms=EXCELCS REPLACE;

      sheet="Sheet1";

      usedate=YES;

      scantime=YES;

      SERVER='rvwsascpt01';

      PORT=9621;

run;

I've also tried with no luck...

libname nhlbi pcfiles server="rvwsascpt01"

port=9621 path="C:\data\excel_without_headers.xls"

header=no;

Anyone run into this before and know how to resolve it?

6 REPLIES 6
art297
Opal | Level 21

Define "headers".  Do you mean variable names or something else?  Can you use the xls engine?

robby_beum
Quartz | Level 8

Good morning Art,

By "no headers", I mean the data starts in row 1 instead of having a header description like StudyID or First_Name in row 1 and the data starting in row 2.

Apparently when running EG 4.3 from a desktop on a 64-bit Windows Server, you have the use the EXCELCS engine.

Robby

art297
Opal | Level 21

According to the documentation the excelcs engine is extremely limited.  Unfortunately, according to Chris (the SAS Dummy) you don't have much choice.

You could try: DBDSOPTS= 'FIRSTOBS=1'

I'm not sure if that serves the same purpose as getnames=no, but it is definitely worth trying.

robby_beum
Quartz | Level 8

Yes, we're finding out the limitations of the EXCELCS engine.

I gave the DBDSOPTS= 'FIRSTOBS=1' a shot and unfortunately it pulls the data in row 1 in and turns it into the column headers in the sas dataset.

At this point, I'm just going to manually insert column headings (and grumble about EXCELCS) for this monthly job before I run the sas jobs. It's not ideal but at least I won't lose the first row of data.

Thank again, I appreciate your help.

Robby

Ksharp
Super User

Did you try the option : 

getnames=no;

or

range='xxxxxxx' ;

robby_beum
Quartz | Level 8

Good Morning Ksharp,

getnames=no is not a valid option when using the EXCELCS engine. http://support.sas.com/kb/41/060.html

The range "sort of works" but not really since it seems to place the first data line as the header no matter if you use $a0, $a1, $a2...

From the same link above:

•The EXCELCS method. This method can help with the GETNAMES= option, but not with the MIXED= option:
1.Insert a blank row above the data that you want to read in Excel.
2.Read the file using the RANGE= option, similar to the following example:

proc import datafile='c:\sastest\myfile2.xlsb'
   out=sasdata
   dbms=excelcs replace;
   range='test1$a2:e21'n;
run;

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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
  • 6 replies
  • 27571 views
  • 0 likes
  • 3 in conversation