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

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

k_shide — Mon, 26 Dec 2016 04:32:08 GMT

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

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

SASKiwi — Mon, 26 Dec 2016 04:48:06 GMT

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.

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

Reeza — Mon, 26 Dec 2016 04:49:53 GMT

Post your code.

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

Reeza — Mon, 26 Dec 2016 21:27:41 GMT

@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

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

k_shide — Tue, 27 Dec 2016 05:41:24 GMT

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..

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

k_shide — Tue, 27 Dec 2016 05:42:47 GMT

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..

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

k_shide — Tue, 27 Dec 2016 06:06:38 GMT

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 .....

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

Reeza — Tue, 27 Dec 2016 06:23:54 GMT

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.