CULPRIT FOUND!   Thank you all for the input.  I've used some of the proposed ideas and will try the rest too.    SAS MISSING() in the WHERE statement was causing the failure to pass the querry to DBMS for fetching data!  As soon as I replaced the commented out code that contains "missing()" with equivalent "is missing" syntax, no more memory hogging on the WORK folder and execution in seconds rather than >30 minutes.   where SurgDt between '01Oct2022'd and '30Sep2023'd and Age >= 18 and CountryUS = 'Y' /*** and missing(ExpiredInOR) */ /*** and not( (missing(OpValve) or OpValve = '2') */ /*** and (missing(OpCAB) or OpCAB = '2') ) */ and ExpiredInOR is missing and not( (OpValve is missing or OpValve = '2') and (OpCAB is missing or OpCAB = '2') ) Any input on MISSING() function?  Weird, isn't it?    As to how I knew that SAS was importing the data: the original querry would (1) take way too long to retrieve <50 rows of data and (2) the memory on the c:\ drive would disappear like rain in the desert while the query was executing.  I have well over 0.5TB on the main drive and it would all get gobbled up by  SAS.  No way a dataset with <50 rows and <200 columns consumes >700GB of memory. ... View more

Straight up SAS date ‘ddmmmyyyy’d, and two numbers — an age and a category number for a status. ... View more

Updated Query -- hope it's more clear this way: proc sql; create table want as select a.<VarList>, b.<VarList>, c.<VarList> from MySQL.MSSQL_A as a inner join MySQL.MSSQL_B as b ON a.IDVarA=b.IDVarB inner join MySQL.MSSQL_C as c ON a.IDVarA=c.IDVarC where a.SurgDate > x AND b.Age > y AND c.Status = z ; quit; ... View more

Fair.  I used shortcuts that I thought were obvious.    libname MySQL ODBC dsn=MySQL schema=DBO; MySQL is defined as an ODBC Data Source 64-bit via ODBC Driver 17 for SQL Server.   Each table in the FROM clause is MySQL.TableA, MySQL.TableB and MySQL.TableC.   I don't think I am doing a cartesian join as column ID is a unique row identifier and each table contains exactly the same number of rows for each patient/ID in the database.  The WHERE clause is meant to highlight the fact that Surgery Date is one table, Patient Age is in anotehr, and Patient Status is the third table.  I want only a small SurgDate range, Patients of a certain Age, and Status.  Hence the three WHERE conditions.  I think using the same name Variable for all three is misleading -- they are all three different variables in each table.  The WHERE clause is not meant to eliminate cartesian joint like it is often used. I agree with you, this code is relatively simple and should be executed on the server by default.  But it is not!  WHY? ... View more

Closing parenthesis got included in the link. Remove it and the link works. Just checked. <> [12874.jpg] A note on obtaining correct marginal predictions from a random intercepts model for binary outcomes - BMC Medical Research Methodology<> bmcmedresmethodol.biomedcentral.com<> ... View more

Thanks.  Even better: I bought YOUR book 🙂 ... View more

You are too kind.  Much obliged!  Thanks!    It's a challenge to transition from decades of just columns, rows and cells to vectors and arrays. ... View more

And I am a total IML idiot.  Why is this code not working? proc iml; use SAShelp.class; read all var {Age}; PP = Age/100; Eta = log(PP / (1-PP)); start Integrand(u) global(eta); L = logistic(u + eta); fu = pdf("Normal", u, 0, 0.2); return( L * fu ); finish; call quad(iPP, "Integrand", {.M,.P}) scale=.0001; print iPP; ... View more

Oh, and one more thing, I think the notation in the formula is what's throwing you off.  "RE" subscripts for alpha and beta signify that it's a Random Effects model rather than that alpha and beta are random effects coefficients.  Alpha and Beta are fixed.  U is the random intercept.   ... View more

Thanks so much, Rick.   Extra background detail.  There is a problem with marginal observation-level Predicted Probabilities from logistic regression when random effects are involved.  Straight up linear predictor expressed as probability is systematically different from the 'correct' value.  See this for more detail (https://bmcmedresmethodol.biomedcentral.com/articles/10.1186/s12874-015-0046-6).   The correct way to calculated marginal probabilities according to the reference and the formula I pasted, is to integrate Predicted Probability for each row over the distribution of all random intervals in the model.  I wish SAS had this correction built in into all PROCs that do multi-level regression.  I requested that feature.  In the absence of it, my starting point is a marginal NOBLUP Predicted Probability value for each row in the model from GLIMMIX, for example.  From there, I back-transform it to linear predictor so that the LP could be integrated over the distribution of random effects or Random Intervals (the u term).  For each row in the model (patients), the Predicted Probability needs to be corrected using the distribution of random intercepts (hospitals). Normal integral QUAD() gives me AUC for the transformation without any density weights.  What you shared seems to do the trick.  I tested a couple of values and it seems to correctly increase the input for values below 0.5 and decreases them for values above .5.  I modified the code slightly and now just need to figure out how to apply it to an entire column of Predicted Probability values.  I think I can simplify this a little bit by outputing Linear Predictors from the model rather than Predicted Probabilities -- skip the `iLink` option. proc IML; PredProb = .73; /* Marginal Predicted Probability from RE GLMM */ Eta = log(PP / (1-PP)); /* Eta = linear predictor */ start Integrand(u) global(eta); L = logistic(u + eta); fu = pdf("Normal", u, 0, 0.2); /* specify f(u) here. eg, PDF for N(0,0.20) */ return( L * fu ); finish; call quad(iPP, "Integrand", {.M,.P}); print PP Eta iPP; quit; Thanks again.  Happy to discuss further.  Hope you can put in a good word with SAS development teams to add this correction to the SAS/STAT core PROCS.   Would love to hear if you disagree with the diagnosis for marginal PPs. ... View more

Thank you Steve. I was not looking to deliberate between PL and Quadrature approaches but this is certainly a relevant topic. Thanks for sharing! ... View more

Perfect! Thanks. Found the same solution. Any idea why one needs to request partitioning to get these basic model fit statistics to output? Does not make sense to me that they won't show up without typing extra code for the PARTITION option and specifying this contrived (TEST=0) scenario to, basically, negate the PARTITION request. ... View more