Is it possible to ods excel with values in each cells in SAS?

0
votes

I am trying to save SAS report into xlsx file. For this purpose, I used "ods excel" function. My hoping results was generated by "proc freq" procedure which has each row and col percentage. At this point, the problems came out. In the result xlsx file, counting values and each row and col percentage values are in the same cells. This make difficulties on following data handling steps. Is there any option that can handle this issue??

*now

year var1 var2
2010 3
3.0
15.0		 97
97.0
85.0

want

year var1 var2
2010 3 15
3.0 97.0
15.0 85.0
2
excelsasprocods
Show your code including the ODS EXCEL statement that might have STYLE= option. If running from a UI that writes your code, add the log to the question. – Richard
Try PROC TABULATE which lets you have more control. – Reeza

2 Answers

0
votes

Try creating an output table instead and export it with proc export. For example:

proc freq data=sashelp.cars noprint;
    tables make / out=freq_table;
run;

proc export 
    data = freq_table
    file = "/folder/myfile.xlsx"
    dbms = xlsx
    replace
    ;
run;

If you want a custom looking report, you'll want to use proc report on the output table with ods excel instead

0
votes

You appear to be producing a cross tabulation in Proc FREQ.

You can capture the ODS data that is used to created the output you don't like and manipulate it a way to create output that you do like.

The ODS OUTPUT, when transposed, creates a table that can be simply Proc PRINTED and will contain the repetitions you want.

Example:

ods listing;
ods select none;
ods output CrossTabFreqs=ctf;
proc freq data=sashelp.baseball;
  table position * division;
  where position like '%B';
  title "FREQ";
run;
ods select all;

data have;
  set ctf;
  if substr(_type_,1,1) = '0' then position = 'Total';
  if substr(_type_,2,1) = '0' then division = 'Total';
run;

proc transpose data=have out=want;
  by position notsorted;
  var frequency percent rowpercent colpercent;
  id division;
run; 

ods excel file='ct.xlsx' style=plateau;

proc print data=want(where=(N(east,west,total)));
run;

ods _all_ close;

enter image description here

Note:

If you capture the cross tabulation output (per Stu) you likely would have to create a pivot table in Excel, and even that would create merged cells you don't like. Pivot table in Excel does have built in filtering UI.