SAS Support Communities

Hi All,   I need to know is there any way I can optimize this join. I am getting notes : NOTE: The execution of this query involves performing one or more Cartesian product joins that can not be optimized. mywork.dates   proc sql noprint;    create table mywork.dates as     select         distinct         a.prod_sk,         a.geo_sk,         b.date_sk,         b.fiscal_start_dt,         b.fiscal_end_dt     from         mywork.tmp_plan_actuals_pgr a,         mylib.date_nm b             where b.fiscal_start_dt >= &min_start_dt.                and b.fiscal_end_dt <= &max_end_dt.             and date_lvl_no = &time_low_lvl.;     quit;   &min_start_dt. , &max_end_dt. ,  &time_low_lvl these have some values . these are getting created in previous steps and totally independent from the tables present in join.   Please help.         ... View more

Hi ,   I have a query. Currently I am facing  ERROR: Insufficient space in file and time issue. I am working on optimization of a SAS code. FOr the same matter I need one suggestion    In the existing code I can see multiple external db connection  to create some tables in database as  for e.g     step 1 .  proc sql noprint;       connect to db (<credential>);            execute (                       create table abc                    ) by db.;         disconnect from db;       ;    quit;   step 2 .  proc sql noprint;       connect to db (<credential>);            execute (                       create table xyz                    ) by db.;         disconnect from db;       ;    quit; like that I have multiple steps, I can make only one connection and create both xyz and abc table. will this help in any sort of optimization. any explanation will be very helpful. Thanks!!!!!! ... View more

Hello, I want to learn about hashing and parallel processing in SAS. Somehow I am not getting any proper documentation where I can get all information  and some practice question. can someone please help.   Thanks a ton! ... View more

Hi ,   I need suggestion for the error I am getting while creating table.   Since I have libray refernce having more than  8 char. So I created a libref    like this  libname CAS_LIB  cas caslib="&mp_sourceName";   but whenever I am trying to create table using CAS_LIB as library . It is throwing error.   I have also inserted code for ref proc casutil; load data =planning.ed_&inputTable CASOUT="ed_&inputTable" OUTCASLIB="&mp_sourceName" replace; libname &vf_lib cas caslib="&mp_sourceName"; proc fedsql SESSREF=&_SESSREF_; create table &vf_lib..tmp_&inputTable. as select a.* ,b.time_id as start_dt_sk from &vf_lib..&inputTable. a, &vf_lib..ed_&inputTable b where a.%bquote(&time_dim.) between b.start_date and b.end_date; quit; proc cas; sessionProp.setsessopt / caslib="&mp_sourceName"; table.alterTable / columns={{rename="%TRIM(&prd_var)", name="actual"}, {rename="%sysfunc(catx(_,&prd_var.,fcst))", name="predict"}} name="tmp_&inputTable."; quit; /* This mp_source name have values like CASUSER(CMSTEST), ShoppingInsight , ModelValuation , etc */ ernce. normal: MPRINT(X): proc casutil; note: NOTE: The UUID '3887c308-3320-8d4d-81f1-b24a9322bcac' is connected using session CASAUTO. normal: SYMBOLGEN: Macro variable PLAN_LIB resolves to planning normal: SYMBOLGEN: Macro variable INPUTTABLE resolves to OUTFOR_102 normal: SYMBOLGEN: Macro variable INPUTTABLE resolves to OUTFOR_102 normal: SYMBOLGEN: && resolves to &. normal: SYMBOLGEN: Macro variable MP_SOURCENAME resolves to CASUSER(cmstest) normal: MPRINT(X): load data =planning.ed_OUTFOR_102 CASOUT="ed_OUTFOR_102" OUTCASLIB="CASUSER(cmstest)" replace; note: NOTE: PLANNING.ED_OUTFOR_102 was successfully added to the "CASUSER(cmstest)" caslib as "ED_OUTFOR_102". normal: SYMBOLGEN: Macro variable VF_LIB resolves to CAS_LIB normal: SYMBOLGEN: Macro variable MP_SOURCENAME resolves to CASUSER(cmstest) normal: MPRINT(NPF_INPUT_CDL_FACT): libname CAS_LIB cas caslib="CASUSER(cmstest)"; note: NOTE: Libref CAS_LIB was successfully assigned as follows: note: Engine: CAS note: Physical Name: 3887c308-3320-8d4d-81f1-b24a9322bcac note: NOTE: PROCEDURE CASUTIL used (Total process time): note: real time 0.28 seconds note: cpu time 0.05 seconds note: note: normal: SYMBOLGEN: Macro variable _SESSREF_ resolves to CASAUTO normal: MPRINT(X): proc fedsql SESSREF=CASAUTO; normal: SYMBOLGEN: Macro variable MP_SOURCENAME resolves to CASUSER(cmstest) normal: SYMBOLGEN: Macro variable INPUTTABLE resolves to OUTFOR_102 title: 17 The SAS System Wednesday, February 22, 2023 05:25:00 PM title: normal: SYMBOLGEN: Macro variable MP_SOURCENAME resolves to CASUSER(cmstest) normal: SYMBOLGEN: Macro variable INPUTTABLE resolves to OUTFOR_102 normal: SYMBOLGEN: Macro variable MP_SOURCENAME resolves to CASUSER(cmstest) normal: SYMBOLGEN: Macro variable INPUTTABLE resolves to OUTFOR_102 normal: SYMBOLGEN: Macro variable TIME_DIM resolves to start_date normal: MPRINT(X): create table CASUSER(cmstest).tmp_OUTFOR_102 as select a.* ,b.time_id as start_dt_sk from normal: CASUSER(cmstest).OUTFOR_102 a, CASUSER(cpstest).ed_OUTFOR_102 b where a.start_date between b.start_date and b.end_date; error: ERROR: Syntax error at or near ".T"   ... View more

Hi All,   I want to know if we can use CAST like functionality in PROC SQL.   I have query where the internal query returns value which is in varchar format and outer query is having UUID datatype . So whenever I am trying to compare values it is throwing errors .   Thanks!     ... View more