topic Re: How to automatically rename of the ACROSS variables in Proc Report in SAS Procedures

How to automatically rename of the ACROSS variables in Proc Report

nketata — Fri, 20 May 2016 16:04:44 GMT

Hi there,

if you run this proc report, the across variables in the output file will be called _C3_ _C4_ and _C5_

proc report data= sashelp.cars nowd out=alpha (drop=_break_ ) ;

column make model type, cylinders;

define type / across;

where invoice ge 60000 ; run

Is there an option enabling SAS to rename the across variables as SUV Sedan and Sports without having to do it manually (drop=_break_ rename=(_C3_=SUV _C4_=SEDAN _C5_=SPORTS)) ?

Thanks

Re: How to automatically rename of the ACROSS variables in Proc Report

BrunoMueller — Fri, 20 May 2016 16:29:27 GMT

How about using Proc TRANSPOSE for this, then there is no need for renaming the columns. The ID statements defines the variable, where the content of the variable defines the name of the transposed column.

Here is a sample:

proc sort data=sashelp.cars out=cars_s;
  where invoice ge 60000;
  by make model ;
run;

proc transpose 
  data=cars_s
  out=crs_trsp
;
  by make model;
  var cylinders;
  id type;
run;

Bruno

Re: How to automatically rename of the ACROSS variables in Proc Report

nketata — Fri, 20 May 2016 19:02:40 GMT

Thanks but an ID value can occur multiple times and the proc transpose would not be possible.

In that case, I would have to do a first proc report to group the variables and get unique values in the ID, then transpose, then do a second proc report to get the final table.

Re: How to automatically rename of the ACROSS variables in Proc Report

ballardw — Fri, 20 May 2016 20:42:49 GMT

Depending on the actual statistics or manipulation maybe this approach gets you close.

proc summary data= sashelp.cars nway;
   where invoice ge 60000 ; 
   class make model type;
   var cylinders;
   output out = temp (drop=_:) max=;
run;

proc transpose data=temp out=want (drop=_name_);
     ;
   var cylinders   ;
   id type;
   by  make model;
run;

Summary creates unique combinations of the NON-id variable and ID value would only occur once within each combination of the by variables. If your actual data /summarization is more complex then you need to provide an example with all of the complexities.

Re: How to automatically rename of the ACROSS variables in Proc Report

nketata — Fri, 20 May 2016 21:02:07 GMT

Thanks for your reply.

All the replies I got are telling me that there is no command in proc report that would do the job directly; it has to be in 2 steps.

Re: How to automatically rename of the ACROSS variables in Proc Report

ballardw — Fri, 20 May 2016 21:41:36 GMT

A part of the issue is that it would be VERY hard for Proc Report to do as you desire because you may have layered across variables which could very well result in multiple variables with the same 2nd level name. Currently you get col4-col6 partially because you can't repeat Cylinder as a name.

It may be worth asking is the Proc Report more important or the Dataset? If the two are not "compatible" then generate the datastep separately. Let the computer do the routine and tedious repetitive work.

I have stuff where I use Proc tabulate basically to create an output set then use Tabulate again to display the results after some manipulation for the final report that goes to the users.

Re: How to automatically rename of the ACROSS variables in Proc Report

LinusH — Sun, 22 May 2016 06:30:21 GMT

Why do you want to this output?
How will you use it?