Thanks @quickbluefish and it's certainly something I'll keep in mind for later and less restrictive environments. The one I'm working in is on 9.4 with noxcmd so can't really call python directly out of SAS even if all the necessary packages would be installed.  ... View more

@RichardAD  Thank you and yes, this is what I was hoping for. ... View more

@quickbluefish Well... Do you actually know a Python package that does such formatting? All I found so far are packages that require to run pip install ... View more

@quickbluefish  Thanks for your answer. And yes, I’m in the end creating an external .sql file per query. What you’re doing is in essence similar to my current attempt. I’m using a RegEx with “all” the SQL keywords and using prxchange() simply add an LF before any keyword I find (changing the script directly within the one single var/row). After that I create a .sql file per row. The SQL’s I’m dealing with can be rather complex and though the result using this simple approach can still be hard to read (like all the elements under a Select statement that can include rather lengthy expressions). I guess what I’m asking for is if someone already spent the time writing some SQL formatter or knows about “published” existing code and is willing to share. I’m working in a restrictive environment and won’t be allowed to install things like Python packages. ... View more

You certainly want to avoid sorting your big dataset and though using a hash table lookup feels like a good option.  Given the length of your key variable is 64 I assume that's already a digest hex value created using sha-256.  You can't fit all the keys of your header table into memory and though like @FreelanceReinh I've been thinking how to reduce the size of your key values so they can fit. Converting your key values to an md5 binary string should reduce memory requirements to what's available to you. BUT using md5 instead of sha-256 will increase the collision risk which with your data volume isn't negligeable. IF the still small risk is acceptable that you select a key that's not in your list then using md5 like in below sample code should be an option. I also would use the SPDE engine for storing such a huge SAS table.  data spde_saslibrary.want(compress=yes); if _n_=1 then do; length _key $16; dcl hash h1(); h1.defineKey('_key'); h1.defineDone(); do until(_done); set saslibrary.headerinputdataset end=_done; _key=md5(clm_id); _rc=h1.ref(); end; end; set saslibrary.lineinputdataset; _key=md5(clm_id); if h1.check()=0 then output; drop _:; run; ...and I believe to remember that at one point there was an issue with the hash that when one didn't define Data the key variables got used as Data doubling the required memory. Should I remember right and if that's still an issue with your SAS version then eventually load a placeholder Data variable for this not to happen. data spde_saslibrary.want(compress=yes); if _n_=1 then do; length _key $16; retain _placeholder ' '; dcl hash h1(); h1.defineKey('_key'); h1.defineData('_placeholder'); h1.defineDone(); do until(_done); set saslibrary.headerinputdataset end=_done; _key=md5(clm_id); _rc=h1.ref(); end; end; set saslibrary.lineinputdataset; _key=md5(clm_id); if h1.check()=0 then output; drop _:; run; Now... If your key variable clm_id contains a 64 character hex string then that's a base16 value. Another way for shortening the string without increasing the collision risk could be to convert this base16 value to a base32 value.  I'm not sure how much processing time such a conversion would add but it's certainly worth giving it a shot - if you can make it work. The approaches I've seen allways first convert the values to base10 and need to do summations. Problem with SAS is that a sha-256 doesn't fit as a full precision integer into a SAS numerical variable. One could do it using something like Python which supports such large integers or then find another approach which doesn't require an intermediary numerical variable.       ... View more

You could just run Proc Setinit. The result in the log will tell you if you run under a SAS9.4 or Viya workspace server. proc setinit; run;   ... View more

From what I know SAS Studio was initially the only client for Viya - but that has changed not the least due to customer demand. I believe the earliest versions for an EG to Viya connection is EG8.2 with Viya 3.5 . @SASKiwi provided the links for these versions in this discussion.   There have been a lot of improvements since Viya 3.4 and I'd really recommend to upgrade. This would also make the EG to Viya connection easier. Using SAS Enterprise Guide with SAS Viya 4   ... View more

@Ksharp already demonstrated that the syntax as such is o.k. so it must be an issue with the code you're including.  To get more info into the log for debugging, execute your macro with the following options: option mprint mlogic symbolgen source2; So something like: option mprint mlogic symbolgen source2; %macro choose_progs(which_progs); %if &which_progs = set1 %then %do; %include "mypath\folder1\program1.sas"; %include "mypath\folder1\program2.sas"; %end; %else %if &which_progs = set2 or &which_progs = set3 %then %do; %include "mypath\folder2\program1.sas"; %include "mypath\folder2\program2.sas"; %end; %mend choose_progs; %choose_progs(set1);   What happens if you run the selected %include statements outside of the macro? %include "mypath\folder1\program1.sas" /source2; %include "mypath\folder1\program2.sas" /source2; ... View more

My 5 cents: Both Tableau and SAS VA are great for interactive reports/dash boards. Both products allow for export of such reports to pdf and both products have interfaces that allow for automation (Tableau: CLI, SAS VA: REST API, SAS VA SDK).  Not sure about Tableau but with SAS you could of course also use SAS/Graph to create such reports. Robert Allison's SAS Graphics Examples   To select what's right for you, I'd also consider migration effort (upskilling included), cost and risk as well as any company policies (like: is there a defined enterprise tool to be used for external reporting, what products are easily available to you/already licensed and installed).    Assuming you're working for a bigger organisation, I'd also try to figure out what other departments use for external reporting and what their experiences are.    ...and of course there are also other tools like R and Python that allow for pretty pdf reports. ... View more

@Werner_69  Same as @JackieJ_SAS : No issues printing multiple pages from SAS ODA using the printer icon. I'm using Chrome (Version 134.0.6998.89 (Official Build) (64-bit)) on a laptop with Windows 11 Version 24H2 If you need further proof that it's an issue with your environment and not SAS ODA: Attach one of your programs that you can't print as-is and I'll test how this prints on my end.       ... View more

You can exchange variables with strings up-to 32767 characters between SAS and a DB. You need to set connection option dbmaxtext=32767 because the default is normally 4096 characters. I can't see in your code where you would use such a variable.   Also not sure how you would use a macro variable. Is your idea to construct some where condition on the SAS side and store it in a macro variable that you then use in your SQL query? I guess that would work as long as the string fits into the SAS macro variable (max 32767 or 65534 characters depending on environment).    From how it looks like loading your (still small) SAS table first into a DB table (could be a temporary table) for a join is likely the appropriate approach. ... View more

I'm wondering why your log says the timeout value is in minutes!? If it were in seconds as per docu TIMEOUT= Session Option then you just walking away at the end of the day shouldn't cause such issues. Timeout: 1800 minutes May-be worth looking a bit closer into the cause for this being minutes together with your SAS Admin and/or SAS Tech Support. ... View more

ECM (Enterprise Case Management) is somewhat solution specific specialty software. If you don't get an answer here then I suggest you also contact SAS Tech Support directly. ... View more

what is the way to see column names and their type(character or numeric) when using Teradata passthrough. Do you want to see the Teradata data types or to what they map on the SAS side? If it's just on the SAS side (character or numeric) then use a libname and then Proc Contents. libname mytera teradata <connection info>. proc contents data=mytera.<table name>; run; If you want the actual Teradata data types then using SAS it must be via explicit SQL passthrough using Teradata specific syntax - for which you would need to ask in a Teradata forum. ... View more