How to use "labels" (Excel column headers) instead of SAS "names" when plotting?

[NKormanik]

I imported into SAS an Excel spreadsheet successfully.  SAS, however, decided to create a "name" for each column of data, in addition to maintaining "label" for each column.  The "labels" correctly correspond to column headings in the original Excel file.  The SAS "names," however, are different from these "labels" -- apparently converted to SAS-speak.

When it comes time to plot a graph, SAS wants me to use the "names" it created, as opposed to the "labels."

This gets really confusing.

Is there a way to use "labels" in a plot statement, as opposed to "names"?

Solution greatly appreciated.

Nicholas Kormanik

nkormanik@gmail.com

.

Code example using variable "names":

proc gcontour data=smoothed;

plot _0801*_1301=_05010

;

run;

quit;

[PaigeMiller]

I imported into SAS an Excel spreadsheet successfully. 

The answer to your question depends on how you did this import.

If you used PROC IMPORT, then you might want to turn on GETNAMES=YES; (although I thought it was the default). This should take the first row of the Excel spreadsheet and make it the SAS variable name.

If you used other methods for importing the spreadsheet, let us know, there is most likely a way to address this issue for other methods of importing.

[ChrisSelley]

If your column headers contain blanks or are repeated or are too long then SAS has to change them into valid SAS variable names

e.g. COLUMN HEADER will become COLUMN_HEADER or TYPE used in 3 columns will result in 3 variables called TYPE, TYPE1 and TYPE2

I believe most procedures will by default use Labels rather than Names

[ballardw]

I believe the OP is wanting to use the label instead of variable name in the PLOT statement. Which generally is not going to work in any procedures.

I suspect the only way Nicholas will be perfectly satisfied would be to have Excel column headers that are also acceptable SAS variable names.

[NKormanik]

Thanks ballardw.  Correct thinking.

After further searching for the answer..., the Excel column headers being imported into SAS can avoid the SAS-speak transformation if one uses the following prior to the import step:

options validvarname=any;

Using said option allows SAS to use pretty much whatever Excel column names one has -- including names with only numbers, names including spaces between words, etc.

After the import..., one can check the properties of the new SAS data file, and all the "names" should be identical to the "labels".

Then when it's time for the plotting step...  not entirely sure why, but it seems necessary to use the following form of code:

plot '20801'n * '21301'n = '50501'n

Simply using --- plot 20801 * 21301 = 50501 --- doesn't work.

Strange how SAS documentation generally blithely shows --- plot x * y = z.

Yeah, right.  Not that easy.

Thanks all.

Nicholas Kormanik

nkormanik@gmail.com

.

[Reeza]

How would SAS know that 20801 and 21301 aren't numbers but variables?

In some cases this is obvious and others not so much, ie is my_variable= other_variable but if the other_variable name is 20801 how would it know how to interpret that?

You can use the full descriptive names but then you're stuck using the literal notation, ie 'variable name'n.

Using labels instead is easier in my opinion.

[NKormanik]

Excellent point, Reeza.

Please tell us how to use "labels" instead?  Especially if it's easier.  Sure hope so.

[AncaTilea · Accepted solution]

Hi Nicholas,

One way to "use" the labels instead of variable "name" is to rename you variables name with the label. For example:

/*below, using SQL you can write a snippet of code that basically translates to this:

      name = label, and this statement will be repeated for all your variables */

proc sql;

    select catt(name,"=", Label)

    into: new_label separated by " "

    from dictionary.columns

    where libname = "WORK" & memname = "YOUR_DATA" ;*note the uppercase needs to be uppercase;

quit;

*you can now automatically rename all your variables to their correspondent label;

data want;

    set YOUR_DATA;

    rename &new_label.;

run;

HOWEVER

if your labels contain character like >, < {}...whatever special characters then you will need to tell SAS to replace those characters.

I learn that Regular Expressions (PRXchange) works wonderful.

Anyhow...

Best of luck!

Anca.