proc import for excel2003, getnames does not produce variable names from excel sheet's 1st line.

[k_shide]

Dear Experts,

This is for SAS University Edition related question.

When you use proc import for excel2003, getname option does not produce variable names from excel sheet's 1st line.

The actual variable name becomes to be like 1_0 1_1 or something like this meanwhile the actual 1st line was like date, price, name etc.

Have you ever encountered similar issues and any work around?

Thank you very much,

Kaz

[SASKiwi]

Try saving your spreadsheet as a CSV file and use DBMS = CSV to import. Excel is notoriously picky as far as imports are concerned. CSV files give you much greater control.

[k_shide]

Thanks. I think CSV is simple and good to use. Only one issue is if you have 20-30 sheets, it may be redundant to create all those files..

[Reeza]

Post your code. 

[k_shide]

The code is as follows.
proc import datafile='/folders/myfolders/aaa.xls'
out=aaa dbms=xls replace;
getnames=yes;
run;

If you use getnames=no then it produce variables like var1, var2 .....

[Reeza]

Consider attaching the xls file then. You can change the extension to get it uploaded. 

Can you post a proc contents from two runs, one with getnames=yes and one with getnames=no. 

[Reeza]

@k_shide I'm unable to replicate your issue on SAS UE - on a Macintosh though. 

Someone else will have to verify Windows version.

proc import datafile='/folders/myfolders/VariableName.xls' 
out=test1 dbms=xls replace;
run;

proc import datafile='/folders/myfolders/VariableName.xls' 
out=test2 dbms=xls replace; Getnames=yes;
run;

title 'test1';
ods select variables;
proc contents data=test1;
run;
title 'test2';
ods select variables;
proc contents data=test2;
run;

Results:

test1

The CONTENTS Procedure

Alphabetic List of Variables and Attributes
#	Variable	Type	Len	Format	Informat	Label
2	Age	Num	8	BEST12.		Age
1	Name	Char	12	$12.	$12.	Name
3	Test	Num	8	BEST12.		Test

---

test2

The CONTENTS Procedure

Alphabetic List of Variables and Attributes
#	Variable	Type	Len	Format	Informat	Label
2	Age	Num	8	BEST12.		Age
1	Name	Char	12	$12.	$12.	Name
3	Test	Num	8	BEST12.		Test

[k_shide]

Thanks. I know you are using the University version with same codes and looks no issues... I just wonder if any format of excel sheet matters or browser I'm using... I don't know yet. Red Hat and Windows excel may matter. I am not sure..