Hello,   I am trying to write the below query which taking longer time around 11 hours & 30 mins to complete. Is their any other way where it can run the query faster way. Any help / suggestion will be really helpful for me. Thanks.   proc sql noprint; select max(datepart(tran_datetime)) format date9. into :end_ugt_hist  from DBA.sas_max_site_datetimes  where site = "SKY" and  "15SEP2020"d <= datepart(tran_datetime) <= today(); quit;   NOTE: PROCEDURE SQL used (Total process time):       real time           11:33:01.77       cpu time            40.52 seconds   Thanks, Amit ... View more

I've become interested in the PROC DS2 package functionality and have been trying to learn as much as I can.    I'm reading this paper right now for reference: https://support.sas.com/resources/papers/proceedings16/7280-2016.pdf    In the paper, the author talks about how SAS automatically includes getter and setter methods in packages that can be called by adding the prefix "get_" or "set_". My question, is there any documentation on the methods that are automatically included in packages?   Additionally, in the paper (bottom of page 6, top of page 7) they create a teacher package which "inherits" from the person package. At one point they reference "get_pname" to get the name from the person package and I was wondering if "get_p" is a predefined prefix? or is there something I'm missing?   Thanks! ... View more

I am working on a linear mixed model to compare results in SAS proc glimmix (or equivalently proc mixed) and CRAN R lme4 package. My challenge is: How do I manipulate the SAS code to get similar (approximately equal) F-ratios for the analysis of variance in SAS and R?   I tried to control for convergence in SAS (see code), but I need help to finetune that SAS code to get the degrees of freedom and/or F-ratio that match those i get in R.    Data extract (1st 5 observations): Here is my SAS code: proc glimmix data=BarleyGroups outdesign=XZ ; class factorA factorB groupA groupB switchA switchB ; model yield= groupA|groupB / ddfm=kr; random groupA*factorA*switchA; random groupA*groupB*factorB*switchB; random groupB*factorB*switchB; random groupB*groupA*factorA*switchA; random groupA*factorA*switchA*groupB*factorB*switchB; parms (0.0000) (0.0000) (0.0000) (0.0000) (0.07074) (0.41458); nloptions maxiter=1000 maxfunc=10000 xconv=1e-5 gconv=1e-5 technique=nmsimp; lsmeans groupA|groupB / lines ; Title "Linear Mixed Model with Convergence"; run; Here is my SAS output from the code above: ********************************************************************************************************************** Here is my R code:    # Load necessary libraries library(lme4) library(emmeans) library(lmerTest) # Provides Type III tests for fixed effects   control_settings <- lmerControl( optimizer = "Nelder_Mead", # Use Nelder-Mead optimizer optCtrl = list(maxfun = 10000), # Set maximum number of function evaluations check.conv.grad = .makeCC(action = "warning", tol = 1e-5), # Gradient convergence check check.conv.singular = .makeCC(action = "warning", tol = 1e-5) # Singular fit check ) Full.mod<-lmer(Yield~groupA+ groupB+groupA:groupB+ (1 | groupA:factorA:switchA) + (1 | groupB:factorB:switchB) + (1 | groupA:groupB:factorB:switchB) + (1 | groupB:groupA:factorA:switchA)+ (1|groupA:factorA:switchA:groupB:factorB:switchB), data = BarleyGroups, control = control_settings) summary(Full.mod) anova(Full.mod, type=3, ddf = "Kenward-Roger")   Here is the R output:       > anova(Full.mod, type=3, ddf = "Kenward-Roger") Type III Analysis of Variance Table with Kenward-Roger's method Sum Sq Mean Sq NumDF DenDF F value Pr(>F) groupA 0.08694 0.04347 2 4.5808 0.1040 0.9033 groupB 1.51768 0.50589 3 5.5396 1.2056 0.3905 groupA:groupB 1.38705 0.23118 6 7.9107 0.5506 0.7585 **************************************************************************************************************************************  ... View more

Hi,   i use Data integration Studio 4.905.    I want to use the transpose transformation to convert value from a column (nationality_id), to new columns.   Basically the number of "nationality_id" by group (=user_id) can vary from 1 to 5, so 5 new nationality_id_x columns will be created for each user_id.   But i want to create some "spare" columns in case of new set of data bring a user_id with more than 5 differents nationnality_id.    So in the mapping window of the transpose transformation, i've created for the target table, 2 new metadata columns named nationality_id_6 and nationality_id_7. I've put the option  "Update the table metadata for the target tables" to No, acccording the documentation that i fnd there (bullet in the Overview section) https://documentation.sas.com/doc/en/etlug/4.904/p1lj6ps6h3xv7yn1qfr17lifevwb.htm   When i try to use the (work)output table in a following "extract" transformation, i've got this error:    ERROR: The following columns were not found in the contributing tables: Nationality_id_6, Nationality_id_7.   How i can deal with that?  Thank you very much. ... View more