Help using Base SAS procedures

PROC IMPORT Without Headers

Reply
Contributor
Posts: 72

PROC IMPORT Without Headers

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?

PROC Star
Posts: 7,362

Re: PROC IMPORT Without Headers

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

Contributor
Posts: 72

Re: PROC IMPORT Without Headers

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

PROC Star
Posts: 7,362

Re: PROC IMPORT Without Headers

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.

Contributor
Posts: 72

Re: PROC IMPORT Without Headers

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

Super User
Posts: 9,676

Re: PROC IMPORT Without Headers

Did you try the option : 

getnames=no;

or

range='xxxxxxx' ;

Contributor
Posts: 72

Re: PROC IMPORT Without Headers

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;

Ask a Question
Discussion stats
  • 6 replies
  • 7759 views
  • 0 likes
  • 3 in conversation