Can you post the XML file, or at least a snippet - from Notepad, of the offending XML text (with all the tags, before and after for about 5 rows). I have not heard of this behavior before.
Hmm.
Could you please post the output of PROC CONTENTS for log_table? At least the list of columns?
Here you go. PROC CONTENTS for log_table
# | Variable | Type | Len | Format |
3 | Date | Num | 8 | DATETIME9. |
2 | Date_TimeStamp1 | Num | 8 | DATETIME21.2 |
7 | Details | Char | 200 | |
4 | Status | Char | 200 | |
1 | fname1 | Char | 200 | |
5 | processid1 | Char | 200 | |
6 | userid1 | Char | 200 |
Yeah, right. All the time we were discussing date values, when in fact it is a datetime value with a format that is simply too short. 9 characters is just too short for datetime.
Correction: not too short, mmddyy10. is just not suitable for datetime values.
KNOW YOUR DATA.
What format should I apply to get the correct output in .xml?
In this thread I've mentioned how my variable looks intially.
Thanks for your time so far.
Well, you would be best clarifying in your code. I would suggest you format your data date as datetime. i.e. not length 9 as that indicates just a date. Same with the format in the report. If you want the datetime output then you can try:
define Date/style(header)=[background=lightgreen] style(column)={tagattr="format:dd/mm/yyyy hh:mm"};
It might work but Excel doesn't have many built in formats. It would be easier to split out date and time into separate variables, and outputting to separate columns.
SAS produces those stars when a date or time value won't fit the format width or the format is unsuitable.
Change datetime9. to datetime19.
And if it is a datetime value in SAS and you want to output from sas just a date use format
f=DTMMDDYY. and not
f=MMDDYY.
Using a date format for a datetime value is a sure way to produce those stars!
Message was edited by: Peter Crawford I rush too much! If format DTMMDDYY hasnt been created yet rather than create your own version with proc format, try with format f=dtdate9. This one has been around since SAS8 (irrc)
Thanks for your tips Peter.
I tried with both the formats in my code in which only dtdate9. format is seems to be valid.
I modified datetime9. to dtdate9. , but still 'date' variable appears to be in string.
So if you want to write your DATETIME values and only include the date part you can use a format that supports that.
SAS(R) 9.4 Formats and Informats: Reference
Or you could first convert your values into DATE values and then you will have many more formats available.
I would recommend E8601DN as it will print YYYY-MM-DD and even Excel should be able to tell that is a date.
I formatted the date variable as E8601DN (instead of datetime9.) in datastep and then modified the following statement (I remove {tagattr}) in proc report before submitting the code.
define Date/style(header)=[background=lightgreen]display;
However, date variable is still appears as string in .xml. I think we need to apply some formats in the define statement to make life easier.
Hi:
When I use the code shown in the attached screen shot, in SAS 9.4, without any other use of TAGATTR or any style overrides, I do see that SAS has sent the correct format from SAS to Excel when the XML file is opened by Excel.
cynthia
To display a date or time in Excel using the ExcelXP tagset you need to have a column that is formated with the E8601DT. format. This will allow you to use the TAGATTR otion to specify how you want to display a date or time in Excel. The sample below shows different data types and how to format them (or some default formatting).
data have;
attrib
someText format=$32.
someDate format=ddmmyyp10.
someDateXLS format=E8601DT.
someTime format=time8.
someTimeXLS format=E8601DT.
someNumber format=comma12.2
someCurrency format=dollar14.2
somePct format=nlpct8.2
;
do i = 1 to 20;
someText = catx("_", i, put(i, roman.));
someDate = today() - 100 + i;
someDateXLS = dhms(someDate, 0, 0, 0);
someTime = time() - i;
someTimeXLS = dhms(someDate, 0, 0, someTime);
someNumber = (ranuni(i) * 5000) + 1000;
someCurrency = (ranuni(i*123) * 10000) + 1000;
somePct = ranuni(0);
output;
end;
drop i;
run;
ods tagsets.ExcelXP file="c:/temp/value_format.xml"
style=analysis
options(
embedded_titles='yes'
embedded_footnotes='yes'
)
;
title "SAS -> Excelxp";
proc report data=have;
define someDateXLS /
/* style(column) = {tagattr='TYPE:DateTime format:[$-F800]DDDD, MMMM DD, YYYY'}*/
style(column) = {tagattr='TYPE:DateTime format:dd.mm.yyyy'}
;
define sometimeXLS /
style(column) = {tagattr="TYPE:DateTime format:hh:mm:ss;@"}
;
define someNumber /
style(column) = {tagattr="Format:#,##0"}
;
run;
title;
ods tagsets.ExcelXP close;
I just noticed, that the code formatting "eats up" part of the text, see the screenshot below:
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.