מדהים.   אתה יודע מה היה מגניב אייל?   אם משתמש היה יכול בפורום לסמן פוסטים מועדפים (כמו זה) לחזרה מהירה 🙂   ... View more

that's what i referred as the undesired result. what i need is the actual file the link refers to. ... View more

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.       ... View more

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: Times are expressed in UTC (Coordinated Universal Time), with a special UTC designator ("Z"). Times are expressed in local time, together with a time zone offset in hours and minutes. A time zone offset of "+hh:mm" indicates that the date/time uses a local time zone which is "hh" hours and "mm" minutes ahead of UTC. A time zone offset of "-hh:mm" indicates that the date/time uses a local time zone which is "hh" hours and "mm" minutes behind UTC. " ... View more

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; ... View more

הי אייל, תודה ומה שלומך. שים לב שלא דיברתי על מאות שדות.. עם זה אין בעיה. הבעיה היא מה עושים כשהמדובר במליוני רשומות?   נראה לי פחות פרקטי לקחת כל רשומה.. לכתוב אותה לקובץ JSON חיצוני.. ואז לקרוא אותו בחזרה באמצעות json libname SQL Pass Through זה אכן פתרון.. כמו גם לבנות VIEW בבסיס הנתונים המקורי.. כאן יש רק בעיית בירוקרטיה מול ה- DBA 🙂   עם groovy עדיין לא יצא לי לעבוד. ראיתי שגם ב- DS2 יש אוסף נאה של פונקציות JSON אבל גם שם אני פחות שוחה 🙂   ... View more

hi tom,   this is relevant only for the function call and not to the json itself  $.id.name object in JSON are always quoted. the key (referenced by the last item in call function) is searched using a perl regex so the code is capable to handle numerous situations.   you can consider this as a simplified version of T-SQL Jason_Value function: https://docs.microsoft.com/en-us/sql/t-sql/functions/json-value-transact-sql?view=sql-server-ver15   my function does not yet support json arrays and i havent yet tested it for performance.  ... View more

bottom line.. try for yourself /* Works */ thisObj =cats('"',scan(j_value,_j,'.'),'"'); /* Works */ thisObj =quote(trim(scan(j_value,_j,'.'))); /* Doesnt Work */ thisObj =quote(scan(j_value,_j,'.')); ... View more

if a quote() is to be used then it needs to be with a trim: thisObj =quote(trim(scan(j_value,_j,'.'))); ... View more

yes, both works but yours is prettier 🙂 ... View more