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?
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
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.
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
Did you try the option :
getnames=no;
or
range='xxxxxxx' ;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: