BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Brandon16
Obsidian | Level 7

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  
1 ACCEPTED SOLUTION

Accepted Solutions
Brandon16
Obsidian | Level 7

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

 

Thanks for all your help and suggestions.

View solution in original post

8 REPLIES 8
Reeza
Super User

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    

 

Brandon16
Obsidian | Level 7

Using an ODS HTML BODY=filepath 

 

then 

 

ODMS HTML CLOSE - At the bottom of the tabulate

Reeza
Super User

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


 

Brandon16
Obsidian | Level 7
No, never used either of those
Brandon16
Obsidian | Level 7

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.

Reeza
Super User

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

Reeza
Super User
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;



Brandon16
Obsidian | Level 7

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

 

Thanks for all your help and suggestions.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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