DATA Step, Macro, Functions and more

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

Reply
Contributor
Posts: 20

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

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

Super User
Posts: 3,260

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

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.

Contributor
Posts: 20

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

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..
Super User
Posts: 19,855

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

Post your code. 

Contributor
Posts: 20

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

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

Super User
Posts: 19,855

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

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. 

 

Super User
Posts: 19,855

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

@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
Contributor
Posts: 20

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

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..
Ask a Question
Discussion stats
  • 7 replies
  • 213 views
  • 1 like
  • 3 in conversation