topic Re: PROC IMPORT Without Headers in SAS Procedures

PROC IMPORT Without Headers

robby_beum — Tue, 23 Oct 2012 21:08:56 GMT

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?

Re: PROC IMPORT Without Headers

art297 — Tue, 23 Oct 2012 22:36:45 GMT

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

Re: PROC IMPORT Without Headers

Ksharp — Wed, 24 Oct 2012 03:02:15 GMT

Did you try the option :

getnames=no;

range='xxxxxxx' ;

Re: PROC IMPORT Without Headers

robby_beum — Wed, 24 Oct 2012 13:58:40 GMT

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

Re: PROC IMPORT Without Headers

robby_beum — Wed, 24 Oct 2012 14:04:17 GMT

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;

Re: PROC IMPORT Without Headers

art297 — Wed, 24 Oct 2012 14:12:44 GMT

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.

Re: PROC IMPORT Without Headers

robby_beum — Wed, 24 Oct 2012 14:49:39 GMT

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