BookmarkSubscribeRSS Feed
k_shide
Obsidian | Level 7

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

7 REPLIES 7
SASKiwi
PROC Star

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
Obsidian | Level 7
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..
k_shide
Obsidian | Level 7
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
Super User

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
Super User

@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
Obsidian | Level 7
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..

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 1221 views
  • 1 like
  • 3 in conversation