topic Proc Tabulate : out= default variable name issue in SAS Procedures

Proc Tabulate : out= default variable name issue

mav08 — Tue, 16 Dec 2008 15:57:28 GMT

Is there a method for the user to define variable names within the output dataset?
The below code is part of a production process in my workplace.
The variable name length of the macro variable &score is between 23 to 28.
The tabulate procedure defaults the variable names for the computed stats within the DecileOut dataset(var_stat ..score_mean score_min score_max).
When the combined variable name (&score_mean) is greater then 32, SAS defaults the variable name to 'Mean'. I am aware that the variable name length is limited to 32.
So my question: Can a user define variable names within the Output dataset in Proc Tabulate? Please provide examples.

proc tabulate data=temp2 noseps formchar=' ' out=DecileOut;
class r_score;
var &score ;
weight weight;
table r_one='' all,
&score*(min mean max)*f=percent8.2
;
run;

Re: Proc Tabulate : out= default variable name issue

Cynthia_sas — Tue, 16 Dec 2008 18:45:19 GMT

Hi:
You can always use RENAME=(old=new) Data set option when creating output datasets. That can be used most places you can specify a OUT= dataset.

I'd recommend 2 possibilities
1) taking all the vowels out of the &SCORE macro variable to make a new variable name with the stats appended or (this is a good approach, if you have numbered variables with the numbers out at position 27 or 28 of the name)
2) take the first 25 or 26 characters of the &SCORE macro variable to make a new variable name (this is a good approach if your variable names are unique in the first 25 or 26 characters and if your stat name plus an underscore is not more than 6 characters.)

I show an example below. If the &SCORE macro variable is less than 26, then you'll get the whole value. Note that in my code, I use &NEW in the RENAME statement and not &ALTNEW.

cynthia

ps OK, if you know macro processing, you know that I could make the code below WAY more complicated and automated with conditional %IF logic, etc in a macro program. But I wanted to keep this simple and something that didn't require a macro program.

[pre]
%let score = inventory;
%let new = %sysfunc(compress(&score,aeiou,i));
%let altnew = %substr(&score,1,26);

%put original variable name = &score;
%put variable name without vowels = &new;
%put alternate name shortened to 26 chars = &altnew;

proc tabulate data=sashelp.shoes
out=DecileOut(rename=(&score._min=&new._min
&score._mean=&new._mean
&score._max=&new._max));
class region product;
var &score ;
table product='' all,
&score*(min mean max);
run;

proc print data=DecileOut;
run;

[/pre]

Re: Proc Tabulate : out= default variable name issue

mav08 — Tue, 16 Dec 2008 19:44:15 GMT

Thanks Cynthia.
However this solution does not work. SAS errors out on the rename step.
Below is the log.

24 Mxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx_mean
_________________________________
213
ERROR 213-322: Variable name Mxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx_mean is longer than 32 characters.

NOTE 138-205: Line generated by the macro variable "PREBAD".
24 Mxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx_mean
_________________________________
23
ERROR 23-7: Invalid value for the RENAME option.

Re: Proc Tabulate : out= default variable name issue

Cynthia_sas — Tue, 16 Dec 2008 21:21:41 GMT

Hi:
Ah, yes, my bad. I didn't run into that issue with "INVENTORY" as the variable. So, the error comes from the &score._min, etc -- which means you have to use the statistic names in the OUT=. For more complicated logic, you might need a macro program or you just might need a datastep to rename your variables before the tabulate runs, so you have more reliable (and smaller) variable names.
cynthia

My (partial) output:
[pre]
decileout2 -- substring method

supercalifragilistic_ supercalifragilistic_ supercalifragilistic_
Obs Product _TYPE_ _PAGE_ _TABLE_ min mean max

1 Boot 1 1 1 374 187012.90 882080
2 Men's Casual 1 1 1 2176 379672.29 2881005
3 Men's Dress 1 1 1 538 290146.80 1847559

[/pre]

My revised code:
[pre]
options nodate nonumber nocenter ls=180;
data testdata;
set sashelp.shoes;
supercalifragilisticexpeialidoci = inventory;
run;

%let score = supercalifragilisticexpeialidoci;
%let new = %substr(&score,1,20);

proc tabulate data=testdata
out=DecileOut2(rename=(min=&new._min
mean=&new._mean
max=&new._max));
class region product;
var &score ;
table product='' all,
&score*(min mean max);
run;

proc print data=DecileOut2;
title 'decileout2 -- substring method';
run;
[/pre]

Alternative -- just rename long variable and then you don't have to worry about it. You'd now use &NEW in the TABULATE step without having to do a RENAME in TABULATE:
[pre]
%let score = supercalifragilisticexpeialidoci;
%let new = %substr(&score,1,20);

data alt;
set testdata(rename=(&score=&new));
run;

proc tabulate data=alt
out=DecileOut3;
class region product;
var &new ;
table product='' all,
&new*(min mean max);
run;

proc print data=DecileOut3 ;
title 'decileout3 different';
run;

[/pre]