topic Proc Tabulate Top row data shifts in Excel in SAS Programming

Proc Tabulate Top row data shifts in Excel

Brandon16 — Mon, 26 Feb 2018 16:51:14 GMT

Hi,

How do you prevent a value from shifting up when my tabulate exports into Excel?

Example below: I want the values 68 and 0.8746 to be in line with the row below

		Count	Percent
Value	Month	68	0.8746
A	Dec-17

Re: Proc Tabulate Top row data shifts in Excel

Reeza — Mon, 26 Feb 2018 17:24:16 GMT

How are you exporting your data to Excel?

@Brandon16 wrote:

Hi,

How do you prevent a value from shifting up when my tabulate exports into Excel?

Example below: I want the values 68 and 0.8746 to be in line with the row below

Count Percent

Value Month 68 0.8746

A Dec-17

Re: Proc Tabulate Top row data shifts in Excel

Brandon16 — Mon, 26 Feb 2018 17:25:46 GMT

Using an ODS HTML BODY=filepath

then

ODMS HTML CLOSE - At the bottom of the tabulate

Re: Proc Tabulate Top row data shifts in Excel

Reeza — Mon, 26 Feb 2018 17:31:53 GMT

Well, that's creating an HTML file so that fact that its not rendering properly in Excel isn't surprising.

Have you tried ODS TAGSETS.EXCELXP or ODS EXCEL (SAS 9.4 TS1M3+) instead? You should get better results with either of those methods.

@Brandon16 wrote:

Using an ODS HTML BODY=filepath

then

ODMS HTML CLOSE - At the bottom of the tabulate

Re: Proc Tabulate Top row data shifts in Excel

Brandon16 — Mon, 26 Feb 2018 17:32:53 GMT

No, never used either of those

Re: Proc Tabulate Top row data shifts in Excel

Brandon16 — Mon, 26 Feb 2018 18:01:14 GMT

I have come across a nocellmerge but that isn't recognised within my code. I would love to know a way around this as it's very annoying how the data is now presented within excel. If anyone has any idea how to resolve this, I would be most grateful.

Re: Proc Tabulate Top row data shifts in Excel

Reeza — Mon, 26 Feb 2018 18:13:27 GMT

ODS TAGSETS.EXCELXP rendered the file fine in my tests. Is there a specific reason you can't switch to it?

https://support.sas.com/rnd/base/ods/odsmarkup/excelxp_demo.html

Re: Proc Tabulate Top row data shifts in Excel

Reeza — Mon, 26 Feb 2018 20:20:44 GMT

data ex1;
input treat    visit    ptn     score1     score2;
cards;
  1        1       1      6.8496     1.3007
  1        2       1     14.7009    14.4018
  1        3       1      8.9982     2.9965
  1        1       2      7.5940     0.1880
  1        2       2     14.2160    13.4321
  1        3       2     14.6928    14.3855
  2        1       3     10.4298     5.8596
  2        2       3     10.3169     5.6338
  2        3       3      5.4979     4.0041
  2        1       4      5.6657     3.6687
  2        2       4     13.1932    11.3864
  2        3       4     10.2387     5.4774
  2        1       1     13.5339    12.0679
  2        2       1      5.6718     3.6563
  2        3       1     14.5702    14.1405
  2        1       2      7.9719     0.9439
  2        2       2      7.7261     0.4522
  2        3       2     11.8993     8.7986
  1        1       3     14.7676    14.5353
  1        2       3      7.2651     0.4698
  1        3       3     11.8824     8.7647
  1        1       4      9.1276     3.2553
  1        2       4     10.5855     6.1711
  1        3       4      7.8723     0.7445
 
;
run;
proc format;
  value vi 1='3/20' 2='8/30' 3='11/03';
    value tr 1='Therapy 1' 2='Therapy 2';
run;

ods html file='c:\_localdata\demo_html.xlsx' style=meadow;

proc tabulate data=ex1;
  class ptn treat visit;
  var score1 score2;
  table ptn='Id #',
        mean=' '*score1='Drug A'*treat=''*visit=''*F=6.
        sum=' '*score2='Drug B'*treat=''*visit=''*F=6. / RTS=6.;
  format treat tr. visit vi.;
run;

ods html close;


ods tagsets.excelxp file='c:\_localdata\demo_xml.xml'  style=meadow;

proc tabulate data=ex1;
  class ptn treat visit;
  var score1 score2;
  table ptn='Id #',
        mean=' '*score1='Drug A'*treat=''*visit=''*F=6.
        sum=' '*score2='Drug B'*treat=''*visit=''*F=6. / RTS=6.;
  format treat tr. visit vi.;
run;

ods tagsets.excelxp close;

ods excel file='c:\_localdata\demo_excel.xlsx'  style=meadow;

proc tabulate data=ex1;
  class ptn treat visit;
  var score1 score2;
  table ptn='Id #',
        mean=' '*score1='Drug A'*treat=''*visit=''*F=6.
        sum=' '*score2='Drug B'*treat=''*visit=''*F=6. / RTS=6.;
  format treat tr. visit vi.;
run;

ods excel close;

Re: Proc Tabulate Top row data shifts in Excel

Brandon16 — Tue, 27 Feb 2018 09:57:19 GMT

I have managed to resolve this by using / row=float;

Thanks for all your help and suggestions.