topic Re: Unmerged proc tabulate output in Excel in SAS Procedures

Unmerged proc tabulate output in Excel

Tom161 — Mon, 01 Sep 2014 10:28:56 GMT

Hello,

Only just signed up here - so hope i'm posting this in the right place.

I'm using SAS 9.1 and am trying to output Proc Tabulate to excel.

The issue is, the output automatically merges cells. Is there an option (or a work-around) that anyone is aware of on how to fix this? I need the value that corresponds to that variable to be outputted in each cell, even if it is the same value as the row above and beneath and so on.

Does this make sense? Please let me know if this isn't clear.

Many thanks in advance!

Tom

Re: Unmerged proc tabulate output in Excel

RW9 — Mon, 01 Sep 2014 10:43:12 GMT

Maybe post example data. My initial thoughts would be to out the proc tabulate to a dataset, then use proc report to print the data.

You could also look at arriving at your results by not using tabulate.

Re: Unmerged proc tabulate output in Excel

Tom161 — Mon, 01 Sep 2014 11:43:14 GMT

Thanks RW,

I've not used Proc Report before (fairly new SAS user) - so maybe this would be my best solution?

I've tried proc print to output the dataset - but that only gives me a count. (Whereas I need a count of 1 occurence, 2 occurences etc up to 26 occurences).

I've attached some sample excel SAS output above.

Thanks again,

Tom

Re: Unmerged proc tabulate output in Excel

Ksharp — Mon, 01 Sep 2014 11:53:03 GMT

Post it at

ODS and Base Reporting

Cynthia@sas will help you a lot . I think proc report is a good choice.

Xia Keshan

Re: Unmerged proc tabulate output in Excel

RW9 — Mon, 01 Sep 2014 12:39:45 GMT

Well from your output it should be pretty straightforward, something along the lines of:

proc tabulate ... out=report; /* This is the tabulate you already have, just add the out= */
...
run;

ods tagsets.excelxp file="c:\test.xlsx" style=statistical;
proc report data=temp nowd split='~'; /* Pro report to print the above data. */
column options grp n1-n26;
define options / "Option A";
define grp / "Group";
define n1 / "1~N";
...
run;

Re: Unmerged proc tabulate output in Excel

Cynthia_sas — Mon, 01 Sep 2014 15:22:47 GMT

Hi:

What you want cannot be done with TABULATE. It ALWAYS merges the row area cells, unlike PROC REPORT or PROC PRINT, which do NOT merge the cells for detail reports. PROC REPORT will perform spanned rows, if you want it to but you have to ask for it. Anyway, the default will show empty row header cells for GROUPED reports, but you can make a "helper" variable to fill every row using a COMPUTE block. The challenge with outputting TABULATE to a dataset and then trying to print it is that the dataset will be "flattened" and not in the same structure as the tabulate cross-tab -- which, I assume is the output structure you want. But without any data to test against, it is hard to come up with code. And, you did NOT explain how you were getting your output into Excel -- ODS HTML, ODS TAGSETS.EXCELXP, ODS CSV?? -- the Excel file that you posted could be either HTML or TAGSETS.EXCELXP -- you did not say which. But, let's assume that your variables are called OPTA, GRP, N1, then your PROC REPORT code would look like the program below.

In the PROC REPORT part of the program, a "helper" column is created called DISPA -- it is meant to display the value of OPTA for every row. To make life easy, I made OPTA and DISPA both character. At the beginning of each value of OPTA, the variable value is grabbed and put into a temporary variable called HOLDIT. Then in the COMPUTE block for DISPA, the HOLDIT variable is used to fill the value of DISPA on that row. Since DISPA is now a duplicate of OPTA, except with a value on every row, the OPTA variable can be hidden with the NOPRINT option. The PROC REPORT code needs COMPLETEROWS so that you will see A, B, C, D, etc, on every row, even if there is no value for GRP on that row (which is not the case in my fake data, but appears to be the case in your data). In your TABULATE output, the cells without any counts were set to missing, PROC REPORT sets those values to 0.

In the absence of any data from you, I made some fake data using SASHELP.SHOES. I also put in subtotals and an overall report total and a summary column on the far right and "reader breaks" between every group on the report.

cynthia

** make some fake data;
data fakedata(keep=opta grp n1 sales);
length opta $4 grp $1;
set sashelp.shoes;
where stores le 26;
** make N1;
n1 = stores;
** make OPTA;
if substr(region,1,1) = 'A' then opta = '4005';
else if substr(region,1,1) = 'C' then opta = '4003';
else if substr(region,1,1) in ('E', 'M') then opta = '4004';
else if substr(region,1,1) in ('P', 'S') then opta = '4002';
else opta = 4006;
** make GRP;
if product = 'Slipper' then product = 'A';
else if product = "Men's Casual" then product = 'C';
else if product = "Women's Casual" then product = 'F';
else if product = 'Sandal' then product = 'G';
grp = substr(product,1,1);
** label variables;
label opta = 'Option A'
grp = 'Group'
n1 = 'N1';
format sales comma10.;
run;

proc sort data=fakedata;
by opta grp;
run;
ods listing close;

title;

ods tagsets.excelxp file='c:\temp\toExcel.xml' style=printer;

proc report data=fakedata completerows nowd;
column opta dispa grp n,n1 sales=tot;
define opta / group noprint order=data;
define dispa / computed 'Option A' style(column)=Header;
define grp / group style(column)=Header;
define n1 / across;
define n / ' ';
define tot / n 'Total' f=comma10.;
compute before opta;
    holdit = opta;
endcomp;
compute dispa / character length=14;
    dispa=holdit;
endcomp;
break after opta / summarize style=Header;
rbreak after / summarize style=Header;
compute after;
    dispa = 'Total';
endcomp;
compute after opta;

** custom text at subtotal;
dispa = catx(' ',holdit,'Total');

** blank line at subtotal;
line ' ';
endcomp;
run;
ods _all_ close;