BookmarkSubscribeRSS Feed
lloraine
Calcite | Level 5
I am using the following code.
ods html file='N:\admin\Linda\SAS\CHM_ClientLevel.xls' style=minimal;
proc print data=WORK.CHM_CLient_Level label noobs;
var CL_N / style(data)={htmlstyle='vnd.ms-excel.numberformat:@'};
var CL_NM CL_EFF_FRM_DT CL_EFF_TO_DT CL_CAN_DT CL_REIN_DT CHM_ENROLLMENT_TYPE CHM_ENROLLMENT_IDENTIFIER CLIENT_STATUS
ACTIVEC1 CANCELC1 ACTIVEM1 CANCELL1;
run;
ods _all_ close;
ods listing;

I would like to format the dates: CL_EFF_FRM_DT etc to format mmddyy8.
I do this and in Excel I get all stars. What am I doing wrong.
4 REPLIES 4
Cynthia_sas
SAS Super FREQ
Hi:
When I do this:
[pre]
data chm_client_level;
infile datalines dsd;
input cl_n
CL_EFF_FRM_DT : date7.
CL_EFF_TO_DT : mmddyy8.
TestNum;
return;
datalines;
001,15nov50,11/15/08,123456
002,23aug51,09/01/08,123456
003,01jan63,12/31/08,123456
;
run;

ods listing;
ods msoffice2k file='c:\temp\CHM_ClientLevel.xls' style=minimal;
proc print data=WORK.CHM_CLient_Level label noobs;
var CL_N / style(data)={htmlstyle='mso-number-format:@'};
var CL_EFF_FRM_DT CL_EFF_TO_DT /
style(data)={htmlstyle='mso-number-format:mm\/dd\/yy'};
var TestNum;
format CL_EFF_FRM_DT CL_EFF_TO_DT mmddyy8. TestNum 2.;
run;
ods _all_ close;
[/pre]

I get a formatted date in the Excel file -- with a 2 digit year. I also get a formatted date in the SAS output with a 2 digit year because of the format statement. No matter how I read the data, the SAS format is generally only used to produce the SAS output file. With LISTING or HTML in a browser, you don't have any issue. But, in general, SAS date or currency formats are not respected by Excel when it opens the HTML file and puts the numbers in spreadsheet cells. Just because you give the file an XLS extension does not mean that SAS is creating an Excel file. It just means that you are giving your HTML file an extension that will cause Excel to launch when the file is double clicked. To verify that the file is still an HTML file, open your "XLS" file with Notepad or a text editor and you will see that SAS and ODS are writing HTML tags.

So, that means you have to use HTML methods (CSS style properties) to format the numbers in a way that Excel can use. Just like you are formatting your CL_N to be a number, I use mso-number-format for my HTMLSTYLE attribute value to send the Excel format for the date. I use mso-number-format instead of 'vnd.ms-excel.numberformat:@ -- just because I've had better experience with it and all my examples are coded using mso-number-format. In my example the [pre] \/ [/pre] is NOT the capital V it is the 2 slashes backslash \ then forward slash / together with no spaces in between.

Generally, in SAS output (not in Excel) asterisks are a sign that a format is too small. In this instance, if you run the above code, you WILL see asterisks in Excel for the TestNum variable and you also get this message in the log:
[pre]
NOTE: At least one W.D format was too small for the number to be printed. The decimal may be shifted by the "BEST" format.
[/pre]

This is because the format of 2. is too small for the variable TestNum. If I increase the format for TestNum to 6., then the asterisks in Excel would go away, because SAS would have a big enough format to use on the number.

cynthia
lloraine
Calcite | Level 5
I tried the following:
ods msoffice2k file='N:\admin\Linda\SAS\CHM_ClientLevel.xls' style=minimal;
proc print data=WORK.CHM_CLient_Level label noobs;

var CL_N / style(data)={htmlstyle='vnd.ms-excel.numberformat:@'};
var CL_EFF_FRM_DT / style (data)={htmlstyle='mso-number-format:mm\/dd\/yy'};
var CL_NM CL_EFF_FRM_DT CL_EFF_TO_DT CL_CAN_DT CL_REIN_DT CHM_ENROLLMENT_TYPE CHM_ENROLLMENT_IDENTIFIER CLIENT_STATUS
ACTIVEC1 CANCELC1 ACTIVEM1 CANCELL1;
format CL_EFF_FRM_DT MMDDYY8.;
run;
ods _all_ close;
ods listing;

I got errors after the ODS statement - ERROR 180-322: Statement is not valid or it is used out of proper order.
lloraine
Calcite | Level 5
I tried the following:
ods msoffice2k file='N:\admin\Linda\SAS\CHM_ClientLevel.xls' style=minimal;
proc print data=WORK.CHM_CLient_Level label noobs;

var CL_N / style(data)={htmlstyle='vnd.ms-excel.numberformat:@'};
var CL_EFF_FRM_DT / style (data)={htmlstyle='mso-number-format:mm\/dd\/yy'};
var CL_NM CL_EFF_FRM_DT CL_EFF_TO_DT CL_CAN_DT CL_REIN_DT CHM_ENROLLMENT_TYPE CHM_ENROLLMENT_IDENTIFIER CLIENT_STATUS
ACTIVEC1 CANCELC1 ACTIVEM1 CANCELL1;
format CL_EFF_FRM_DT MMDDYY8.;
run;
ods _all_ close;
ods listing;

I got errors after the ODS statement - ERROR 180-322: Statement is not valid or it is used out of proper order.
lloraine
Calcite | Level 5
I tried the following:
ods msoffice2k file='N:\admin\Linda\SAS\CHM_ClientLevel.xls' style=minimal;
proc print data=WORK.CHM_CLient_Level label noobs;

var CL_N / style(data)={htmlstyle='vnd.ms-excel.numberformat:@'};
var CL_EFF_FRM_DT / style (data)={htmlstyle='mso-number-format:mm\/dd\/yy'};
var CL_NM CL_EFF_FRM_DT CL_EFF_TO_DT CL_CAN_DT CL_REIN_DT CHM_ENROLLMENT_TYPE CHM_ENROLLMENT_IDENTIFIER CLIENT_STATUS
ACTIVEC1 CANCELC1 ACTIVEM1 CANCELL1;
format CL_EFF_FRM_DT MMDDYY8.;
run;
ods _all_ close;
ods listing;

I got errors after the ODS statement - ERROR 180-322: Statement is not valid or it is used out of proper order.

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
  • 4 replies
  • 2505 views
  • 0 likes
  • 2 in conversation