Hello
Can you please share the SAS code used to generate the file?
If not does the file has xlsx extension or xls.?
basically this is the sas code i'm using:
it seems that the before / after 11AM issue i've mentioned earlier is indeed relevant.
ods excel file="\\some_location\some_file.xlsx" options(sheet_name='report1');
title;footnote;
proc print data=sashelp.class;run;
ods excel options(sheet_name='report2');
proc print data=sashelp.classfit;run;
ods excel close;
Hello
I did find the following difference on dates before and after saving. (I used Libre Office). The file could be imported into power bi after saving
Original
<dcterms:created xsi:type="dcterms:W3CDTF">2021-06-27T 7:37:29+00:00</dcterms:created>
After saving
<dcterms:modified xsi:type="dcterms:W3CDTF">2021-06-27T07:53:21Z</dcterms:modified>
I suggest that your team take up the case with SAS and Microsoft power bi team to resolve the issue.
As a postfix to my answer above, if the file can be opened in excel, it should be possible to open in power bi.
The issue to me looks more on power bi side and should be addressed by that team.
hi,
thank u very much for your assistance
it seems like u hit the spot and the "problem" is the TimeZone offset..
although the W3CDTF (iso 8601) allows 2 "legal" options, it seems like SAS adapted one approach while Microsoft adapted the other.
yet, remains the question as for the difference SAS register the time zone offset in different hours.
it should not matter at what time the file was generated..
for that reason, i believe it's more like a SAS issue.
https://www.w3.org/TR/NOTE-datetime
"
This profile defines two ways of handling time zone offsets:
"
Hi @Assaf_Attas
I searched and it looks like this is a known issue for SAS Technical Support so perhaps the best approach now is to open a support track about it and to hear what they say about it.
HTH,
Eyal
i've investigated further and this is definitely a SAS issue.
if generated before 10AM (a single digit HH):
<dcterms:created xsi:type="dcterms:W3CDTF">2021-06-28T 7:16:33+03:00</dcterms:created> - Not openable by Excel Power BI
if generated after 10AM (a two digit HH):
<dcterms:created xsi:type="dcterms:W3CDTF">2021-06-28T10:04:09+03:00</dcterms:created> - Openable by Excel Power BI
if i manually add a leading zero to HH in core.xml : 2021-06-28T 7:16:33+03:00 to 2021-06-28T07:16:33+03:00 - Openable by Excel Power BI
so the problem is that SAS doesn't comply with W3CDTF standard for hours 00:00AM till 09:59AM
@EyalGonen , please open a track for this.
Hi @Assaf_Attas
I cannot open the track on your behalf. Please email support@sas.com to open a track and send in all the relevant info to replicate the issue including the SAS version and code + any additional findings you have found.
Eyal
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!