topic Re: import excel file with numbers in column headings in SAS Procedures

import excel file with numbers in column headings

NWV — Tue, 20 Oct 2009 19:33:44 GMT

I have data something like this:

SampleID 1result 2result 3result
A 0.9 0.4 1.2
B 0.3 0.6 0.8
C 0.2 0.7 2.3

Which are in excel. I am very familiar with using the libname statement method to bring an excel file into SAS. BUT, as you can see the column names have leading numbers. Which SAS replaces with an _ at the time of import. This is problematic because I then lose the only identifying piece of information about that column. If SAS were to put a leading underscore in FRONT of the number, and not replace the number, that would be fine.

These data are coming directly from a laboratory instrument, and I am assured by the tecnicians that the column headings cannot be changed.

So, besides editing every excel file prior to import, is there a way to bring in the data from excel and not lose the useful column information? There are many, many of these excel files.......

Thank you,

NWV

Re: import excel file with numbers in column headings

Patrick — Tue, 20 Oct 2009 21:40:17 GMT

input SampleID $ '1result'n '2result'n.....

the syntax '...'n treats the string as a literal therefore allows you to use it as a variable name.

You better rename these variables later on or you will have the same issue over and over.
i.e. rename '1result'n = _1result;

...Ooops, sorry, didn't read your post good enough:
For a fast solution: Use the import wizard in SAS EG and you will get what you need.
The code generated by the import wizard will also give you some ideas how to solve this issue.

Also worth a try might be options validvarname=any; (not sure if this will have any effect).

HTH
Patrick

Message was edited by: Patrick

Re: import excel file with numbers in column headings

deleted_user — Tue, 20 Oct 2009 22:46:06 GMT

If you import the data from an Excel sheet formatted like you have it in your post this should work. It uses the label name with a character in front of it to rename the column. Assuming your first column will always be 'SampleID' the macro skips that column.

I modified a macro outlined in the SUGI paper at this link:
www2.sas.com/proceedings/sugi28/118-28.pdf

PROC IMPORT DATAFILE="C:\TEST.XLS"
OUT=TEST
REPLACE
DBMS = EXCEL ;
RUN;

options macrogen mprint mlogic;
%macro rename(lib=,dsn=);
options pageno=1 nodate;
proc contents data=&lib..&dsn;
title "Before Renaming All Variables";
run;
proc sql noprint;
select nvar-1 into :num_vars
from dictionary.tables
where libname="&LIB" and
memname="&DSN";
select distinct NAME into :var1-
:var%TRIM(%LEFT(&num_vars))
from dictionary.columns
where libname="&LIB" and
memname="&DSN" and name ne "SampleID";

select distinct %trim("A"||label) into :lvar1-
:lvar%TRIM(%LEFT(&num_vars))
from dictionary.columns
where libname="&LIB" and
memname="&DSN" and name ne "SampleID";

quit;
run;
proc datasets library=&LIB;
modify &DSN;
rename
%do i=1 %to &num_vars;
&&var&i=&&Lvar&i.
%end;
;
quit;
run;
options pageno=1 nodate;
proc contents data=&lib..&dsn;
title "After Renaming All Variables";
run;
%mend rename;
%rename(lib=WORK,dsn=TEST);

Re: import excel file with numbers in column headings

NWV — Wed, 21 Oct 2009 12:49:35 GMT

Well, the options validvarname=any worked to bring the column headers in just fine with leading numbers. How they will be handled in the rest of the program remains to be seen!

Thank you so much for the suggestions.

Re: import excel file with numbers in column headings

data_null__ — Wed, 21 Oct 2009 17:36:16 GMT

> How they will be handled in the rest of the program
> remains to be seen!

Rename them and set validvarname back to V7...

[pre]
options validvarname=any;
data test;
length '1results'n '2results'n '3results'n '444Resu'n 8;
set sashelp.class;
run;
proc sql;
select
cats
(
nliteral(name),
'=',
substr(name,anyalpha(name)),
substr(name,1,anyalpha(name)-1)
)
into :rename separated by ' '
from dictionary.columns
where libname eq 'WORK' and memname eq 'TEST' and anydigit(name) eq 1;

quit;
run;
proc datasets;
modify test;
rename &rename;
run;
contents data=test short;
quit;
options validvarname=v7;
[/pre]

Re: import excel file with numbers in column headings

Peter_C — Thu, 22 Oct 2009 12:29:45 GMT

"wondrous to be..."
nLiteral()
is new to me and looks to be very useful...
A quick check on an "outsize" name string returned an name literal that's longer.
The SASlog demonstrates an issue
[pre]52 data t;
53 name = nliteral("Thought w'ld try a string of arbiTrary $length OK?" );
54 put name= ;
55 call symput( 'name', name ) ;
56 run;

name="Thought w'ld try a string of arbiTrary $length OK?"N
NOTE: The data set WORK.T has 1 observations and 1 variables.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds

57 option validVarName= any ;
58 data trouble ;
59 set t ;
60 &name= name ;
ERROR: The variable named Thought w'ld try a string of arbiTrary $length OK? contains more than 32 characters.
61 run;

NOTE: The SAS System stopped processing this step because of errors.[/pre]

Is this behaviour to tolerate or recommend for "improvement"

PeterC

Re: import excel file with numbers in column headings

data_null__ — Thu, 22 Oct 2009 13:37:16 GMT

You have demonstrated that NLITERAL does not know anything about valid SAS names, and seems to be more of a special version of QUOTE function.

I guess you need to futher examine the result with NVALID function.

I would like a function that "transforms" a string into a valid SAS name.

But I reckon that could be accomplished with ANYFIRST/NOTFIRST and ANYNAME/NOTNAME Message was edited by: data _null_;

Re: import excel file with numbers in column headings

Peter_C — Thu, 22 Oct 2009 14:10:39 GMT

Another part of the valid-name-generation exercise requires information from elsewhere:
To be valid, a name must be unique in the context where it is used . ......
......
must be a way ....
effective, needs simple packaging, like nValid() and nLiteral() functions indicate is very close by......
I just don't want to start building hashtables to store and search for generated names.
Somewhere SAS has internally packaged the unique-name-management, for example, for access and import routines, that handle column names of "varying quality"
... ... ... hopefully we'll be informed by some "little birdie" if it might be on someone-else's wish-list ....

PeterC

Re: import excel file with numbers in column headings

data_null__ — Thu, 22 Oct 2009 18:22:18 GMT

I have seen the rules referred to as "variable name normalization rules" and they are dependent on the setting of the VALIDVARNAME system option. As you suggest it would "nice" to have a callable form. I guess we will have to call on our "old friend" the macro language.

Cut from docs

[pre]
SAS/ACCESS Specific Details

VALIDVARNAME= enables you to control which rules apply for SAS variable
names. For more information about the VALIDVARNAME= system option, see the
SAS Language Reference: Dictionary. The settings are as follows:

VALIDVARNAME=V7
indicates that a DBMS column name is changed to a valid SAS name by using
the following rules:

Up to 32 mixed-case alphanumeric characters are allowed.

Names must begin with an alphabetic character or an underscore.

Invalid characters are changed to underscores.

Any column name that is not unique when it is normalized is made unique by
appending a counter (0,1,2,...) to the name.

This is the default value for SAS Version 7 and later.
[/pre]