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 |
I have managed to resolve this by using / row=float;
Thanks for all your help and suggestions.
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
Using an ODS HTML BODY=filepath
then
ODMS HTML CLOSE - At the bottom of the tabulate
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
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.
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
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;
I have managed to resolve this by using / row=float;
Thanks for all your help and suggestions.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.