topic Re: proc import xlsx file into sas in SAS Programming

proc import xlsx file into sas

kakarla_p6 — Mon, 18 Nov 2019 16:19:51 GMT

i am completely new to programming and a beginner to sas learning.

how to write keep or drop option in proc import while importing an excel file when it has spaces in the column names of its excel sheet

the following are the program that i wrote and the log warning.

options validvarname=v7;
proc import
datafile= '/home/u43101927/EPG194/Data/np_info.xlsx' out=species (keep=Park Name Category Scientific Name Common Names)
dbms=xlsx replace;
sheet="species";
getnames=yes;
run;

1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

71 options validvarname=v7;

72 proc import

73 datafile= '/home/u43101927/EPG194/Data/np_info.xlsx' out=species (keep=Park Name Category Scientific Name Common Names)

74 dbms=xlsx replace;

75 sheet="species";

76 getnames=yes;

77 run;

NOTE: Variable Name Change. Species ID -> Species_ID

NOTE: Variable Name Change. Park Name -> Park_Name

NOTE: Variable Name Change. Scientific Name -> Scientific_Name

NOTE: Variable Name Change. Common Names -> Common_Names

NOTE: Variable Name Change. Record Status -> Record_Status

NOTE: Variable Name Change. Conservation Status -> Conservation_Status

NOTE: One or more variables were converted because the data type is not supported by the V9 engine. For more details, run with

options MSGLEVEL=I.

WARNING: The variable Park in the DROP, KEEP, or RENAME list has never been referenced.

WARNING: The variable Name in the DROP, KEEP, or RENAME list has never been referenced.

WARNING: The variable Scientific in the DROP, KEEP, or RENAME list has never been referenced.

WARNING: The variable Name in the DROP, KEEP, or RENAME list has never been referenced.

WARNING: The variable Common in the DROP, KEEP, or RENAME list has never been referenced.

WARNING: The variable Names in the DROP, KEEP, or RENAME list has never been referenced.

NOTE: The import data set has 119259 observations and 13 variables.

NOTE: WORK.SPECIES data set was successfully created.

NOTE: PROCEDURE IMPORT used (Total process time):

real time 13.69 seconds

user cpu time 13.65 seconds

system cpu time 0.04 seconds

memory 42037.78k

OS Memory 75752.00k

Timestamp 11/18/2019 04:16:33 PM

Step Count 132 Switch Count 4

Page Faults 0

Page Reclaims 10399

Page Swaps 0

Voluntary Context Switches 41

Involuntary Context Switches 14

Block Input Operations 0

Block Output Operations 4616

79 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

please help me to figure out the problem.

Re: proc import xlsx file into sas

r_behata — Mon, 18 Nov 2019 16:26:15 GMT

This option has taken care of the spaces in the column names by replacing with the Underscores , which is apparent in the log :

 options validvarname=v7;

Just modify your keep list accordingly :

proc import
datafile= '/home/u43101927/EPG194/Data/np_info.xlsx' out=species (keep=Park_Name Category Scientific_Name Common_Names)
dbms=xlsx replace;
sheet="species";
getnames=yes;
run;

Re: proc import xlsx file into sas

Kurt_Bremser — Mon, 18 Nov 2019 16:27:23 GMT

Note the nice thing that SAS is doing for you by converting the nonstandard names (with blanks) to standard names (with underlines); use those standard names in the keep= option.

Re: proc import xlsx file into sas

kakarla_p6 — Mon, 18 Nov 2019 16:39:26 GMT

believe me, i spent about an hour working on this with different possibilities including that underscore in the variable name also. it didn't worked out then.

but this time it worked fine! i might have done some other possible mistake!

it worked. thanks.

Re: proc import xlsx file into sas

kakarla_p6 — Mon, 18 Nov 2019 16:40:33 GMT

Noted!

Thank you.

it worked.

Re: proc import xlsx file into sas

Cynthia_sas — Mon, 18 Nov 2019 16:45:33 GMT

HI:
If you watch the demos where we discuss importing Excel files using PROC IMPORT, I believe there are notes about using VALIDVARNAME=V7 so that any column names with spaces are altered to have underscores. Otherwise, you have to deal with the spaces in the column names in subsequent programs by using a different naming conventions. Because you were using VALIDVARNAME=V7, your KEEP= option needed to use the altered names, not the Excel names.
Cynthia

Re: proc import xlsx file into sas

ballardw — Mon, 18 Nov 2019 23:06:04 GMT

The SAS system is pretty nice with some things. One is that when you substitution like that in the log then you can click on the replace name, copy and paste into the editor.

The obnoxious files where the column headers exceed 32 characters and have the same first 32 characters are not so nice as SAS will give you VARxx where XX is the column number.

Or when the column heading is repeated. You may have some finding out which of Total, Total12, Total18, Total21 (or more) that you actually want for a given purpose.