Hello
When I run proc report with across then printed output show
proc report data = SasHelp.Cars;
column Type Mpg_City;
define Type/across descending;
define Mpg_City/display;
run;
When I add output to data set option then I see that columns names are : _C1_ _C2_ ...._C6_
How can I tell sas to create columns names -Wagon Truck Sports Sedan SUV Hybrid?
I know that I can use rename but my question is if can tell sas to create these name directly ?
proc report data = SasHelp.Cars out=MyOutputDataset;
column Type, Mpg_City;
define Type/across descending 'Type';
define Mpg_City/display 'Mpg_City';
run;
In this code, I added the 'out=MyOutputDataset' option to store the output in a dataset named 'MyOutputDataset'. The 'display' statement within the 'define' statement for 'Type' and 'Mpg_City' columns specifies the custom display names 'Type' and 'Mpg_City' respectively.
When you run this code, the output dataset 'MyOutputDataset' will have columns named 'Type' and 'Mpg_City' as you specified.
Why do you need a wide SAS data set? We have repeatedly advised you to not create wide data sets. Please explain.
PROC REPORT is for creating reports. Just because you can create a data set from PROC REPORT does not mean you should create SAS data sets from PROC REPORT. (The only use I have ever had from the output data set from PROC REPORT is to debug my PROC REPORT code so I can see what columns contain what data and whether the column of interest is _C5_ or _C6_ and so on).
The behavior you are seeing is what is expected and documented below:
The output data set contains one variable for each column of the report. PROC REPORT tries to use the name of the report item as the name of the corresponding variable in the output data set. However, it cannot perform this substitution if a data set variable is under or over an across variable or if a data set variable appears multiple times in the COLUMN statement without aliases. In these cases, the name of the variable is based on the column number (_C1_, _C2_, and so on).
There is not an automatic way to rename the variables. You could use macro logic to build the RENAME list.
The idea of writing a macro to fix this makes me extremely uncomfortable, and if I was going to spend time on something, it would be how best to avoid using this wide data set (so no macro needed) and obtain the results I want from the original long data set.
Even in the case where there is only one across variable, writing the macro would need to take into account the setting of the ORDER= option and other options such as the DESCENDING option, the MISSING option, the PRELOADFMT option, as well as (possibly) the formats assigned to this variable and order of the data values in the data set. If there are several across variables, or several (non-across) variables nested inside an across variable, the programming becomes even more complicated.
To quote Weird Al Yankovic, "I'd rather clean all the bathrooms in Grand Central Station with my tongue".
@PaigeMiller wrote:
The idea of writing a macro to fix this makes me extremely uncomfortable, and if I was going to spend time on something, it would be how best to avoid using this wide data set (so no macro needed) and obtain the results I want from the original long data set.
Even in the case where there is only one across variable, writing the macro would need to take into account the setting of the ORDER= option and other options such as the DESCENDING option, the MISSING option, the PRELOADFMT option, as well as (possibly) the formats assigned to this variable and order of the data values in the data set. If there are several across variables, or several (non-across) variables nested inside an across variable, the programming becomes even more complicated.
To quote Weird Al Yankovic, "I'd rather clean all the bathrooms in Grand Central Station with my tongue".
Since Proc Report is one of the few procedures that supports multilabel formats the macro would also have to deal with potential use of the MLF option for the formats.
It may be a good idea to describe exactly what you intend to do with the output data set with the multiple columns for type.
I am having a hard time coming up with a clean use for such a data set that looks like proc report output. It might be that what you want to do is possible without creating this intermediate data set.
No idea why you would want such a strange dataset, but perhaps this code can be used to make such a dataset.
filename code temp;
data _null_;
file code;
if 0 then set sashelp.cars(keep=type);
declare hash h(dataset:'sashelp.cars',ordered:'d');
h.definekey('type');
h.definedata('type');
h.definedone();
declare hiter iter('h');
rc = iter.first();
do while (rc = 0);
length nliteral $65 ;
nliteral=nliteral(type);
put 'if type=' type :$quote. 'then ' nliteral '=1;';
rc = iter.next();
end;
stop;
run;
data want;
set sashelp.cars(keep=type);
%include code / source2;
set sashelp.cars(keep=Mpg_City);
drop type;
run;
proc print data=want (obs=10);
run;
proc report data = SasHelp.Cars;
column Type Mpg_City;
define Type/across descending;
define Mpg_City/display;
run;
Results:
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.