05-20-2016 12:04 PM
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)) ?
05-20-2016 12:29 PM
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;
05-20-2016 03:02 PM
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.
05-20-2016 04:42 PM
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.
05-20-2016 05:02 PM
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.
05-20-2016 05:41 PM
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.