BookmarkSubscribeRSS Feed
doakley
Calcite | Level 5

1)  I am having trouble controlling the format of some output from SAS to an Excel file: for instance, when I'm using age-groups as my variable, the column or row label 10-14 is routinely interpreted as a date (14-Oct).  Here's the code:

ods html path="c:\temp" (url=none) file="&YEAR._I11.xls" style=minimal;

PROC PRINT NOOBS LABEL DATA=MD6H;

      TITLE1 'TABLE I11';

      TITLE2 'DIVORCES BY AGE-GROUP OF HUSBAND, BY AGE-GROUP OF WIFE';

      TITLE3 "KANSAS, &YEAR";

      FORMAT HUS_AGE AGE_A.;

      LABEL HUS_AGE='AGE-GROUP OF HUSBAND'

The label on COL2 is reinterpreted as a date. If I try COL2="'10-14" [double quote,single quote,10-14,double quote], the output is '10-14, with a visible quote mark. Same if I use COL2='''10-14' [single quote,single quote,single quote,10-14,single quote]. The same problem exists for rows when this age-group comes in through the FORMAT statement.

2) How do I use ods to put a header over a set of Excel columns?

Thanks,

David

            COL1='TOTAL' COL2='10-14' COL3='15-19' COL4='20-24'

            COL5='25-29' COL6='30-34' COL7='35-39'

            COL8='40-44' COL9='45-49' COL10='50-54' COL11='55-59'

            COL12='60-64' COL13='65-69' COL14='70-74' COL15='75-79'

            COL16='80 & Over' COL17='N.S.';

RUN;

ods html close;

1 REPLY 1
Cynthia_sas
SAS Super FREQ

Hi:

  You will have to send a Microsoft format from SAS to Excel. Since you are using HTML-based techniques to create a file for Excel, this points to 2 changes to your code:

 

1) I would recommend using ODS MSOFFICE2K as the destination instead of ODS HTML. ODS HTML destination creates HTML 4.0 "flavor" of HTML tags, which Microsoft doesn't always like. On the other hand, ODS MSOFFICE2K creates Microsoft HTML "flavor" of HTML tags (yes, Microsoft created their own flavor of HTML).

  

2) When Microsoft created their own "flavor" of HTML, they also created their own "flavor" of CSS style properties that you can use to send Microsoft formats to Excel. for HTML-based files, these correspond to using mso-number-format style property with the HTMLSTYLE attribute.

  What you need to do is tell Excel to treat your column as TEXT, not as a number (or date). So the "magic" character that does this is the @, which represents text in a Microsoft format.

  I show an example of this on pages 2-4 of this paper.

http://support.sas.com/resources/papers/proceedings11/266-2011.pdf (look at the HTMLSTYLE override example for the ISBN variable).

cynthia

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
  • 1 reply
  • 657 views
  • 3 likes
  • 2 in conversation