proc report across with out to data set

[Ronein]

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 ?

[manalimokal28]

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.

[PaigeMiller]

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).

[Cynthia_sas]

Hi: The short answer to your question is that PROC REPORT will only output the absolute column names that it assigns to the ACROSS items. You could macro-ize some kind of solution, but that is not "vanilla" PROC REPORT. Using RENAME is what most folks do when/if they need to create an OUTPUT dataset. However, as pointed out by others, generally wide datasets are not useful for subsequent SAS procedures.
Cynthia

[Kathryn_SAS]

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.

[PaigeMiller]

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".

[ballardw]

---

@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.

[Cynthia_sas]

Hi @PaigeMiller, you're right that the times I have used a macro for this are few and far between and usually only when I know absolutely the number of unique columns generated by the ACROSS item and only in the circumstances where the post-processing was to immediately send the output dataset to a second report step -- not to a graph or stat procedure.
Cynthia

[ballardw]

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.

[Tom]

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: