Hi @MarkDawson    Your suggestion works but what you copied in the post above is incomplete. You missed the "Catalog=" piece in the Properties statement, i.e.,  caslib spkcaslib dataSource=( srctype='spark', platform='databricks', authenticationType='OAUTH2', port=443, httpPath="&hpath.", schema="&schm.", /*Before was: schema="&ctl..&schm." */ server="&srv.", PROPERTIES="Catalog=&ctl." /* Added this statement*/ ); Could you please edit your solution so I can mark it as accepted?   Thank you ... View more

You can apply a format to table in memory using ALTERTABLE - here I'm loading an Oracle table into memory, and then altering the format for one column proc casutil ; load incaslib="oracle" casdata="CARS" outcaslib="CASUSER" casout="CARS" replace ; altertable incaslib="casuser" casdata="CARS" columns = { {name= "Cylinders" format="roman." } } ; quit; The solution provide previously, works well when loading a table from a Library (DATE=...), but will not work when loading from a CAS Library (INCASELIB=...), such as the Oracle example above. Using ALTERTABLE should work for any in memory table.       ... View more

Hi Jeroen, Your post was a few months ago, and I only just came across it.  SAS did add some support for this, see 68840 - Database connection information for a source table is used when creating a new database table with the Create Table transformation (sas.com) for a Hot Fix that might help you.  However, if that does not meet your use case, please contact your local SAS Technical Support office, maybe with a package file that shows the issue. SAS can then review and determine if we can also address your specific use case. Thanks, Mark ... View more

If your data really looks like that ("dates" as strings, wide layout), you suffer from useless data in a useless structure. See this short example for intelligent data making the code simple: data table1; input id monthcount mnth :yymmn6.; format date yymmd7.; datalines; 1 1 202004 1 2 202005 1 3 202006 1 4 202007 1 5 202008 1 6 202009 ; data m1; input id mnth :yymmn6. volume; format mnth yymmd7.; datalines; 1 202004 12 ; data m3; input id mnth :yymmn6. volume; format mnth yymmd7.; datalines; 1 202005 15 ; data all / view=all; set m:; run; data want; merge table1 all ; by id mnth; run; proc report data=want; column id mnth,volume; define id / group; define mnth / "Volume" across; define volume / "" analysis sum; run; To show you how to make intelligent data out of what you have, supply it in DATA STEPS WITH DATALINES, as I'm not into the guessing game. ... View more

<rant> Do NOT use the "any" informats. They will try their "best" to make dates or times out of all kinds of crap that has no right to exist in the first place, with (at best) hilarious results. </rant> Having said that, you get very nice timestamps in ISO 8601 format, so you positively should use the E8601DT informat. Since SAS won't process dates before 1582 (the introduction of the Gregorian calendar), you only need to create a mechanism for date(time)s before that. With DB/2, 0001-01-01 is the default value for date columns that can't be missing. When we import such dates into SAS, we set them to missing. Other dates might have special meanings as defined in your organization. We have special timestamps in the years 900-999 that signal data that was migrated from an old system; for SAS, we replace the "09" at the beginning with "17", as we can be sure none of our customers is that old, or will have a claim from back then 😉   So you best read your values as character first, make the necessary adjustments, and then convert to a SAS datetime with the INPUT function and the E8601DT informat, or set a missing value explicitly. ... View more

You want to keep all records in which the auxiliary variables are not missing (there is one such record per episode).  But you also want each distinct episode identified:   data want; set have; by id ; if first.id then episode=0; if flag1=1 then episode+1; if var1^=' '; run;   ... View more

Hi,   I'm using sas studio 3.5 in windows.   I have tried the "Libname mylib clear" and it was successfull.   Opened a new sas session and I can't see that library that has been deleted.   Now created library with the same but I haven't got any warnings/error?   Just curious about what could be the implications if we delete library using clear command and recreate those libraries again?   Best krisraa ... View more

I think it's because of this %let DIR_IN_BASE=I:\SGEE\SGEE\Jorge\Proyecto\ENTRADA; change it to this %let DIR_IN_BASE=I:\SGEE\SGEE\Jorge\Proyecto\ENTRADA\;   ... View more