DATA Step, Macro, Functions and more

Proc Tabulate Top row data shifts in Excel

Accepted Solution Solved
Reply
Contributor
Posts: 42
Accepted Solution

Proc Tabulate Top row data shifts in Excel

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 

 

  CountPercent
ValueMonth680.8746
ADec-17  

Accepted Solutions
Solution
‎02-27-2018 04:57 AM
Contributor
Posts: 42

Re: Proc Tabulate Top row data shifts in Excel

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

 

Thanks for all your help and suggestions.

View solution in original post


All Replies
Super User
Posts: 23,267

Re: Proc Tabulate Top row data shifts in Excel

Posted in reply to Brandon16

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    

 

Contributor
Posts: 42

Re: Proc Tabulate Top row data shifts in Excel

Using an ODS HTML BODY=filepath 

 

then 

 

ODMS HTML CLOSE - At the bottom of the tabulate

Super User
Posts: 23,267

Re: Proc Tabulate Top row data shifts in Excel

Posted in reply to Brandon16

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


 

Contributor
Posts: 42

Re: Proc Tabulate Top row data shifts in Excel

No, never used either of those
Contributor
Posts: 42

Re: Proc Tabulate Top row data shifts in Excel

Posted in reply to Brandon16

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.

Super User
Posts: 23,267

Re: Proc Tabulate Top row data shifts in Excel

Posted in reply to Brandon16

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

Super User
Posts: 23,267

Re: Proc Tabulate Top row data shifts in Excel

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;



Solution
‎02-27-2018 04:57 AM
Contributor
Posts: 42

Re: Proc Tabulate Top row data shifts in Excel

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

 

Thanks for all your help and suggestions.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 107 views
  • 0 likes
  • 2 in conversation