The responsibility of every parent: Shaping the future of our children. ... View more

Hi SAS Community, I hope you're doing well. I'm currently working on a dataset in SAS and would like to arrange the columns in a specific order based on my personal preference, rather than the default or alphabetical order. Specifically, I'm using SAS Visual Analytics and working with a crosstab table. I'd like to control the order in which the columns appear in the crosstab, regardless of how they are sorted in the dataset or metadata. Is there a way to manually set or customize the column order within a crosstab in SAS Visual Analytics? Any guidance, steps, or best practices would be greatly appreciated.    Thank you in advance for your support! @Joy_SAS  ... View more

Hello I am running SAS program-I get a message -Large results were detected I dont want to print any report on screen because the report is export into XLSX file and sent automaticaly by email Note- I am using ods excel  to crate multiple sheets XLSX file Here is the code I runMy question- What changes should I do in code to prevent this message???   %include '!RSMEHOME/SASCode/SHARECode/Libname.sas'; %let datetime = %sysfunc(datetime(), datetime20.) ; %let datetime_clean = %sysfunc(translate(&datetime,_, %str(: ))) ; %let path="/usr/local/SAS/SASUsers/LabRet/UserDir/udclk79/R&datetime_clean..xlsx"; proc sql noprint; select max(mon) as last_mon_in_Report_YYMM1 into :last_mon_in_Report_YYMM1 from R_R.P_CrdRate_Bakara_S1 ; quit; %put last_mon_in_Report_YYMM1=&last_mon_in_Report_YYMM1; proc sql noprint; select max(mon) as last_mon_in_Report_YYMM2 into :last_mon_in_Report_YYMM2 from R_R.CrdRate_Gini_S9 ; quit; %put last_mon_in_Report_YYMM2=&last_mon_in_Report_YYMM2; data _null_; ddate=input("&last_mon_in_Report_YYMM1.",yymmn4.); prev=intnx('month',ddate,-1); prev_YYMM=input(put(prev,yymmn4.),best.); format ddate prev ddmmyy10.; call symputx('prev_YYMM',prev_YYMM); Run; %put prev_YYMM=&prev_YYMM; /**** S1--No pepper***/ /**** S1--No pepper***/ /**** S1--No pepper***/ proc sort data=R_R.NP_CrdRate_Bakara_S1 out=a_NP_S1; by mon; Run; data b_NP_S1; retain NP_IND_Prob mon; set a_NP_S1; by mon; NP_DIF_Tamhil_NrY_POS=NP_Tamhil_NrY_POS-LAG(NP_Tamhil_NrY_POS); NP_DIF_Tamhil_NrY_Null=NP_Tamhil_NrY_Null-LAG(NP_Tamhil_NrY_Null); NP_DIF_Tamhil_NrY_0=NP_Tamhil_NrY_0-LAG(NP_Tamhil_NrY_0); NP_DIF_Tamhil_NrY_NoPreClean=NP_Tamhil_NrY_NoPreClean-LAG(NP_Tamhil_NrY_NoPreClean); NP_DIF_Tamhil_NrY_PreClean_NoCrd=NP_Tamhil_NrY_PreClean_NoCrd-LAG(NP_Tamhil_NrY_PreClean_NoCrd); format NP_DIF_Tamhil_NrY_POS NP_DIF_Tamhil_NrY_Null NP_DIF_Tamhil_NrY_0 NP_DIF_Tamhil_NrY_NoPreClean NP_DIF_Tamhil_NrY_PreClean_NoCrd percent10.2; IF abs(NP_DIF_Tamhil_NrY_POS)>0.03 OR abs(NP_DIF_Tamhil_NrY_Null)>0.03 OR abs(NP_DIF_Tamhil_NrY_0)>0.03 OR abs(NP_DIF_Tamhil_NrY_NoPreClean)>0.03 OR abs(NP_DIF_Tamhil_NrY_PreClean_NoCrd)>0.03 then NP_IND_Prob=1; else NP_IND_Prob=0;Run; proc sort data=b_NP_S1; by descending mon; Run; proc sql noprint; select NP_IND_Prob as Ind_NP_S1_lastMon into :Ind_NP_S1_lastMon trimmed from b_NP_S1 where mon=&last_mon_in_Report_YYMM1. ; quit; %put Ind_NP_S1_lastMon=&Ind_NP_S1_lastMon.; proc sql noprint; select case when &Ind_NP_S1_lastMon.=1 then 'Red' when max(NP_IND_Prob)=1 then 'Orange' else 'Green' end as Ind_Color_Sheet_NP_S1 into : Ind_Color_Sheet_NP_S1 from b_NP_S1 ; quit; %put Ind_Color_Sheet_NP_S1=&Ind_Color_Sheet_NP_S1.; /*Bakara_Crd_Rate*/ /*ODS _ALL_ CLOSE; */ /**to prevent print on screen***/ ods excel file=&path. options (sheet_name='NP_S1' embedded_titles='yes' embedded_footnotes='yes' sheet_interval="proc" /***One table in sheet**/ gridlines = 'yes' tab_color="&Ind_Color_Sheet_NP_S1." ); title; title1 bold color=red height=5 font=simplex 'בקרת תמהיל ציון כא ריק-אפס או חיובי'; title2 bold color=red height=5 font=simplex 'קריטריון להדלקה- שינוי לפחות 3% בתמהיל יישויות בעלות ציון כא חיובי'; title3 bold color=red height=4 font=simplex 'אוכלוסייה-יישויות_שאינן_פפר-אינן_חטיבה_860_ואינן_סניף_998'; title4 bold color=black height=4 font=simplex 'הערה-ציון כא יכול ליהיות עם ערך ריק-0 מ-2 סיבות אפשריות'; title5 bold color=black height=4 font=simplex 'סיבה1-אוכלוסיה לא מדורגת '; title6 bold color=black height=4 font=simplex 'סיבה2-אוכלוסיה מדורגת ללא פעילות בכא וללא קיום כא בשנה אחרונה'; proc report data=b_NP_S1 missing nowd split='*'; column NP_IND_Prob mon ("מספר_יישויות" NP_Nr NP_NrY_POS NP_NrY_Null NP_NrY_0 NP_NrY_NoPreClean NP_NrY_PreClean_NoCrd) ("תמהיל_יישויות" NP_Tamhil_NrY_POS NP_Tamhil_NrY_Null NP_Tamhil_NrY_0 NP_Tamhil_NrY_NoPreClean NP_Tamhil_NrY_PreClean_NoCrd) ("הפרש_בתמהיל_מחודש_קודם" NP_DIF_Tamhil_NrY_POS NP_DIF_Tamhil_NrY_Null NP_DIF_Tamhil_NrY_0 NP_DIF_Tamhil_NrY_NoPreClean NP_DIF_Tamhil_NrY_PreClean_NoCrd) ; define NP_IND_Prob / 'אינד להדלקה' DISPLAY; define mon / 'חודש' DISPLAY; define NP_Nr / 'מספר יישויות' DISPLAY format=comma32.; define NP_NrY_POS / ' ציון כא חיובי' DISPLAY format=comma32.; define NP_NrY_Null / ' ציון כא ריק' DISPLAY format=comma32.; define NP_NrY_0 / ' ציון כא 0' DISPLAY format=comma32.; define NP_NrY_NoPreClean / 'ציון כא ריק* מסיבת*לא מדורגת' DISPLAY format=comma32.; define NP_NrY_PreClean_NoCrd / 'ציון כא ריק* מסיבת *יישות מדורגת*ללא פעילות בכא' DISPLAY format=comma32.; define NP_Tamhil_NrY_POS / 'ציון כא חיובי' DISPLAY; define NP_Tamhil_NrY_Null / 'ציון כא ריק' DISPLAY; define NP_Tamhil_NrY_0 / 'ציון כא 0' DISPLAY; define NP_Tamhil_NrY_NoPreClean / 'ציון כא ריק* מסיבת *לא מדורגת' DISPLAY; define NP_Tamhil_NrY_PreClean_NoCrd / 'ציון כא ריק* מסיבת*יישות מדורגת* ללא פעילות כא' DISPLAY; define NP_DIF_Tamhil_NrY_POS / 'ציון כא חיובי' DISPLAY; define NP_DIF_Tamhil_NrY_Null / 'ציון כא ריק' DISPLAY; define NP_DIF_Tamhil_NrY_0 / 'ציון כא 0' DISPLAY; define NP_DIF_Tamhil_NrY_NoPreClean / 'ציון כא ריק* מסיבת *יישות לא מדורגת' DISPLAY; define NP_DIF_Tamhil_NrY_PreClean_NoCrd / 'ציון כא ריק* מסיבת*יישות מדורגת* ללא פעילות כא' DISPLAY; compute NP_IND_Prob; if NP_IND_Prob = 1 then call define (_col_,"style", "style={background=lightred}"); else call define (_col_,"style", "style={background=lightgreen}"); endcomp; compute NP_DIF_Tamhil_NrY_POS; if abs(NP_DIF_Tamhil_NrY_POS) >0.03 then do; call define ('NP_DIF_Tamhil_NrY_POS',"style", "style={background=lightred}"); call define ('NP_Tamhil_NrY_POS',"style", "style={background=lightred}"); call define ('NP_NrY_POS',"style", "style={background=lightred}"); end; endcomp; compute NP_DIF_Tamhil_NrY_Null; if abs(NP_DIF_Tamhil_NrY_Null) >0.03 then do; call define ('NP_DIF_Tamhil_NrY_Null',"style", "style={background=lightred}"); call define ('NP_Tamhil_NrY_Null',"style", "style={background=lightred}"); call define ('NP_NrY_Null',"style", "style={background=lightred}"); end; endcomp; compute NP_DIF_Tamhil_NrY_0; if abs(NP_DIF_Tamhil_NrY_0) >0.03 then do; call define ('NP_DIF_Tamhil_NrY_0',"style", "style={background=lightred}"); call define ('NP_Tamhil_NrY_0',"style", "style={background=lightred}"); call define ('NP_NrY_0',"style", "style={background=lightred}"); end; endcomp; compute NP_DIF_Tamhil_NrY_NoPreClean; if abs(NP_DIF_Tamhil_NrY_NoPreClean) >0.03 then do; call define ('NP_DIF_Tamhil_NrY_NoPreClean',"style", "style={background=lightred}"); call define ('NP_Tamhil_NrY_NoPreClean',"style", "style={background=lightred}"); call define ('NP_NrY_NoPreClean',"style", "style={background=lightred}"); end; endcomp; compute NP_DIF_Tamhil_NrY_PreClean_NoCrd; if abs(NP_DIF_Tamhil_NrY_PreClean_NoCrd) >0.03 then do; call define ('NP_DIF_Tamhil_NrY_PreClean_NoCrd',"style", "style={background=lightred}"); call define ('NP_Tamhil_NrY_PreClean_NoCrd',"style", "style={background=lightred}"); call define ('NP_NrY_PreClean_NoCrd',"style", "style={background=lightred}"); end; endcomp; run; /****Pelet S1-- pepper***/ /****Pelet S1-- pepper***/ /****Pelet S1-- pepper***/ /**נדרש תיקון אך ורק ב2201-2206 בלל שבחודשים הללו אין פרי קלין**/ proc sort data=R_R.P_CrdRate_Bakara_S1 out=a_P_S1; by mon; Run; data b_P_S1; retain P_IND_Prob mon; set a_P_S1; by mon; P_DIF_Tamhil_NrY_POS=P_Tamhil_NrY_POS-LAG(P_Tamhil_NrY_POS); P_DIF_Tamhil_NrY_Null=P_Tamhil_NrY_Null-LAG(P_Tamhil_NrY_Null); P_DIF_Tamhil_NrY_0=P_Tamhil_NrY_0-LAG(P_Tamhil_NrY_0); P_DIF_Tamhil_NrY_NoPreClean=P_Tamhil_NrY_NoPreClean-LAG(P_Tamhil_NrY_NoPreClean); P_DIF_Tamhil_NrY_PreClean_NoCrd=P_Tamhil_NrY_PreClean_NoCrd-LAG(P_Tamhil_NrY_PreClean_NoCrd); format P_DIF_Tamhil_NrY_POS P_DIF_Tamhil_NrY_Null P_DIF_Tamhil_NrY_0 P_DIF_Tamhil_NrY_NoPreClean P_DIF_Tamhil_NrY_PreClean_NoCrd percent10.2; IF abs(P_DIF_Tamhil_NrY_POS)>0.03 OR abs(P_DIF_Tamhil_NrY_Null)>0.03 OR abs(P_DIF_Tamhil_NrY_0)>0.03 OR abs(P_DIF_Tamhil_NrY_NoPreClean)>0.03 OR abs(P_DIF_Tamhil_NrY_PreClean_NoCrd)>0.03 then P_IND_Prob=1; else P_IND_Prob=0;Run; proc sort data=b_P_S1; by descending mon; Run; proc sql noprint; select P_IND_Prob as Ind_P_S1_lastMon into :Ind_P_S1_lastMon Trimmed from b_P_S1 where mon=&last_mon_in_Report_YYMM1. and mon>=2502 ; quit; %put Ind_P_S1_lastMon=&Ind_P_S1_lastMon.; proc sql noprint; select case when &Ind_P_S1_lastMon.=1 then 'Red' when max(P_IND_Prob)=1 then 'Orange' else 'Green' end as Ind_Color_Sheet_P_S1 into : Ind_Color_Sheet_P_S1 Trimmed from b_P_S1 where mon>=2502 ; quit; %put Ind_Color_Sheet_P_S1=&Ind_Color_Sheet_P_S1.; /*Bakara_Crd_Rate*/ /*ODS _ALL_ CLOSE; */ /**to prevent print on screen***/ ods excel options (sheet_name='P_S1' sheet_interval="PROC" /**One table in sheet***/ embedded_titles='yes' embedded_footnotes='yes' sheet_interval="PROC" /***One table in sheet**/ gridlines = 'yes' tab_color="&Ind_Color_Sheet_P_S1." ); title; title1 bold color=red height=4 font=simplex 'אוכלוסייה-יישויות_פפר-חטיבה_860_או_סניף_998'; proc report data=b_P_S1 missing nowd split='*'; column P_IND_Prob mon ("מספר_יישויות" P_Nr P_NrY_POS P_NrY_Null P_NrY_0 P_NrY_NoPreClean P_NrY_PreClean_NoCrd) ("תמהיל_יישויות" P_Tamhil_NrY_POS P_Tamhil_NrY_Null P_Tamhil_NrY_0 P_Tamhil_NrY_NoPreClean P_Tamhil_NrY_PreClean_NoCrd) ("הפרש_בתמהיל_מחודש_קודם" P_DIF_Tamhil_NrY_POS P_DIF_Tamhil_NrY_Null P_DIF_Tamhil_NrY_0 P_DIF_Tamhil_NrY_NoPreClean P_DIF_Tamhil_NrY_PreClean_NoCrd) ; define P_IND_Prob / 'אינד להדלקה' DISPLAY; define mon / 'חודש' DISPLAY; define P_Nr / 'מספר יישויות' DISPLAY format=comma32.; define P_NrY_POS / ' ציון כא חיובי' DISPLAY format=comma32.; define P_NrY_Null / ' ציון כא ריק' DISPLAY format=comma32.; define P_NrY_0 / ' ציון כא 0' DISPLAY format=comma32.; define P_NrY_NoPreClean / 'ציון כא ריק* מסיבת*לא מדורגת' DISPLAY format=comma32.; define P_NrY_PreClean_NoCrd / 'ציון כא ריק* מסיבת *יישות מדורגת*ללא פעילות בכא' DISPLAY format=comma32.; define P_Tamhil_NrY_POS / 'ציון כא חיובי' DISPLAY; define P_Tamhil_NrY_Null / 'ציון כא ריק' DISPLAY; define P_Tamhil_NrY_0 / 'ציון כא 0' DISPLAY; define P_Tamhil_NrY_NoPreClean / 'ציון כא ריק* מסיבת *לא מדורגת' DISPLAY; define P_Tamhil_NrY_PreClean_NoCrd / 'ציון כא ריק* מסיבת*יישות מדורגת* ללא פעילות כא' DISPLAY; define P_DIF_Tamhil_NrY_POS / 'ציון כא חיובי' DISPLAY; define P_DIF_Tamhil_NrY_Null / 'ציון כא ריק' DISPLAY; define P_DIF_Tamhil_NrY_0 / 'ציון כא 0' DISPLAY; define P_DIF_Tamhil_NrY_NoPreClean / 'ציון כא ריק* מסיבת *יישות לא מדורגת' DISPLAY; define P_DIF_Tamhil_NrY_PreClean_NoCrd / 'ציון כא ריק* מסיבת*יישות מדורגת* ללא פעילות כא' DISPLAY; compute P_IND_Prob; if P_IND_Prob = 1 then call define (_col_,"style", "style={background=lightred}"); else call define (_col_,"style", "style={background=lightgreen}"); endcomp; compute P_DIF_Tamhil_NrY_POS; if abs(P_DIF_Tamhil_NrY_POS) >0.03 then do; call define ('P_DIF_Tamhil_NrY_POS',"style", "style={background=lightred}"); call define ('P_Tamhil_NrY_POS',"style", "style={background=lightred}"); call define ('P_NrY_POS',"style", "style={background=lightred}"); end; endcomp; compute P_DIF_Tamhil_NrY_Null; if abs(P_DIF_Tamhil_NrY_Null) >0.03 then do; call define ('P_DIF_Tamhil_NrY_Null',"style", "style={background=lightred}"); call define ('P_Tamhil_NrY_Null',"style", "style={background=lightred}"); call define ('P_NrY_Null',"style", "style={background=lightred}"); end; endcomp; compute P_DIF_Tamhil_NrY_0; if abs(P_DIF_Tamhil_NrY_0) >0.03 then do; call define ('P_DIF_Tamhil_NrY_0',"style", "style={background=lightred}"); call define ('P_Tamhil_NrY_0',"style", "style={background=lightred}"); call define ('P_NrY_0',"style", "style={background=lightred}"); end; endcomp; compute P_DIF_Tamhil_NrY_NoPreClean; if abs(P_DIF_Tamhil_NrY_NoPreClean) >0.03 then do; call define ('P_DIF_Tamhil_NrY_NoPreClean',"style", "style={background=lightred}"); call define ('P_Tamhil_NrY_NoPreClean',"style", "style={background=lightred}"); call define ('P_NrY_NoPreClean',"style", "style={background=lightred}"); end; endcomp; compute P_DIF_Tamhil_NrY_PreClean_NoCrd; if abs(P_DIF_Tamhil_NrY_PreClean_NoCrd) >0.03 then do; call define ('P_DIF_Tamhil_NrY_PreClean_NoCrd',"style", "style={background=lightred}"); call define ('P_Tamhil_NrY_PreClean_NoCrd',"style", "style={background=lightred}"); call define ('P_NrY_PreClean_NoCrd',"style", "style={background=lightred}"); end; endcomp; run; /***Pelet S1-b------No_Pepper+Pepper all together in one report***/ /***Pelet S1-b------No_Pepper+Pepper all together in one report***/ /***Pelet S1-b------No_Pepper+Pepper all together in one report***/ proc sort data=R_R.CrdRate_Bakara_S1b out=S1b; by descending mon; Run; proc sql noprint; select Ind_Prob as Ind_S1b_lastMon into :Ind_S1b_lastMon trimmed from S1b where mon=&last_mon_in_Report_YYMM1. ; quit; %put Ind_S1b_lastMon=&Ind_S1b_lastMon.; proc sql noprint; select case when &Ind_S1b_lastMon.=1 then 'Red' when max(Ind_Prob)=1 then 'Orange' else 'Green' end as Ind_Color_Sheet_S1_b into : Ind_Color_Sheet_S1_b trimmed from S1b ; quit; %put Ind_Color_Sheet_S1_b=&Ind_Color_Sheet_S1_b.; ods excel options (sheet_name='S1-b' sheet_interval="PROC" /**One table in sheet**/ embedded_titles='yes' embedded_footnotes='yes' gridlines = 'yes' tab_color="&Ind_Color_Sheet_S1_b." ); title; title1 bold color=red height=4 font=simplex 'בדיקת תקינות ערכי ציון כא'; title2 bold color=red height=4 font=simplex 'קריטריון להדלקה-קיימות יישויות עם ציון כא שלילי או מעל 1'; proc report data=S1b missing nowd split='*'; column ind_prob mon ("מספר יישויות" Nr_Y NR_Nulls NR_0 NR_POS_Ad1 NR_POS_MAAL1 NR_NEG) ; define IND_Prob / 'אינד להדלקה' DISPLAY; define mon / 'חודש' DISPLAY; define Nr_Y / 'מספר יישויות' DISPLAY f=comma32.; define NR_Nulls / 'ציון כא * ריק' DISPLAY f=comma32.; define NR_0/ 'ציון כא * 0' DISPLAY f=comma32.; define NR_POS_Ad1 / 'ציון כא * חיובי קטן שווה 1' DISPLAY f=comma32.; define NR_POS_MAAL1 / 'ציון כא * גדול מ1' DISPLAY f=comma32. style(header)={font_weight=bold color=red}; define NR_NEG / 'ציון כא * שלילי' DISPLAY f=comma32. style(header)={font_weight=bold color=red}; compute IND_Prob; if IND_Prob = 1 then call define(_col_,"style", "style={background=lightred}"); else call define (_col_,"style", "style={background=lightgreen}"); endcomp; compute NR_POS_MAAL1; if NR_POS_MAAL1 >0 then call define ('NR_POS_MAAL1',"style", "style={background=lightred}"); endcomp; compute NR_NEG; if NR_NEG >0 then call define ('NR_NEG',"style", "style={background=lightred}"); endcomp; Run; /***Pelet S2--NP***/ /***Pelet S2--NP***/ /***Pelet S2--NP***/ proc sort data=R_R.CrdRate_Bakara_S2_NP out=S2_NP; by mon; Run; data S2_NP_b; retain NP_IND_Prob mon; set S2_NP; by mon; NP_Dif_Tamhil_PRE_CLEAN=NP_Tamhil_PRE_CLEAN-LAG(NP_Tamhil_PRE_CLEAN); NP_Dif_Tamhil_NoPreClean=NP_Tamhil_NoPreClean-LAG(NP_Tamhil_NoPreClean); NP_Dif_Tamhil_PRE_CLEAN_CRD=NP_Tamhil_PRE_CLEAN_CRD-LAG(NP_Tamhil_PRE_CLEAN_CRD); NP_Dif_Tamhil_PRE_CLEAN_NoCRD=NP_Tamhil_PRE_CLEAN_NoCRD-LAG(NP_Tamhil_PRE_CLEAN_NoCRD); format NP_Dif_Tamhil_PRE_CLEAN NP_Dif_Tamhil_NoPreClean NP_Dif_Tamhil_PRE_CLEAN_CRD NP_Dif_Tamhil_PRE_CLEAN_NoCRD percent10.2; IF abs(NP_Dif_Tamhil_PRE_CLEAN)>0.03 OR abs(NP_Dif_Tamhil_NoPreClean)>0.03 OR abs(NP_Dif_Tamhil_PRE_CLEAN_CRD)>0.03 OR abs(NP_Dif_Tamhil_PRE_CLEAN_NoCRD)>0.03 then NP_IND_Prob=1; else NP_IND_Prob=0; Run; proc sort data=S2_NP_b; by descending mon; Run; proc sql noprint; select NP_IND_Prob as Ind_NP_S2_lastMon into :Ind_NP_S2_lastMon trimmed from S2_NP_b where mon=&last_mon_in_Report_YYMM1. ; quit; %put Ind_NP_S2_lastMon=&Ind_NP_S2_lastMon.; proc sql noprint; select case when &Ind_NP_S2_lastMon.=1 then 'Red' when max(NP_IND_Prob)=1 then 'Orange' else 'Green' end as Ind_Color_Sheet_NP_S2 into : Ind_Color_Sheet_NP_S2 trimmed from S2_NP_b ; quit; %put Ind_Color_Sheet_NP_S2=&Ind_Color_Sheet_NP_S2.; ods excel options (sheet_name='S2_NP' sheet_interval="PROC" /**One table in sheet***/ embedded_titles='yes' embedded_footnotes='yes' gridlines = 'yes' tab_color="&Ind_Color_Sheet_NP_S2." ); title; title1 bold color=red height=5 font=simplex 'בקרת מספר יישויות לפי-כן/לא מדורגת,כן/לא פעילות או קיום בכא שנה אחרונה'; title2 bold color=red height=5 font=simplex 'אוכלוסייה-יישויות שאינן פפר'; title3 bold color=red height=5 font=simplex 'אינד להדלקה-הפרש בתמהיל מעל 3% מחודש קודם'; title4 bold color=black height=5 font=simplex 'בקרה זו הינה עזר בלבד ולא בקרה ישירה של ציון כא'; title5 bold color=black height=5 font=simplex 'בדיקת כא נעשית בשנה אחרונה'; proc report data=S2_NP_b missing nowd split='*'; column NP_IND_Prob mon ("מספר יישויות" NP_nr_Y NP_nr_PRE_CLEAN NP_nr_PRE_CLEAN_CRD NP_nr_PRE_CLEAN_NoCRD NP_nr_NoPreClean) ("תמהיל יישויות" NP_Tamhil_PRE_CLEAN NP_Tamhil_PRE_CLEAN_CRD NP_Tamhil_PRE_CLEAN_NoCRD NP_Tamhil_NoPreClean) ("הפרש תמהיל מחודש קודם" NP_Dif_Tamhil_PRE_CLEAN NP_Dif_Tamhil_PRE_CLEAN_CRD NP_Dif_Tamhil_PRE_CLEAN_NoCRD NP_Dif_Tamhil_NoPreClean) ; define NP_IND_Prob / 'אינד להדלקה' DISPLAY; define mon / 'חודש' DISPLAY; define NP_nr_Y / 'מספר יישויות' DISPLAY f=comma32.; define NP_nr_PRE_CLEAN / 'יישויות*מדורגות' DISPLAY f=comma32.; define NP_nr_PRE_CLEAN_CRD / 'יישויות_מדורגות*קיימת פעילות בכא' DISPLAY f=comma32.; define NP_nr_PRE_CLEAN_NoCRD / 'יישויות_מדורגות*אין פעילות בכא' DISPLAY f=comma32.; define NP_nr_NoPreClean / 'יישויות*לא מדורגות' DISPLAY f=comma32.; define NP_Tamhil_PRE_CLEAN / 'יישויות_מדורגות' DISPLAY; define NP_Tamhil_PRE_CLEAN_CRD / 'יישויות_מדורגות*קיימת פעילות בכא' DISPLAY; define NP_Tamhil_PRE_CLEAN_NoCRD / 'יישויות_מדורגות*אין פעילות בכא' DISPLAY; define NP_Tamhil_NoPreClean / 'יישויות*לא מדורגות' DISPLAY; define NP_Dif_Tamhil_PRE_CLEAN / 'יישויות_מדורגות' DISPLAY style(header)={font_weight=bold color=blue}; define NP_Dif_Tamhil_PRE_CLEAN_CRD / 'יישויות_מדורגות*קיימת פעילות בכא' DISPLAY style(header)={font_weight=bold color=blue}; define NP_Dif_Tamhil_PRE_CLEAN_NoCRD / 'יישויות_מדורגות*אין פעילות בכא' DISPLAY style(header)={font_weight=bold color=blue}; define NP_Dif_Tamhil_NoPreClean / 'יישויות*לא מדורגות' DISPLAY style(header)={font_weight=bold color=blue}; compute NP_IND_Prob; if NP_IND_Prob = 1 then call define (_col_,"style", "style={background=lightred}"); else call define (_col_,"style", "style={background=lightgreen}"); endcomp; compute NP_Dif_Tamhil_PRE_CLEAN; if abs(NP_Dif_Tamhil_PRE_CLEAN )>0.03 then do; call define ('NP_Dif_Tamhil_PRE_CLEAN',"style", "style={background=lightyellow}"); call define ('NP_nr_PRE_CLEAN',"style", "style={background=lightyellow}"); call define ('NP_Tamhil_PRE_CLEAN',"style", "style={background=lightyellow}"); end; endcomp; compute NP_Dif_Tamhil_PRE_CLEAN_CRD; if abs(NP_Dif_Tamhil_PRE_CLEAN_CRD )>0.03 then do; call define ('NP_Dif_Tamhil_PRE_CLEAN_CRD',"style", "style={background=lightyellow}"); call define ('NP_nr_PRE_CLEAN_CRD',"style", "style={background=lightyellow}"); call define ('NP_Tamhil_PRE_CLEAN_CRD',"style", "style={background=lightyellow}"); end; endcomp; compute NP_Dif_Tamhil_PRE_CLEAN_NoCRD; if abs(NP_Dif_Tamhil_PRE_CLEAN_NoCRD )>0.03 then do; call define ('NP_Dif_Tamhil_PRE_CLEAN_NoCRD',"style", "style={background=lightyellow}"); call define ('NP_Tamhil_PRE_CLEAN_NoCRD',"style", "style={background=lightyellow}"); call define ('NP_nr_PRE_CLEAN_NoCRD',"style", "style={background=lightyellow}"); end; endcomp; compute NP_Dif_Tamhil_NoPreClean; if abs(NP_Dif_Tamhil_NoPreClean )>0.03 then do; call define ('NP_Dif_Tamhil_NoPreClean',"style", "style={background=lightyellow}"); call define ('NP_Tamhil_NoPreClean',"style", "style={background=lightyellow}"); call define ('NP_nr_NoPreClean',"style", "style={background=lightyellow}"); end; endcomp; Run; /***Pelet S2---pepper***/ /***Pelet S2---pepper***/ /***Pelet S2---pepper***/ proc sort data=R_R.CrdRate_Bakara_S2_P out=S2_P; by mon; Run; data S2_P_b; retain P_IND_Prob mon; set S2_P; by mon; P_Dif_Tamhil_PRE_CLEAN=P_Tamhil_PRE_CLEAN-LAG(P_Tamhil_PRE_CLEAN); P_Dif_Tamhil_NoPreClean=P_Tamhil_NoPreClean-LAG(P_Tamhil_NoPreClean); P_Dif_Tamhil_PRE_CLEAN_CRD=P_Tamhil_PRE_CLEAN_CRD-LAG(P_Tamhil_PRE_CLEAN_CRD); P_Dif_Tamhil_PRE_CLEAN_NoCRD=P_Tamhil_PRE_CLEAN_NoCRD-LAG(P_Tamhil_PRE_CLEAN_NoCRD); format P_Dif_Tamhil_PRE_CLEAN P_Dif_Tamhil_NoPreClean P_Dif_Tamhil_PRE_CLEAN_CRD P_Dif_Tamhil_PRE_CLEAN_NoCRD percent10.2; IF abs(P_Dif_Tamhil_PRE_CLEAN)>0.03 OR abs(P_Dif_Tamhil_NoPreClean)>0.03 OR abs(P_Dif_Tamhil_PRE_CLEAN_CRD)>0.03 OR abs(P_Dif_Tamhil_PRE_CLEAN_NoCRD)>0.03 then P_IND_Prob=1; else P_IND_Prob=0; Run; proc sort data=S2_P_b; by descending mon; Run; proc sql noprint; select P_IND_Prob as Ind_P_S2_lastMon into :Ind_P_S2_lastMon trimmed from S2_P_b where mon>=2502 AND mon=&last_mon_in_Report_YYMM1. ; quit; %put Ind_P_S2_lastMon=&Ind_P_S2_lastMon.; proc sql noprint; select case when &Ind_P_S2_lastMon.=1 then 'Red' when max(P_IND_Prob)=1 then 'Orange' else 'Green' end as Ind_Color_Sheet_P_S2 into : Ind_Color_Sheet_P_S2 trimmed from S2_P_b where mon>=2502 ; quit; %put Ind_Color_Sheet_P_S2=&Ind_Color_Sheet_P_S2.; ods excel options (sheet_name='S2_P' sheet_interval="PROC" /***One table in sheet**/ absolute_column_width="13,13,13,18,18,18,18,18,18,18,18,18,18,18,18,18" row_heights='30,16,20,20,20' embedded_titles='yes' embedded_footnotes='yes' gridlines = 'yes' tab_color="&Ind_Color_Sheet_P_S2." ); title; title1 bold color=red height=5 font=simplex 'בקרת מספר יישויות לפי-כן/לא אוכלוסייה מדורגת,כן/לא פעילות או קיום כא שנה אחרונה'; title2 bold color=red height=5 font=simplex 'אוכלוסייה-יישויות פפר'; title3 bold color=red height=5 font=simplex 'אינד להדלקה-הפרש בתמהיל מעל 3% מחודש קודם'; title4 bold color=red height=5 font=simplex 'הדלקת גליון-החל מ 2502 בגלל שזה פפר'; title5 bold color=black height=5 font=simplex 'בקרה זו הינה עזר בלבד ולא בקרה ישירה של ציון כא'; title6 bold color=black height=5 font=simplex 'בדיקת פעילות כא נעשית בשנה אחרונה'; proc report data=S2_P_b missing nowd split='*'; column P_IND_Prob mon ("מספר יישויות" P_nr_Y P_nr_PRE_CLEAN P_nr_PRE_CLEAN_CRD P_nr_PRE_CLEAN_NoCRD P_nr_NoPreClean) ("תמהיל יישויות" P_Tamhil_PRE_CLEAN P_Tamhil_PRE_CLEAN_CRD P_Tamhil_PRE_CLEAN_NoCRD P_Tamhil_NoPreClean) ("הפרש תמהיל מחודש קודם" P_Dif_Tamhil_PRE_CLEAN P_Dif_Tamhil_PRE_CLEAN_CRD P_Dif_Tamhil_PRE_CLEAN_NoCRD P_Dif_Tamhil_NoPreClean) ; define P_IND_Prob / 'אינד להדלקה' DISPLAY; define mon / 'חודש' DISPLAY; define P_nr_Y / 'מספר יישויות' DISPLAY f=comma32.; define P_nr_PRE_CLEAN / 'יישויות*מדורגות' DISPLAY f=comma32.; define P_nr_PRE_CLEAN_CRD / 'יישויות_מדורגות*קיימת פעילות בכא' DISPLAY f=comma32.; define P_nr_PRE_CLEAN_NoCRD / 'יישויות_מדורגות*אין פעילות בכא' DISPLAY f=comma32.; define P_nr_NoPreClean / 'יישויות*לא מדורגות' DISPLAY f=comma32.; define P_Tamhil_PRE_CLEAN / 'יישויות_מדורגות' DISPLAY; define P_Tamhil_PRE_CLEAN_CRD / 'יישויות_מדורגות*קיימת פעילות בכא' DISPLAY; define P_Tamhil_PRE_CLEAN_NoCRD / 'יישויות_מדורגות*אין פעילות בכא' DISPLAY; define P_Tamhil_NoPreClean / 'יישויות*לא מדורגות' DISPLAY; define P_Dif_Tamhil_PRE_CLEAN / 'יישויות_מדורגות' DISPLAY style(header)={font_weight=bold color=blue}; define P_Dif_Tamhil_PRE_CLEAN_CRD / 'יישויות_מדורגות*קיימת פעילות בכא' DISPLAY style(header)={font_weight=bold color=blue}; define P_Dif_Tamhil_PRE_CLEAN_NoCRD / 'יישויות_מדורגות*אין פעילות בכא' DISPLAY style(header)={font_weight=bold color=blue}; define P_Dif_Tamhil_NoPreClean / 'יישויות*לא מדורגות' DISPLAY style(header)={font_weight=bold color=blue}; compute P_IND_Prob; if P_IND_Prob = 1 then call define (_col_,"style", "style={background=lightred}"); else call define (_col_,"style", "style={background=lightgreen}"); endcomp; compute P_Dif_Tamhil_PRE_CLEAN; if abs(P_Dif_Tamhil_PRE_CLEAN )>0.03 then do; call define ('P_Dif_Tamhil_PRE_CLEAN',"style", "style={background=lightyellow}"); call define ('P_nr_PRE_CLEAN',"style", "style={background=lightyellow}"); call define ('P_Tamhil_PRE_CLEAN',"style", "style={background=lightyellow}"); end; endcomp; compute P_Dif_Tamhil_PRE_CLEAN_CRD; if abs(P_Dif_Tamhil_PRE_CLEAN_CRD )>0.03 then do; call define ('P_Dif_Tamhil_PRE_CLEAN_CRD',"style", "style={background=lightyellow}"); call define ('P_nr_PRE_CLEAN_CRD',"style", "style={background=lightyellow}"); call define ('P_Tamhil_PRE_CLEAN_CRD',"style", "style={background=lightyellow}"); end; endcomp; compute P_Dif_Tamhil_PRE_CLEAN_NoCRD; if abs(P_Dif_Tamhil_PRE_CLEAN_NoCRD )>0.03 then do; call define ('P_Dif_Tamhil_PRE_CLEAN_NoCRD',"style", "style={background=lightyellow}"); call define ('P_Tamhil_PRE_CLEAN_NoCRD',"style", "style={background=lightyellow}"); call define ('P_nr_PRE_CLEAN_NoCRD',"style", "style={background=lightyellow}"); end; endcomp; compute P_Dif_Tamhil_NoPreClean; if abs(P_Dif_Tamhil_NoPreClean )>0.03 then do; call define ('P_Dif_Tamhil_NoPreClean',"style", "style={background=lightyellow}"); call define ('P_Tamhil_NoPreClean',"style", "style={background=lightyellow}"); call define ('P_nr_NoPreClean',"style", "style={background=lightyellow}"); end; endcomp; Run; /***Pelet S3--No pepper****/ /***Pelet S3--No pepper****/ /***Pelet S3--No pepper****/ proc sort data=R_R.NP_CrdRate_Bakara_S3; by Help mon; Run; Data NP_S3_a; retain NP_IND_Prob mon _prev_mx_pos_prob_ Ind_CRD_12Mon help Ind_Stira NP_nr NP_Tamhil NP_dif_tamhil ; set R_R.NP_CrdRate_Bakara_S3; by Help; NP_dif_tamhil=NP_Tamhil-LAG(NP_Tamhil); If first.Help then do; NP_dif_tamhil=.; end; Format NP_dif_tamhil percent10.2; IF abs(NP_dif_tamhil)>0.03 then NP_IND_Prob=1; else NP_IND_Prob=0; Run; proc sort data=NP_S3_a; by descending mon descending Ind_Stira descending Ind_CRD_12Mon; Run; data NP_S3_b; retain NP_IND_Prob mon; set NP_S3_a; IF abs(NP_dif_tamhil)>0.03 then NP_IND_Prob=1; else NP_IND_Prob=0; Run; proc sql noprint; select max(NP_IND_Prob) as Ind_NP_S3_lastMon into :Ind_NP_S3_lastMon trimmed from NP_S3_b where mon=&last_mon_in_Report_YYMM1. ; quit; %put Ind_NP_S3_lastMon=&Ind_NP_S3_lastMon.; proc sql noprint; select case when &Ind_NP_S3_lastMon.=1 then 'Red' when max(NP_IND_Prob)=1 then 'Orange' else 'Green' end as Ind_Color_Sheet_NP_S3 into : Ind_Color_Sheet_NP_S3 trimmed from NP_S3_b ; quit; %put Ind_Color_Sheet_NP_S3=&Ind_Color_Sheet_NP_S3.; proc format; value red_fmt 1=lightred other=lightgreen; run; proc format; value red2_fmt 1=lightorange other=white; run; ods excel options(sheet_name="S3-NP_one_tbl" embedded_titles='yes' sheet_interval="PROC" /**one table in sheet***/ embedded_titles='yes' embedded_footnotes='yes' gridlines = 'yes' tab_color="&Ind_Color_Sheet_NP_S3." ); title; title1 j=l bold color=red height=4 font=simplex 'אוכלוסיה-יישויות מדורגות שאינן פפר'; title2 j=l bold color=red height=4 font=simplex 'בקרת אוכלוסיה של דוד'; title3 j=l bold color=red height=4 font=simplex 'פלט כלל חודשים בטבלה אחת'; title4 j=l bold color=black height=4 font=simplex 'ב2505 בוצע שינוי בלוגיקה ומעתה ואילך נבדק ניצול בכא או החזר לכא או קיום כא ללא ניצול או החזר'; proc report data=NP_S3_b missing nowd split='*' style(report)={frame=box font_size=8pt bordercolor=black borderwidth=2px} ; columns NP_IND_Prob mon Ind_CRD_12Mon _prev_mx_pos_prob_ Ind_Stira NP_nr NP_Tamhil NP_dif_tamhil; define mon/display order=data ' '; define Ind_CRD_12Mon/display 'אינד לקיום *פעילות כא'; define _prev_mx_pos_prob_/display 'אינד ציון כא *חיובי או ריק'; define Ind_Stira/display 'אינד לסתירה'; define NP_IND_Prob/display 'אינד להדלקה'; define NP_nr/display 'מספר יישויות'; define NP_Tamhil/display 'תמהיל יישויות'; define NP_dif_tamhil/display 'הפרש תמהיל יישויות'; compute NP_IND_Prob; IF NP_IND_Prob=1 then call define(_col_,"Style","style={background=LIGHTRED}"); else call define(_col_,"Style","style={background=LIGHTGREEN}"); endcomp; compute Ind_Stira; If Ind_Stira=1 then call define (_row_,"style","style=[backgroundcolor=lightblue]"); endcomp; compute NP_dif_tamhil; if NP_IND_Prob=1 then call define(_col_,"Style","style={background=LIGHTRED}"); endcomp; run; ods excel options(sheet_name="S3-NP_Multi_tables" embedded_titles='yes' sheet_interval="none" /**Multiple tables in sheet***/ embedded_titles='yes' embedded_footnotes='yes' gridlines = 'yes' tab_color="&Ind_Color_Sheet_NP_S3." ); /**Here multiple reports in same sheet***/ title; title1 j=l bold color=red height=4 font=simplex 'חלוקת הפלט למספר טבלאות'; title2 j=l 'אינד להדלקה'; proc report data=NP_S3_b missing nowd split='*' style(report)={frame=box font_size=8pt bordercolor=black borderwidth=2px} ; columns Ind_CRD_12Mon _prev_mx_pos_prob_ Ind_Stira NP_IND_Prob,mon; define Ind_CRD_12Mon/group 'אינד לקיום *פעילות כא'; define _prev_mx_pos_prob_/group 'אינד ציון כא *חיובי או ריק'; define Ind_Stira/group 'אינד לסתירה'; define mon/across order=data ' '; define NP_IND_Prob/mean 'אינד להדלקה'; compute NP_IND_Prob; call define(_col_,"Style","style={background=red_fmt.}"); endcomp; compute Ind_Stira; call define(_col_,"Style","style={background=lightblue.}"); endcomp; run; title; title j=l 'מספר יישויות'; proc report data=NP_S3_b missing nowd split='*' style(report)={frame=box font_size=8pt bordercolor=black borderwidth=2px} ; columns Ind_CRD_12Mon _prev_mx_pos_prob_ Ind_Stira NP_nr,mon; define Ind_CRD_12Mon/group 'אינד לקיום *פעילות כא'; define _prev_mx_pos_prob_/group 'אינד ציון כא *חיובי או ריק'; define Ind_Stira/group 'אינד לסתירה'; define mon/across order=data ' '; define NP_nr/mean 'מספר יישויות' f=comma32.; compute Ind_Stira; call define(_col_,"Style","style={background=lightblue.}"); endcomp; run; title; title j=l 'תמהיל יישויות'; proc report data=NP_S3_b missing nowd split='*' style(report)={frame=box font_size=8pt bordercolor=black borderwidth=2px} ; columns Ind_CRD_12Mon _prev_mx_pos_prob_ Ind_Stira NP_Tamhil,mon; define Ind_CRD_12Mon/group 'אינד לקיום *פעילות כא'; define _prev_mx_pos_prob_/group 'אינד ציון כא *חיובי או ריק'; define Ind_Stira/group 'אינד לסתירה'; define mon/across order=data ' '; define NP_Tamhil/mean 'תמהיל יישויות'; compute Ind_Stira; call define(_col_,"Style","style={background=lightblue.}"); endcomp; run; title; title j=l 'הפרש בתמהיל יישויות מחודש קודם'; proc report data=NP_S3_b missing nowd split='*' style(report)={frame=box font_size=8pt bordercolor=black borderwidth=2px} ; columns Ind_CRD_12Mon _prev_mx_pos_prob_ Ind_Stira NP_dif_tamhil,mon; define Ind_CRD_12Mon/group 'אינד לקיום *פעילות כא'; define _prev_mx_pos_prob_/group 'אינד ציון כא *חיובי או ריק'; define Ind_Stira/group 'אינד לסתירה'; define mon/across order=data ' '; define NP_dif_tamhil/mean 'הפרש תמהיל יישויות'; compute Ind_Stira; If Ind_Stira=1 then call define (_row_,"style","style=[backgroundcolor=lightblue]"); endcomp; run; /***Pelet S3--pepper****/ /***Pelet S3--pepper****/ /***Pelet S3--pepper****/ proc sort data=R_R.P_CrdRate_Bakara_S3; by Help mon; Run; Data P_S3_a; retain P_IND_Prob mon _prev_mx_pos_prob_ Ind_CRD_12Mon help Ind_Stira P_nr P_Tamhil P_dif_tamhil ; set R_R.P_CrdRate_Bakara_S3; by Help; P_dif_tamhil=P_Tamhil-LAG(P_Tamhil); If first.Help then do; P_dif_tamhil=.; end; Format P_dif_tamhil percent10.2; IF abs(P_dif_tamhil)>0.03 then P_IND_Prob=1; else P_IND_Prob=0; Run; proc sort data=P_S3_a; by descending mon descending Ind_Stira descending Ind_CRD_12Mon; Run; data P_S3_b; retain P_IND_Prob mon; set P_S3_a; IF abs(P_dif_tamhil)>0.03 then P_IND_Prob=1; else P_IND_Prob=0; Run; proc sql noprint; select max(P_IND_Prob) as Ind_P_S3_lastMon into :Ind_P_S3_lastMon trimmed from P_S3_b where mon=&last_mon_in_Report_YYMM1. and mon>=2502 ; quit; %put Ind_P_S3_lastMon=&Ind_P_S3_lastMon.; proc sql noprint; select case when &Ind_P_S3_lastMon.=1 then 'Red' when max(P_IND_Prob)=1 then 'Orange' else 'Green' end as Ind_Color_Sheet_P_S3 into : Ind_Color_Sheet_P_S3 trimmed from P_S3_b where mon>=2502 ; quit; %put Ind_Color_Sheet_P_S3=&Ind_Color_Sheet_P_S3.; ods excel options(sheet_name="S3-P_one_tbl" embedded_titles='yes' sheet_interval="PROC" /**one table in sheet***/ embedded_titles='yes' embedded_footnotes='yes' gridlines = 'yes' tab_color="&Ind_Color_Sheet_P_S3." ); title; title1 j=l bold color=red height=4 font=simplex 'אוכלוסיה-יישויות מדורגות פפר'; title2 j=l bold color=red height=4 font=simplex 'בקרת אוכלוסיה של דוד'; title3 j=l bold color=red height=4 font=simplex 'פלט כלל חודשים בטבלה אחת'; title4 j=l bold color=black height=4 font=simplex 'ב2505 בוצע שינוי בלוגיקה ומעתה ואילך נבדק ניצול בכא או החזר לכא או קיום כא ללא ניצול או החזר'; proc report data=P_S3_b missing nowd split='*' style(report)={frame=box font_size=8pt bordercolor=black borderwidth=2px} ; columns P_IND_Prob mon Ind_CRD_12Mon _prev_mx_pos_prob_ Ind_Stira P_nr P_Tamhil P_dif_tamhil; define mon/display order=data ' '; define Ind_CRD_12Mon/display 'אינד לקיום *פעילות כא'; define _prev_mx_pos_prob_/display 'אינד ציון כא *חיובי או ריק'; define Ind_Stira/display 'אינד לסתירה'; define P_IND_Prob/display 'אינד להדלקה'; define P_nr/display 'מספר יישויות'; define P_Tamhil/display 'תמהיל יישויות'; define P_dif_tamhil/display 'הפרש תמהיל יישויות'; compute P_IND_Prob; IF P_IND_Prob=1 then call define(_col_,"Style","style={background=LIGHTRED}"); else call define(_col_,"Style","style={background=LIGHTGREEN}"); endcomp; compute Ind_Stira; If Ind_Stira=1 then call define (_row_,"style","style=[backgroundcolor=lightblue]"); endcomp; compute P_dif_tamhil; if P_IND_Prob=1 then call define(_col_,"Style","style={background=LIGHTRED}"); endcomp; run; ods excel options(sheet_name="S3-P_Multi_tables" embedded_titles='yes' sheet_interval="none" /**Multiple tables in sheet***/ embedded_titles='yes' embedded_footnotes='yes' gridlines = 'yes' tab_color="&Ind_Color_Sheet_P_S3." ); /**Here multiple reports in same sheet***/ title; title1 j=l bold color=red height=5 font=simplex 'אוכלוסייה-יישויות מדורגות_פפר'; title2 j=l bold color=red height=5 font=simplex 'בקרת אוכלוסיה של דוד'; title3 j=l 'אינד להדלקה'; proc report data=P_S3_b missing nowd split='*' style(report)={frame=box font_size=8pt bordercolor=black borderwidth=2px} ; columns Ind_CRD_12Mon _prev_mx_pos_prob_ Ind_Stira P_IND_Prob,mon; define Ind_CRD_12Mon/group 'אינד לקיום *פעילות כא'; define _prev_mx_pos_prob_/group 'אינד ציון כא *חיובי או ריק'; define Ind_Stira/group 'אינד לסתירה'; define mon/across order=data ' '; define P_IND_Prob/mean 'אינד להדלקה'; compute P_IND_Prob; call define(_col_,"Style","style={background=red_fmt.}"); endcomp; compute Ind_Stira; call define(_col_,"Style","style={background=red2_fmt.}"); endcomp; run; title; title j=l 'מספר יישויות'; proc report data=P_S3_b missing nowd split='*' style(report)={frame=box font_size=8pt bordercolor=black borderwidth=2px} ; columns Ind_CRD_12Mon _prev_mx_pos_prob_ Ind_Stira P_nr,mon; define Ind_CRD_12Mon/group 'אינד לקיום *פעילות כא'; define _prev_mx_pos_prob_/group 'אינד ציון כא *חיובי או ריק'; define Ind_Stira/group 'אינד לסתירה'; define mon/across order=data ' '; define P_nr/mean 'מספר יישויות' f=comma32.; compute Ind_Stira; call define(_col_,"Style","style={background=red2_fmt.}"); endcomp; run; title; title j=l 'תמהיל יישויות'; proc report data=P_S3_b missing nowd split='*' style(report)={frame=box font_size=8pt bordercolor=black borderwidth=2px} ; columns Ind_CRD_12Mon _prev_mx_pos_prob_ Ind_Stira P_Tamhil,mon; define Ind_CRD_12Mon/group 'אינד לקיום *פעילות כא'; define _prev_mx_pos_prob_/group 'אינד ציון כא *חיובי או ריק'; define Ind_Stira/group 'אינד לסתירה'; define mon/across order=data ' '; define P_Tamhil/mean 'תמהיל יישויות'; compute Ind_Stira; call define(_col_,"Style","style={background=red2_fmt.}"); endcomp; run; title; title j=l 'הפרש בתמהיל יישויות מחודש קודם'; proc report data=P_S3_b missing nowd split='*' style(report)={frame=box font_size=8pt bordercolor=black borderwidth=2px} ; columns Ind_CRD_12Mon _prev_mx_pos_prob_ Ind_Stira P_dif_tamhil,mon; define Ind_CRD_12Mon/group 'אינד לקיום *פעילות כא'; define _prev_mx_pos_prob_/group 'אינד ציון כא *חיובי או ריק'; define Ind_Stira/group 'אינד לסתירה'; define mon/across order=data ' '; define P_dif_tamhil/mean 'הפרש תמהיל יישויות'; compute Ind_Stira; call define(_col_,"Style","style={background=red2_fmt.}"); endcomp; run; /*****Pelet S4--NO pepper****/ /*****Pelet S4--NO pepper****/ /*****Pelet S4--NO pepper****/ proc sort data=R_R.NP_CrdRate_Bakara_S4; by mon; Run; Data a_pelet_Sheet_S4_NP; retain NP_IND_Prob mon; set R_R.NP_CrdRate_Bakara_S4; dif_p10=abs(sum(P10,-LAG(p10))); dif_p20=abs(sum(P20,-LAG(p20))); dif_p30=abs(sum(P30,-LAG(p30))); dif_p40=abs(sum(P40,-LAG(p40))); dif_p50=abs(sum(P50,-LAG(p50))); dif_p60=abs(sum(P60,-LAG(p60))); dif_p70=abs(sum(P70,-LAG(p70))); dif_p80=abs(sum(P80,-LAG(p80))); dif_p90=abs(sum(P90,-LAG(p90))); dif_p95=abs(sum(P95,-LAG(p95))); dif_p100=abs(sum(P100,-LAG(p100))); If _N_=1 then do; dif_p10=.; dif_p20=.; dif_p30=.; dif_p40=.; dif_p50=.; dif_p60=.; dif_p70=.; dif_p80=.; dif_p90=.; dif_p95=.; dif_p100=.; end; Format dif_p10 dif_p20 dif_p30 dif_p40 dif_p50 dif_p60 dif_p70 dif_p80 dif_p90 dif_p95 dif_p100 percent10.5; IF abs(dif_p10)>0.05 OR abs(dif_p20)>0.05 OR abs(dif_p30)>0.05 OR abs(dif_p40)>0.05 OR abs(dif_p50)>0.05 OR abs(dif_p60)>0.05 OR abs(dif_p70)>0.05 OR abs(dif_p80)>0.05 OR abs(dif_p90)>0.05 OR abs(dif_p95)>0.05 OR abs(dif_p100)>0.05 then NP_IND_Prob=1; else NP_IND_Prob=0; Run; proc sort data=a_pelet_Sheet_S4_NP out=NP_S4; by descending mon; Run; proc sql noprint; select NP_IND_Prob as Ind_NP_S4_lastMon into :Ind_NP_S4_lastMon trimmed from NP_S4 where mon=&last_mon_in_Report_YYMM1. ; quit; %put Ind_NP_S4_lastMon=&Ind_NP_S4_lastMon.; proc sql noprint; select case when &Ind_NP_S4_lastMon.=1 then 'Red' when max(NP_IND_Prob)=1 then 'Orange' else 'Green' end as Ind_Color_Sheet_NP_S4 into : Ind_Color_Sheet_NP_S4 trimmed from NP_S4 ; quit; %put Ind_Color_Sheet_NP_S4=&Ind_Color_Sheet_NP_S4.; ods excel options (sheet_name='S4-NP' sheet_interval="PROC" /***One table in sheet**/ absolute_column_width="12,8,8,8,8,8,8,8,8,8,8,8,8,8,8,8,8,8,8,8,8,8,8,8" row_heights='30,16,20,20,20' tab_color="&Ind_Color_Sheet_NP_S4." embedded_titles='yes' embedded_footnotes='yes' gridlines = 'yes' ); title; title1 bold color=red height=5 font=simplex 'אוכ-יישויות שאינן פפר בעלות ציון כא חיובי'; title2 bold color=red height=5 font=simplex 'Percentiles בדיקת יציבות אחוזונים'; title3 bold color=red height=5 font=simplex 'קריטריון להדלקה- קפיצה מעל 5% כלומר מעל 0.05 בערך האחוזון מחודש קודם'; title4 bold color=black height=5 font=simplex 'בודקים הפרש בערכי אחוזונים בכל חודש מחודש קודם'; title5 bold color=black height=5 font=simplex 'כלומר בודקים דלתא בערך האחוזון מחודש לחודש קודם'; title6 bold color=black height=5 font=simplex 'לשם נוחיות הערכים מוצגים באחוזים ולא במספר עשרוני כלומר ערכים בין 0 ל 100%'; proc report data=NP_S4 missing nowd style(report)={frame=box font_size=8pt bordercolor=black borderwidth=2px} ; column NP_IND_Prob mon ("אחוזון_Percentiles" P10 P20 P30 P40 P50 P60 P70 P80 P90 P95 P100 ) ("הפרש_באחוזון מחודש קודם" dif_p10 dif_p20 dif_p30 dif_p40 dif_p50 dif_p60 dif_p70 dif_p80 dif_p90 dif_p95 dif_p100 ) n ; define NP_IND_Prob/DISPLAY 'אינד להדלקה'; define mon/DISPLAY 'חודש' ; define P10/DISPLAY '10' f=percent10.4 style(column)=[font_size=8pt]; define P20/DISPLAY '20' f=percent10.4 style(column)=[font_size=8pt]; define P30/DISPLAY '30' f=percent10.4 style(column)=[font_size=8pt]; define P40/DISPLAY '40' f=percent10.4 style(column)=[font_size=8pt]; define P50/DISPLAY '50' f=percent10.4 style(column)=[font_size=8pt]; define P60/DISPLAY '60' f=percent10.4 style(column)=[font_size=8pt]; define P70/DISPLAY '70' f=percent10.4 style(column)=[font_size=8pt]; define P80/DISPLAY '80' f=percent10.4 style(column)=[font_size=8pt]; define P90/DISPLAY '90' f=percent10.4 style(column)=[font_size=8pt]; define P95/DISPLAY '95' f=percent10.4 style(column)=[font_size=8pt]; define P100/DISPLAY '100' f=percent10.4 style(column)=[font_size=8pt]; define dif_p10/DISPLAY '10' f=percent10.4 style=[background=highlight_S4_Fmt.] style(column)=[font_size=8pt]; define dif_p20/DISPLAY '20' f=percent10.4 style=[background=highlight_S4_Fmt.] style(column)=[font_size=8pt]; define dif_p30/DISPLAY '30' f=percent10.4 style=[background=highlight_S4_Fmt.] style(column)=[font_size=8pt] ; define dif_p40/DISPLAY '40' f=percent10.4 style=[background=highlight_S4_Fmt.] style(column)=[font_size=8pt]; define dif_p50/DISPLAY '50' f=percent10.4 style=[background=highlight_S4_Fmt.] style(column)=[font_size=8pt]; define dif_p60/DISPLAY '60' f=percent10.4 style=[background=highlight_S4_Fmt.] style(column)=[font_size=8pt]; define dif_p70/DISPLAY '70' f=percent10.4 style=[background=highlight_S4_Fmt.] style(column)=[font_size=8pt]; define dif_p80/DISPLAY '80' f=percent10.4 style=[background=highlight_S4_Fmt.] style(column)=[font_size=8pt]; define dif_p90/DISPLAY '90' f=percent10.4 style=[background=highlight_S4_Fmt.] style(column)=[font_size=8pt]; define dif_p95/DISPLAY '95' f=percent10.4 style=[background=highlight_S4_Fmt.] style(column)=[font_size=8pt]; define dif_p100/DISPLAY '100' f=percent10.4 style=[background=highlight_S4_Fmt.] style(column)=[font_size=8pt]; compute NP_IND_Prob; if NP_IND_Prob = 1 then call define (_col_,"style", "style={background=lightred}"); else call define (_col_,"style", "style={background=lightgreen}"); endcomp; compute dif_p100; if dif_p10>0.05 then call define ('P10',"style", "style={background=red}"); if dif_p20>0.05 then call define ('P20',"style", "style={background=red}"); if dif_p30>0.05 then call define ('P30',"style", "style={background=red}"); if dif_p40>0.05 then call define ('P40',"style", "style={background=red}"); if dif_p50>0.05 then call define ('P50',"style", "style={background=red}"); if dif_p60>0.05 then call define ('P60',"style", "style={background=red}"); if dif_p70>0.05 then call define ('P70',"style", "style={background=red}"); if dif_p80>0.05 then call define ('P80',"style", "style={background=red}"); if dif_p90>0.05 then call define ('P90',"style", "style={background=red}"); if dif_p95>0.05 then call define ('P95',"style", "style={background=red}"); if dif_p100>0.05 then call define ('P100',"style", "style={background=red}"); endcomp; compute dif_p10; if dif_p10>0.05 then call define (_COL_,"style", "style={background=red}"); endcomp; compute dif_p20; if dif_p20>0.05 then call define (_COL_,"style", "style={background=red}"); endcomp; compute dif_p30; if dif_p30>0.05 then call define (_COL_,"style", "style={background=red}"); endcomp; compute dif_p40; if dif_p40>0.05 then call define (_COL_,"style", "style={background=red}"); endcomp; compute dif_p50; if dif_p50>0.05 then call define (_COL_,"style", "style={background=red}"); endcomp; compute dif_p60; if dif_p60>0.05 then call define (_COL_,"style", "style={background=red}"); endcomp; compute dif_p70; if dif_p70>0.05 then call define (_COL_,"style", "style={background=red}"); endcomp; compute dif_p80; if dif_p80>0.05 then call define (_COL_,"style", "style={background=red}"); endcomp; compute dif_p90; if dif_p90>0.05 then call define (_COL_,"style", "style={background=red}"); endcomp; Run; /*pelet S4-pepper*/ /*pelet S4-pepper*/ /*pelet S4-pepper*/ proc sort data=R_R.P_CrdRate_Bakara_S4; by mon; Run; Data a_pelet_Sheet_S4_P; retain P_IND_Prob mon; set R_R.P_CrdRate_Bakara_S4; dif_p10=abs(sum(P10,-LAG(p10))); dif_p20=abs(sum(P20,-LAG(p20))); dif_p30=abs(sum(P30,-LAG(p30))); dif_p40=abs(sum(P40,-LAG(p40))); dif_p50=abs(sum(P50,-LAG(p50))); dif_p60=abs(sum(P60,-LAG(p60))); dif_p70=abs(sum(P70,-LAG(p70))); dif_p80=abs(sum(P80,-LAG(p80))); dif_p90=abs(sum(P90,-LAG(p90))); dif_p95=abs(sum(P95,-LAG(p95))); dif_p100=abs(sum(P100,-LAG(p100))); If _N_=1 then do; dif_p10=.; dif_p20=.; dif_p30=.; dif_p40=.; dif_p50=.; dif_p60=.; dif_p70=.; dif_p80=.; dif_p90=.; dif_p95=.; dif_p100=.; end; Format dif_p10 dif_p20 dif_p30 dif_p40 dif_p50 dif_p60 dif_p70 dif_p80 dif_p90 dif_p95 dif_p100 percent10.5; IF abs(dif_p10)>0.05 OR abs(dif_p20)>0.05 OR abs(dif_p30)>0.05 OR abs(dif_p40)>0.05 OR abs(dif_p50)>0.05 OR abs(dif_p60)>0.05 OR abs(dif_p70)>0.05 OR abs(dif_p80)>0.05 OR abs(dif_p90)>0.05 OR abs(dif_p95)>0.05 OR abs(dif_p100)>0.05 then P_IND_Prob=1; else P_IND_Prob=0; Run; proc sort data=a_pelet_Sheet_S4_P out=P_S4; by descending mon; Run; proc sql noprint; select P_IND_Prob as Ind_P_S4_lastMon into :Ind_P_S4_lastMon trimmed from P_S4 where mon=&last_mon_in_Report_YYMM1. and mon>=2502 ; quit; %put Ind_P_S4_lastMon=&Ind_P_S4_lastMon.; proc sql noprint; select case when &Ind_P_S4_lastMon.=1 then 'Red' when max(P_IND_Prob)=1 then 'Orange' else 'Green' end as Ind_Color_Sheet_P_S4 into : Ind_Color_Sheet_P_S4 trimmed from P_S4 where mon>=2502 ; quit; %put Ind_Color_Sheet_P_S4=&Ind_Color_Sheet_P_S4.; Proc format ; value highlight_S4_Fmt 0.05<-high = 'red' ; Run; ods excel options (sheet_name='S4-P' sheet_interval="PROC" /***One table in sheet**/ absolute_column_width="12,8,8,8,8,8,8,8,8,8,8,8,8,8,8,8,8,8,8,8,8,8,8,8" row_heights='30,16,20,20,20' tab_color="&Ind_Color_Sheet_P_S4." embedded_titles='yes' embedded_footnotes='yes' gridlines = 'yes' ); title; title1 j=l bold color=red height=5 font=simplex 'אוכ-יישויות פפר בעלות ציון כא חיובי'; title2 j=l bold color=red height=5 font=simplex 'Percentiles בדיקת יציבות אחוזונים'; title3 j=l bold color=red height=5 font=simplex 'קריטריון להדלקה- קפיצה מעל 5% כלומר מעל 0.05 בערך האחוזון מחודש קודם'; title4 j=l bold color=red height=5 font=simplex 'הדלקת גליון-החל מ 2502 בגלל שזה פפר'; title5 j=l bold color=black height=5 font=simplex 'בודקים הפרש בערכי אחוזונים בכל חודש מחודש קודם'; title6 j=l bold color=black height=5 font=simplex 'כלומר בודקים דלתא בערך האחוזון מחודש לחודש קודם'; title7 bold color=black height=5 font=simplex 'לשם נוחיות הערכים מוצגים באחוזים ולא במספר עשרוני כלומר ערכים בין 0 ל 100%'; proc report data=P_S4 missing nowd style(report)={frame=box font_size=8pt bordercolor=black borderwidth=2px} ; column P_IND_Prob mon ("אחוזון_Percentiles" P10 P20 P30 P40 P50 P60 P70 P80 P90 P95 P100 ) ("הפרש_באחוזון מחודש קודם" dif_p10 dif_p20 dif_p30 dif_p40 dif_p50 dif_p60 dif_p70 dif_p80 dif_p90 dif_p95 dif_p100 ) n ; define P_IND_Prob/DISPLAY 'אינד להדלקה'; define mon/DISPLAY 'חודש' ; define P10/DISPLAY '10' f=percent10.4 style(column)=[font_size=8pt]; define P20/DISPLAY '20' f=percent10.4 style(column)=[font_size=8pt]; define P30/DISPLAY '30' f=percent10.4 style(column)=[font_size=8pt]; define P40/DISPLAY '40' f=percent10.4 style(column)=[font_size=8pt]; define P50/DISPLAY '50' f=percent10.4 style(column)=[font_size=8pt]; define P60/DISPLAY '60' f=percent10.4 style(column)=[font_size=8pt]; define P70/DISPLAY '70' f=percent10.4 style(column)=[font_size=8pt]; define P80/DISPLAY '80' f=percent10.4 style(column)=[font_size=8pt]; define P90/DISPLAY '90' f=percent10.4 style(column)=[font_size=8pt]; define P95/DISPLAY '95' f=percent10.4 style(column)=[font_size=8pt]; define P100/DISPLAY '100' f=percent10.4 style(column)=[font_size=8pt]; define dif_p10/DISPLAY '10' f=percent10.4 style=[background=highlight_S4_Fmt.] style(column)=[font_size=8pt]; define dif_p20/DISPLAY '20' f=percent10.4 style=[background=highlight_S4_Fmt.] style(column)=[font_size=8pt]; define dif_p30/DISPLAY '30' f=percent10.4 style=[background=highlight_S4_Fmt.] style(column)=[font_size=8pt] ; define dif_p40/DISPLAY '40' f=percent10.4 style=[background=highlight_S4_Fmt.] style(column)=[font_size=8pt]; define dif_p50/DISPLAY '50' f=percent10.4 style=[background=highlight_S4_Fmt.] style(column)=[font_size=8pt]; define dif_p60/DISPLAY '60' f=percent10.4 style=[background=highlight_S4_Fmt.] style(column)=[font_size=8pt]; define dif_p70/DISPLAY '70' f=percent10.4 style=[background=highlight_S4_Fmt.] style(column)=[font_size=8pt]; define dif_p80/DISPLAY '80' f=percent10.4 style=[background=highlight_S4_Fmt.] style(column)=[font_size=8pt]; define dif_p90/DISPLAY '90' f=percent10.4 style=[background=highlight_S4_Fmt.] style(column)=[font_size=8pt]; define dif_p95/DISPLAY '95' f=percent10.4 style=[background=highlight_S4_Fmt.] style(column)=[font_size=8pt]; define dif_p100/DISPLAY '100' f=percent10.4 style=[background=highlight_S4_Fmt.] style(column)=[font_size=8pt]; compute P_IND_Prob; if P_IND_Prob = 1 then call define (_col_,"style", "style={background=lightred}"); else call define (_col_,"style", "style={background=lightgreen}"); endcomp; compute dif_p100; if dif_p10>0.05 then call define ('P10',"style", "style={background=red}"); if dif_p20>0.05 then call define ('P20',"style", "style={background=red}"); if dif_p30>0.05 then call define ('P30',"style", "style={background=red}"); if dif_p40>0.05 then call define ('P40',"style", "style={background=red}"); if dif_p50>0.05 then call define ('P50',"style", "style={background=red}"); if dif_p60>0.05 then call define ('P60',"style", "style={background=red}"); if dif_p70>0.05 then call define ('P70',"style", "style={background=red}"); if dif_p80>0.05 then call define ('P80',"style", "style={background=red}"); if dif_p90>0.05 then call define ('P90',"style", "style={background=red}"); if dif_p95>0.05 then call define ('P95',"style", "style={background=red}"); if dif_p100>0.05 then call define ('P100',"style", "style={background=red}"); endcomp; compute dif_p10; if dif_p10>0.05 then call define (_COL_,"style", "style={background=red}"); endcomp; compute dif_p20; if dif_p20>0.05 then call define (_COL_,"style", "style={background=red}"); endcomp; compute dif_p30; if dif_p30>0.05 then call define (_COL_,"style", "style={background=red}"); endcomp; compute dif_p40; if dif_p40>0.05 then call define (_COL_,"style", "style={background=red}"); endcomp; compute dif_p50; if dif_p50>0.05 then call define (_COL_,"style", "style={background=red}"); endcomp; compute dif_p60; if dif_p60>0.05 then call define (_COL_,"style", "style={background=red}"); endcomp; compute dif_p70; if dif_p70>0.05 then call define (_COL_,"style", "style={background=red}"); endcomp; compute dif_p80; if dif_p80>0.05 then call define (_COL_,"style", "style={background=red}"); endcomp; compute dif_p90; if dif_p90>0.05 then call define (_COL_,"style", "style={background=red}"); endcomp; Run; /***Pelet S5--Here no have crietria for problem***/ /***Pelet S5--Here no have crietria for problem***/ /***Pelet S5--Here no have crietria for problem***/ proc sort data=r_r.CrdRate_Bakara_S5 out=S5; by descending mon; Run; proc format; value $hdfmt 'לא_פפר'='cyan' 'פפר'='cyan' ; Run; ods excel options (sheet_name='S5' sheet_interval="PROC" /***One table in sheet**/ absolute_column_width="13,25,15,15,15,15,15,15,15,15" row_heights='15' tab_color='grey' embedded_titles='yes' embedded_footnotes='yes' gridlines = 'yes' ); title; title1 j=l bold color=red height=5 font=simplex 'אוכלוסייה--כלל יישויות קלין'; title2 j=l bold color=red height=5 font=simplex 'בקרת אחוז יישויות בכשל 12 חודשים קדימה'; title3 j=l bold color=red height=5 font=simplex 'לא הוגדר קריטריון להדלקה'; title4 j=l bold color=black height=4 font=simplex 'זוהי איננה בקרה ישירה לנושא ציון כא'; title5 j=l bold color=black height=4 font=simplex 'אם למשל מפורסם סקור 2507 אז מתעדכן קלין של 2407'; proc report data=S5 missing nowd split='*' /*STYLE(Report) = {frame=box bordercolor = grey borderwidth=2px}*/ /*STYLE(header) = HEADER{background=verylightgrey fontsize=2}*/ style(report)={frame=box font_size=8pt bordercolor=black borderwidth=2px} style(header)={font_weight=bold borderwidth=0 borderspacing=0 font_size=9pt background=$hdfmt. }; ; column mon ("לא_פפר" NP_Nr_Y NP_nr_KESHELFUTURE NP_nr_NoKESHELFUTURE NP_PCT_KESHELFUTURE) ("פפר" P_Nr_Y P_nr_KESHELFUTURE P_nr_NoKESHELFUTURE P_PCT_KESHELFUTURE) ; define mon/Display "חודש" style(header)=[background=lightblue] ; define NP_Nr_Y/Display style(header)=[background=lightblue] 'מספר יישויות' f=comma32. ; define NP_nr_KESHELFUTURE/Display style(header)=[background=lightblue] 'מספר יישויות * בכשל' f=comma32. ; define NP_nr_NoKESHELFUTURE/Display style(header)=[background=lightblue] 'מספר יישויות * לא בכשל' f=comma32. ; define NP_PCT_KESHELFUTURE/Display style(header)=[background=lightblue] style(header)={font_weight=bold color=red} STYLE(column)=[just=c width=.6in] 'אחוז בכשל ' ; define P_Nr_Y/Display style(header)=[background=lightblue] 'מספר יישויות '; define P_nr_KESHELFUTURE/Display style(header)=[background=lightblue] 'מספר יישויות * בכשל ' f=comma32. ; define P_nr_NoKESHELFUTURE/Display style(header)=[background=lightblue] 'מספר יישויות * לא בכשל' f=comma32. ; define P_PCT_KESHELFUTURE/Display style(header)=[background=lightblue] style(header)={font_weight=bold color=red} STYLE(column)=[just=c width=.6in] 'אחוז בכשל' ; COMPUTE NP_PCT_KESHELFUTURE; IF _col_ = 6 THEN DO; CALL DEFINE(_col_,'style','style={background=verylightyellow}'); END; ENDCOMP; COMPUTE P_PCT_KESHELFUTURE; IF _col_ = 10 THEN DO; CALL DEFINE(_col_,'style','style={background=verylightyellow}'); END; ENDCOMP; Run; /****Pelet S6---No pepper***/ /****Pelet S6---No pepper***/ /****Pelet S6---No pepper***/ proc sort data=R_R.NP_CrdRate_Bakara_S6 out=NP_S6; by descending mon _cat_; Run; Data NP_S6_Last_mon; set NP_S6(Where=(mon=&YYMM2.)); Run; proc sql noprint; select max(case when Ind_prob_Ordinal='1' then 1 else 0 end ) as Ind_NP_S6_lastMon into :Ind_NP_S6_lastMon trimmed from NP_S6_Last_mon ; quit; %put Ind_NP_S6_lastMon=&Ind_NP_S6_lastMon.; proc sql noprint; select case when &Ind_NP_S6_lastMon.=1 then 'Red' when max(case when Ind_prob_Ordinal='1' then 1 else 0 end )=1 then 'Orange' else 'Green' end as Ind_Color_Sheet_NP_S6 into : Ind_Color_Sheet_NP_S6 trimmed from NP_S6 ; quit; %put Ind_Color_Sheet_NP_S6=&Ind_Color_Sheet_NP_S6.; ods excel options(sheet_name="S6A_NP" embedded_titles='yes' /*sheet_interval="proc" */ sheet_interval="none" /*Multiple tables in sheet**/ tab_color="&Ind_Color_Sheet_NP_S6." embedded_footnotes='yes' gridlines = 'yes' ); /**Here multiple reports in same sheet***/ title; title1 j=l bold color=red height=4 font=simplex 'בדיקה אורדינליות באחוז כשל בקבוצת ציון כא'; title2 j=l bold color=red height=4 font=simplex 'ניתוח עבור חודש אחרון בלבד'; title3 j=l bold color=black height=4 font=simplex 'אוכ-יישויות נקיות שאינן פפר'; title4 j=l color=black height=3 font=simplex 'בדיקת אורדינליות נעשית ללא קטגוריית ערכים ריקים/0'; title5 j=l color=black height=3 font=simplex 'הערה:חלוקת ציון כא לקבוצות '; title6 j=l color=black height=3 font=simplex '0-10/ 10-20/ 20-30/ 30-40/ 40-50/ 50-60/ 60-70/ 70-80/ 80-90/ 90-100/0-Null'; proc report data=NP_S6_Last_mon; column Ind_prob_Ordinal mon CAT NP_nr NP_nr_Ra NP_PCT_ra ; define Ind_prob_Ordinal/Display style(header)=[background=lightblue] 'אינד להדלקה'; define mon/Display style(header)=[background=lightblue] 'חודש' ; define CAT/Display style(header)=[background=lightblue] 'קטגוריית ציון כא' ; define NP_nr/Display style(header)=[background=lightblue] 'מספר יישויות' ; define NP_nr_Ra/Display style(header)=[background=lightblue] 'מספר יישויות בכשל' ; define NP_PCT_ra/Display style(header)=[background=lightblue] 'אחוז כשל' style(column)=[backgroundcolor=lightgrey] style(header)=[color=red]; compute Ind_prob_Ordinal; IF Ind_prob_Ordinal=1 then call define(_col_,"Style","style={background=RED}"); else call define(_col_,"Style","style={background=LIGHTGREEN}"); endcomp; Run; title; title1 'פלט שכולל את כלל החודשים'; proc report data=NP_S6; column Ind_prob_Ordinal mon CAT NP_nr NP_nr_Ra NP_PCT_ra ; define Ind_prob_Ordinal/Display style(header)=[background=lightblue] 'אינד להדלקה'; define mon/Display style(header)=[background=lightblue] 'חודש' ; define CAT/Display style(header)=[background=lightblue] 'קטגוריית*ציון כא' ; define NP_nr/Display style(header)=[background=lightblue] 'מספר יישויות' ; define NP_nr_Ra/Display style(header)=[background=lightblue] 'מספר יישויות בכשל' ; define NP_PCT_ra/Display style(header)=[background=lightblue] 'אחוז כשל' style(column)=[backgroundcolor=lightgrey] style(header)=[color=red]; compute Ind_prob_Ordinal; IF Ind_prob_Ordinal=1 then call define(_col_,"Style","style={background=RED}"); else call define(_col_,"Style","style={background=LIGHTGREEN}"); endcomp; Run; /*************S6B_NP*********************/ /*************S6B_NP*********************/ /*************S6B_NP*********************/ /*************S6B_NP*********************/ proc sort data=R_R.NP_CrdRate_Bakara_S6(DROP=Ind_prob_Ordinal) out=NP_S6b; by _cat_ mon; Run; data NP_S6b_2; set NP_S6b; by _cat_; dif_Tamhil_nr=NP_Tamhil_nr-LAG(NP_Tamhil_nr); dif_Tamhil_ra=NP_Tamhil_ra-Lag(NP_Tamhil_ra); dif_PCT_ra=NP_PCT_ra-Lag(NP_PCT_ra); if first._cat_ then do; dif_Tamhil_nr=.; dif_Tamhil_ra=.; dif_PCT_ra=.; end; format dif_Tamhil_nr dif_Tamhil_ra dif_PCT_ra percent10.2; if abs(dif_Tamhil_nr)>0.03 or abs(dif_Tamhil_ra)>0.03 or abs(dif_PCT_ra)>0.03 then NP_IND_Prob=1;else NP_IND_Prob=0; Run; data NP_S6b_3; retain NP_IND_Prob mon; set NP_S6b_2; Run; proc sort data=NP_S6b_3; by descending mon _CAT_; Run; proc sql noprint; select max(NP_IND_Prob) as Ind_NP_S6b into :Ind_NP_S6b trimmed from NP_S6b_3 ; quit; %put Ind_NP_S6b=&Ind_NP_S6b.; proc sql noprint; select max(NP_IND_Prob) as Ind_NP_S6b_Lastmon into :Ind_NP_S6b_Lastmon trimmed from NP_S6b_3 where mon=&YYMM2. ; quit; %put Ind_NP_S6b_Lastmon=&Ind_NP_S6b_Lastmon.; proc sql noprint; select case when &Ind_NP_S6b_Lastmon.=1 then 'Red' when max(NP_IND_Prob)=1 then 'Orange' else 'Green' end as Ind_Color_Sheet_NP_S6b into : Ind_Color_Sheet_NP_S6b trimmed from NP_S6b_3 ; quit; %put Ind_Color_Sheet_NP_S6b=&Ind_Color_Sheet_NP_S6b.; ods excel options(sheet_name="S6B_NP" embedded_titles='yes' sheet_interval="proc" /**one table in sheet**/ tab_color="&Ind_Color_Sheet_NP_S6b." ); title; title2 j=l bold color=red height=4 font=simplex 'אוכ-יישויות נקיות שאינן פפר'; title3 j=l bold color=red height=4 font=simplex 'בדיקה2-בדיקת יציבות במספר יישויות נקיות, מספר יישויות בכשל'; title4 j=l bold color=red height=4 font=simplex 'הערה: קריטריון להדלקה- שינוי מעל 3% בתמהיל יישויות/תמהיל יישויות בכשל/אחוז כשל מחודש לחודש'; title5 j=l bold color=red height=4 font=simplex'הערה- בודקים שינויים בתמהיל בין חודש לחודש קודם'; proc report data=NP_S6b_3 missing nowd split='*' style(report)={frame=box font_size=8pt bordercolor=black borderwidth=2px} style(header)={font_weight=bold borderwidth=0 borderspacing=0 font_size=8pt background=$hdfmt. }; ; column NP_IND_Prob mon CAT _CAT_ NP_nr NP_nr_Ra NP_PCT_ra NP_Tamhil_nr NP_Tamhil_ra dif_Tamhil_nr dif_Tamhil_ra dif_PCT_ra ; define NP_IND_Prob/Display style(header)=[background=lightblue] 'אינד להדלקה'; define mon/Display style(header)=[background=lightblue] 'חודש' ; define CAT/Display style(header)=[background=lightblue] 'ציון כא' ; define _CAT_/Display style(header)=[background=lightblue] 'סדר קטגוריית'; define NP_nr/Display style(header)=[background=lightblue] 'מספר יישויות' ; define NP_nr_Ra/Display style(header)=[background=lightblue] ' יישויות בכשל' ; define NP_PCT_ra/Display style(header)=[background=lightblue] 'אחוז בכשל' ; define NP_Tamhil_nr/Display style(header)=[background=lightblue] 'תמהיל יישויות' ; define NP_Tamhil_ra/Display style(header)=[background=lightblue] 'תמהיל בכשל' ; define dif_Tamhil_nr/Display style(header)=[background=lightblue] 'הפרש תמהיל יישויות' ; define dif_Tamhil_ra/Display style(header)=[background=lightblue] 'הפרש תמהיל כשל' ; define dif_PCT_ra/Display style(header)=[background=lightblue] 'הפרש אחוז כשל' ; compute NP_IND_Prob; IF NP_IND_Prob=1 then call define(_col_,"Style","style={background=RED}"); else call define(_col_,"Style","style={background=LIGHTGREEN}"); endcomp; Run; /***Pelet S6--pepper***/ /***Pelet S6--pepper***/ /***Pelet S6--pepper***/ proc sort data=R_R.P_CrdRate_Bakara_S6 out=P_S6; by descending mon _cat_; Run; Data P_S6_Last_mon; retain Ind_prob_Ordinal mon; set P_S6(Where=(mon=&YYMM2.)); Run; proc sql noprint; select max(case when Ind_prob_Ordinal='1' then 1 else 0 end ) as Ind_P_S6_lastMon into :Ind_P_S6_lastMon trimmed from P_S6_Last_mon ; quit; %put Ind_P_S6_lastMon=&Ind_P_S6_lastMon.; proc sql noprint; select case when &Ind_P_S6_lastMon.=1 then 'Red' when max(case when Ind_prob_Ordinal='1' then 1 else 0 end )=1 then 'Orange' else 'Green' end as Ind_Color_Sheet_P_S6 into : Ind_Color_Sheet_P_S6 trimmed from P_S6 ; quit; %put Ind_Color_Sheet_P_S6=&Ind_Color_Sheet_P_S6.; ods excel options(sheet_name="S6A_P" embedded_titles='yes' sheet_interval="none" /*Multiple tables in sheet**/ tab_color="&Ind_Color_Sheet_P_S6." embedded_titles='yes' embedded_footnotes='yes' gridlines = 'yes' ); /**Here multiple reports in same sheet***/ title; title1 j=l bold color=red height=4 font=simplex 'בדיקה אורדינליות באחוז כשל בקבוצת ציון כא עבור חודש אחרון בלבד'; title2 j=l bold color=black height=4 font=simplex 'אוכ-יישויות נקיות פפר'; title3 j=l color=black height=3 font=simplex 'בדיקת אורדינליות נעשית ללא קטגוריית ערכים ריקים'; title4 j=l color=black height=3 font=simplex 'הערה:חלוקת ציון כא לקבוצות '; title5 j=l color=black height=3 font=simplex '0-10/ 10-20/ 20-30/ 30-40/ 40-50/ 50-60/ 60-70/ 70-80/ 80-90/ 90-100/0-Null'; proc report data=P_S6_Last_mon; column Ind_prob_Ordinal mon CAT P_nr P_nr_Ra P_PCT_ra ; define Ind_prob_Ordinal/Display style(header)=[background=lightblue] 'אינד להדלקה'; define mon/Display style(header)=[background=lightblue] 'חודש' ; define CAT/Display style(header)=[background=lightblue] 'קטגוריית*ציון כא' ; define P_nr/Display style(header)=[background=lightblue] 'מספר יישויות' ; define P_nr_Ra/Display style(header)=[background=lightblue] 'מספר יישויות בכשל' ; define P_PCT_ra/Display style(header)=[background=lightblue] 'אחוז כשל' style(column)=[backgroundcolor=lightgrey] style(header)=[color=red]; compute Ind_prob_Ordinal; IF Ind_prob_Ordinal=1 then call define(_col_,"Style","style={background=RED}"); else call define(_col_,"Style","style={background=LIGHTGREEN}"); endcomp; Run; title; title1 'פלט שכולל את כלל החודשים'; proc report data=P_S6; column Ind_prob_Ordinal mon CAT P_nr P_nr_Ra P_PCT_ra ; define Ind_prob_Ordinal/Display style(header)=[background=lightblue] 'אינד להדלקה'; define mon/Display style(header)=[background=lightblue] 'חודש' ; define CAT/Display style(header)=[background=lightblue] 'קטגוריית*ציון כא' ; define P_nr/Display style(header)=[background=lightblue] 'מספר יישויות' ; define P_nr_Ra/Display style(header)=[background=lightblue] 'מספר יישויות בכשל' ; define P_PCT_ra/Display style(header)=[background=lightblue] 'אחוז כשל' style(column)=[backgroundcolor=lightgrey] style(header)=[color=red]; compute Ind_prob_Ordinal; IF Ind_prob_Ordinal=1 then call define(_col_,"Style","style={background=RED}"); else call define(_col_,"Style","style={background=LIGHTGREEN}"); endcomp; Run; /*************S6B_P*********************/ /*************S6B_P*********************/ /*************S6B_P*********************/ proc sort data=R_R.P_CrdRate_Bakara_S6(DROP=Ind_prob_Ordinal) out=P_S6b; by _cat_ mon; Run; data P_S6b_2; set P_S6b; by _cat_; dif_Tamhil_nr=P_Tamhil_nr-LAG(P_Tamhil_nr); dif_Tamhil_ra=P_Tamhil_ra-Lag(P_Tamhil_ra); dif_PCT_ra=P_PCT_ra-Lag(P_PCT_ra); if first._cat_ then do; dif_Tamhil_nr=.; dif_Tamhil_ra=.; dif_PCT_ra=.; end; format dif_Tamhil_nr dif_Tamhil_ra dif_PCT_ra percent10.2; if abs(dif_Tamhil_nr)>0.03 or abs(dif_Tamhil_ra)>0.03 or abs(dif_PCT_ra)>0.03 then P_IND_Prob=1;else P_IND_Prob=0; Run; data P_S6b_3; retain P_IND_Prob mon; set P_S6b_2; Run; proc sort data=P_S6b_3; by descending mon _CAT_; Run; proc sql noprint; select max(P_IND_Prob) as Ind_P_S6b into :Ind_P_S6b trimmed from P_S6b_3 ; quit; %put Ind_P_S6b=&Ind_P_S6b.; proc sql noprint; select max(P_IND_Prob) as Ind_P_S6b_Lastmon into :Ind_P_S6b_Lastmon trimmed from P_S6b_3 where mon=&YYMM2. ; quit; %put Ind_P_S6b_Lastmon=&Ind_P_S6b_Lastmon.; proc sql noprint; select case when &Ind_P_S6b_Lastmon.=1 then 'Red' when max(P_IND_Prob)=1 then 'Orange' else 'Green' end as Ind_Color_Sheet_P_S6b into : Ind_Color_Sheet_P_S6b trimmed from P_S6b_3 ; quit; %put Ind_Color_Sheet_P_S6b=&Ind_Color_Sheet_P_S6b.; ods excel options(sheet_name="S6B_P" embedded_titles='yes' sheet_interval="PROC" /***One table in sheet**/ tab_color="&Ind_Color_Sheet_P_S6b." embedded_titles='yes' embedded_footnotes='yes' gridlines = 'yes' ); title; title2 j=l bold color=red height=4 font=simplex 'אוכ-יישויות נקיות פפר'; title3 j=l bold color=red height=4 font=simplex 'בדיקה2-בדיקת יציבות במספר יישויות נקיות, מספר יישויות בכשל'; title4 j=l bold color=red height=4 font=simplex 'הערה: קירטריון להדלקה שינוי של מעל 3% בתמהיל יישויות/תמהיל יישויות בכשל/אחוז כשל מחודש לחודש'; title5 j=l bold color=red height=4 font=simplex'הערה- בודקים שינויים בתמהיל בין חודש לחודש קודם'; proc report data=P_S6b_3 missing nowd split='*' style(report)={frame=box font_size=8pt bordercolor=black borderwidth=2px} style(header)={font_weight=bold borderwidth=0 borderspacing=0 font_size=8pt background=$hdfmt. }; ; column P_IND_Prob mon CAT _CAT_ P_nr P_nr_Ra P_PCT_ra P_Tamhil_nr P_Tamhil_ra dif_Tamhil_nr dif_Tamhil_ra dif_PCT_ra ; define P_IND_Prob/Display style(header)=[background=lightblue] 'אינד להדלקה'; define mon/Display style(header)=[background=lightblue] 'חודש' ; define CAT/Display style(header)=[background=lightblue] 'ציון כא' ; define _CAT_/Display style(header)=[background=lightblue] 'סדר קטגוריית'; define P_nr/Display style(header)=[background=lightblue] 'מספר יישויות' ; define P_nr_Ra/Display style(header)=[background=lightblue] ' יישויות בכשל' ; define P_PCT_ra/Display style(header)=[background=lightblue] 'אחוז בכשל' ; define P_Tamhil_nr/Display style(header)=[background=lightblue] 'תמהיל יישויות' ; define P_Tamhil_ra/Display style(header)=[background=lightblue] 'תמהיל בכשל' ; define dif_Tamhil_nr/Display style(header)=[background=lightblue] 'הפרש תמהיל יישויות' ; define dif_Tamhil_ra/Display style(header)=[background=lightblue] 'הפרש תמהיל כשל' ; define dif_PCT_ra/Display style(header)=[background=lightblue] 'הפרש אחוז כשל' ; compute P_IND_Prob; IF P_IND_Prob=1 then call define(_col_,"Style","style={background=RED}"); else call define(_col_,"Style","style={background=LIGHTGREEN}"); endcomp; Run; /****Pelet S7---No pepper--Last Month***/ /****Pelet S7---No pepper--Last Month***/ /****Pelet S7---No pepper--Last Month***/ /**Dummy code will not print anyting**/ /***We must put it between 2 none***/ data R_R.NP_CrdRate_Mavarim_S7; set R_R.NP_CrdRate_Mavarim_S7; IF mon=2201 then delete; /**Becuase first month so we didnt check one month before so mavarim are not good to show***/ Run; proc sort data=R_R.NP_CrdRate_Mavarim_S7 out=NP_S7_a; by combine mon; Run; data NP_S7_b; set NP_S7_a; by combine; NP_Dif_Tamhil_nr=NP_tamhil_nr-LAG(NP_tamhil_nr); format NP_Dif_Tamhil_nr percent10.4; if first.combine then NP_Dif_Tamhil_nr=.; Run; data NP_S7_c; set NP_S7_b; IF abs(NP_Dif_Tamhil_nr)>0.015 then NP_IND_Prob=1; else NP_IND_Prob=0; Run; /*קריטריון להדלקה- שינוי בתמהיל במעל 1.5%*/ proc sort data=NP_S7_c; by descending mon _CAT_ _PREVCAT_; Run; Data NP_Want_Mavarim_S7; length Ind_Same_Better_Worse $20.; set NP_S7_c; IF _PREVCAT_=0 then Ind_Same_Better_Worse='NO_EXIST_PREV_MON'; else IF _PREVCAT_=1 AND _CAT_=1 then Ind_Same_Better_Worse='NULL_TO_NULL'; else IF _PREVCAT_=1 AND _CAT_ ne 1 then Ind_Same_Better_Worse='NULL_TO_POS'; else IF _PREVCAT_ ne 1 AND _CAT_ = 1 then Ind_Same_Better_Worse='POS_TO_NULL'; else IF _CAT_=_PREVCAT_ then Ind_Same_Better_Worse='SAME'; else IF _CAT_>_PREVCAT_ then Ind_Same_Better_Worse='WORSE'; else IF _CAT_<_PREVCAT_ then Ind_Same_Better_Worse='BETTER'; IF _PREVCAT_ not in (0,1) then dif_Categories=abs(sum(_CAT_,-_PREVCAT_)); Run; proc sort data= NP_Want_Mavarim_S7; by descending mon; Run; proc sql noprint; select MAX(NP_IND_Prob) as Ind_NP_S7_lastMon into :Ind_NP_S7_lastMon TRIMMED from NP_Want_Mavarim_S7 where mon=&last_mon_in_Report_YYMM1. ; quit; %put Ind_NP_S7_lastMon=&Ind_NP_S7_lastMon.; proc sql noprint; select case when &Ind_NP_S7_lastMon.=1 then 'Red' when max(NP_IND_Prob)=1 then 'Orange' else 'Green' end as Ind_Color_Sheet_NP_S7 into : Ind_Color_Sheet_NP_S7 TRIMMED from NP_Want_Mavarim_S7 ; quit; %put Ind_Color_Sheet_NP_S7=&Ind_Color_Sheet_NP_S7.; data last_mon_Mavarim_NP; retain Ind_Same_Better_Worse mon Combine PREV_TO_CUR cat _CAT_ PREVCAT _PREVCAT_ dif_Categories NP_nr NP_tamhil_nr NP_Dif_Tamhil_nr NP_IND_Prob dif_Categories; set NP_Want_Mavarim_S7(Where=(mon in (&YYMM1.))); if cat='1_NULL/0' AND PREVCAT='' THEN dif_Categories=0; PREV_TO_CUR=catx(',',PREVCAT,cat); run; proc sort data=last_mon_Mavarim_NP(keep=mon PREV_TO_CUR NP_nr NP_tamhil_nr NP_Dif_Tamhil_nr NP_IND_Prob) OUT=NP_S7_PELET1; by descending NP_IND_Prob PREV_TO_CUR; Run; ods select none; ods excel options( sheet_interval="proc"); proc print data=sashelp.class;run; ods select all; ods excel options(sheet_name="S7_NP_LastMon_Matrices" embedded_titles='yes' sheet_interval="none" /**Multiple tables in sheet***/ tab_color="&Ind_Color_Sheet_NP_S7." ); title; title1 j=l bold color=red height=5 font=simplex 'מטריצת מעברים עבור חודש אחרון'; title2 j=l bold color=black height=5 font=simplex 'בחישוב התמהיל לא לוקחים בחשבון מעבר מקטגוריה ריק לריק'; title3 j=l bold color=black height=5 font=simplex 'קריטריון להדלקה- שינוי בתמהיל במעל 1.5%'; proc tabulate data=last_mon_Mavarim_NP missing; by descending mon ; class PREVCAT CAT /order=data ; var NP_IND_Prob; table PREVCAT='' all, (CAT='נוכחי' all)*(SUM=''*NP_IND_Prob='אינד הדלקה'*f=8.0) / rts=10 misstext='0' box='קודם'; run; title; title j=l bold color=red height=5 font=simplex 'מספר יישויות'; proc tabulate data=last_mon_Mavarim_NP missing; by descending mon ; class PREVCAT CAT /order=data ; var NP_nr; table PREVCAT='' all, (CAT='נוכחי' all)*(SUM=''*NP_nr='מספר_יישויות'*f=comma32.) / rts=10 misstext='0' box='קודם'; run; title; title j=l bold color=red height=5 font=simplex 'תמהיל'; proc tabulate data=last_mon_Mavarim_NP missing; by descending mon ; class PREVCAT CAT /order=data ; var NP_tamhil_nr; table PREVCAT='' all, (CAT='נוכחי' all)*(SUM=''*NP_tamhil_nr='תמהיל'*f=percent10.2) / rts=10 misstext='0' box='קודם'; run; title; title j=l bold color=red height=5 font=simplex 'הפרש תמהיל מחודש שעבר'; proc tabulate data=last_mon_Mavarim_NP missing; by descending mon ; class PREVCAT CAT /order=data ; var NP_Dif_Tamhil_nr; table PREVCAT='' all, (CAT='נוכחי' all)*(SUM=''*NP_Dif_Tamhil_nr='הפרש_תמהיל'*f=percent10.2) / rts=10 misstext='0' box='קודם'; run; ods excel options(sheet_name="S7_NP_LAST_MON_Table" embedded_titles='yes' sheet_interval="proc" /**one table in sheet**/ tab_color="&Ind_Color_Sheet_P_S7." ); title; title1 j=l bold color=red height=7 font=simplex 'הצגת מעברים רק של חודש אחרון'; title2 j=l bold color=black height=5 font=simplex 'אוכ-יישויות 833 לא מקושרים שאינן פפר'; title3 j=l bold color=red height=5 font=simplex 'הקבוצות אינן דירוגים אלה קבוצות ציון כא'; proc report data=NP_S7_PELET1; column NP_IND_Prob mon PREV_TO_CUR NP_nr NP_tamhil_nr NP_Dif_Tamhil_nr ; define NP_IND_prob/Display style(header)=[background=lightblue] 'אינד להדלקה' ; define mon/Display style(header)=[background=lightblue] 'חודש' ; define prev_to_cur/Display style(header)=[background=lightblue] 'חודש_קודם+חודש_נוכחי' ; define np_nr/Display style(header)=[background=lightblue] 'ש' ; define np_tamhil_nr/Display style(header)=[background=lightblue] 'חודש' ; define np_dif_tamhil_nr/Display style(header)=[background=lightblue] 'חודש' ; compute NP_IND_Prob; IF NP_IND_Prob=1 then call define(_col_,"Style","style={background=RED}"); else call define(_col_,"Style","style={background=LIGHTGREEN}"); endcomp; Run; /****Pelet S7--- pepper--Last Month***/ /****Pelet S7--- pepper--Last Month***/ /****Pelet S7--- pepper--Last Month***/ /**Dummy code will not print anyting**/ /***We must put it between 2 none***/ data R_R.P_CrdRate_Mavarim_S7; set R_R.P_CrdRate_Mavarim_S7; IF mon=2201 then delete; /**Becuase first month so we didnt check one month before so mavarim are not good to show***/ Run; proc sort data=R_R.P_CrdRate_Mavarim_S7 out=P_S7_a; by combine mon; Run; data P_S7_b; set P_S7_a; by combine; P_Dif_Tamhil_nr=P_tamhil_nr-LAG(P_tamhil_nr); format P_Dif_Tamhil_nr percent10.4; if first.combine then P_Dif_Tamhil_nr=.; Run; data P_S7_c; set P_S7_b; IF abs(P_Dif_Tamhil_nr)>0.015 then P_IND_Prob=1; else P_IND_Prob=0; Run; /*קריטריון להדלקה- שינוי בתמהיל במעל 1.5%*/ proc sort data=P_S7_c; by descending mon _CAT_ _PREVCAT_; Run; Data P_Want_Mavarim_S7; length Ind_Same_Better_Worse $20.; set P_S7_c; IF _PREVCAT_=0 then Ind_Same_Better_Worse='NO_EXIST_PREV_MON'; else IF _PREVCAT_=1 AND _CAT_=1 then Ind_Same_Better_Worse='NULL_TO_NULL'; else IF _PREVCAT_=1 AND _CAT_ ne 1 then Ind_Same_Better_Worse='NULL_TO_POS'; else IF _PREVCAT_ ne 1 AND _CAT_ = 1 then Ind_Same_Better_Worse='POS_TO_NULL'; else IF _CAT_=_PREVCAT_ then Ind_Same_Better_Worse='SAME'; else IF _CAT_>_PREVCAT_ then Ind_Same_Better_Worse='WORSE'; else IF _CAT_<_PREVCAT_ then Ind_Same_Better_Worse='BETTER'; IF _PREVCAT_ not in (0,1) then dif_Categories=abs(sum(_CAT_,-_PREVCAT_)); Run; proc sort data= P_Want_Mavarim_S7; by descending mon; Run; proc sql noprint; select MAX(P_IND_Prob) as Ind_P_S7_lastMon into :Ind_P_S7_lastMon TRIMMED from P_Want_Mavarim_S7 where mon=&last_mon_in_Report_YYMM1. ; quit; %put Ind_P_S7_lastMon=&Ind_P_S7_lastMon.; proc sql noprint; select case when &Ind_P_S7_lastMon.=1 then 'Red' when max(P_IND_Prob)=1 then 'Orange' else 'Green' end as Ind_Color_Sheet_P_S7 into : Ind_Color_Sheet_P_S7 TRIMMED from P_Want_Mavarim_S7 ; quit; %put Ind_Color_Sheet_P_S7=&Ind_Color_Sheet_P_S7.; data last_mon_Mavarim_P; retain Ind_Same_Better_Worse mon Combine PREV_TO_CUR cat _CAT_ PREVCAT _PREVCAT_ dif_Categories P_nr P_tamhil_nr P_Dif_Tamhil_nr P_IND_Prob dif_Categories; set P_Want_Mavarim_S7(Where=(mon in (&YYMM1.))); if cat='1_NULL/0' AND PREVCAT='' THEN dif_Categories=0; PREV_TO_CUR=catx(',',PREVCAT,cat); run; proc sort data=last_mon_Mavarim_P(keep=mon PREV_TO_CUR P_nr P_tamhil_nr P_Dif_Tamhil_nr P_IND_Prob) OUT=P_S7_PELET1; by descending P_IND_Prob PREV_TO_CUR; Run; ods select none; ods excel options( sheet_interval="proc"); proc print data=sashelp.class;run; ods select all; ods excel options(sheet_name="S7_P_LAST_MON_MATRICES" embedded_titles='yes' sheet_interval="none" /**Multiple tables in sheet***/ tab_color="&Ind_Color_Sheet_P_S7." ); title; title1 j=l bold color=red height=5 font=simplex 'מטריצת מעברים עבור חודש אחרון'; title2 j=l bold color=black height=5 font=simplex 'בחישוב התמהיל לא לוקחים בחשבון מעבר מקטגוריה ריק לריק'; title3 j=l bold color=black height=5 font=simplex 'קריטריון להדלקה- שינוי בתמהיל במעל 1.5%'; proc tabulate data=last_mon_Mavarim_P missing; by descending mon ; class PREVCAT CAT /order=data ; var P_IND_Prob; table PREVCAT='' all, (CAT='נוכחי' all)*(SUM=''*P_IND_Prob='אינד הדלקה'*f=8.0) / rts=10 misstext='0' box='קודם'; run; title; title j=l bold color=red height=5 font=simplex 'מספר יישויות'; proc tabulate data=last_mon_Mavarim_P missing; by descending mon ; class PREVCAT CAT /order=data ; var P_nr; table PREVCAT='' all, (CAT='נוכחי' all)*(SUM=''*P_nr='מספר_יישויות'*f=comma32.) / rts=10 misstext='0' box='קודם'; run; title; title j=l bold color=red height=5 font=simplex 'תמהיל'; proc tabulate data=last_mon_Mavarim_P missing; by descending mon ; class PREVCAT CAT /order=data ; var P_tamhil_nr; table PREVCAT='' all, (CAT='נוכחי' all)*(SUM=''*P_tamhil_nr='תמהיל'*f=percent10.2) / rts=10 misstext='0' box='קודם'; run; title; title j=l bold color=red height=5 font=simplex 'הפרש תמהיל מחודש שעבר'; proc tabulate data=last_mon_Mavarim_P missing; by descending mon ; class PREVCAT CAT /order=data ; var P_Dif_Tamhil_nr; table PREVCAT='' all, (CAT='נוכחי' all)*(SUM=''*P_Dif_Tamhil_nr='הפרש_תמהיל'*f=percent10.2) / rts=10 misstext='0' box='קודם'; run; ods excel options(sheet_name="S7_P_LAST_MON_Table" embedded_titles='yes' sheet_interval="proc" /**one table in sheet**/ tab_color="&Ind_Color_Sheet_P_S7." ); title; title1 j=l bold color=red height=7 font=simplex 'הצגה נוספת בצורת טבלה ולא מטריצה של מעברים של חודש אחרון'; title2 j=l bold color=black height=5 font=simplex 'אוכ-יישויות 833 לא מקושרים שאינן פפר'; title3 j=l bold color=red height=5 font=simplex 'הקבוצות אינן דירוגים אלה קבוצות ציון כא'; proc report data=P_S7_PELET1; column P_IND_Prob mon PREV_TO_CUR P_nr P_tamhil_nr P_Dif_Tamhil_nr ; define P_IND_prob/Display style(header)=[background=lightblue] 'אינד להדלקה' ; define mon/Display style(header)=[background=lightblue] 'חודש' ; define prev_to_cur/Display style(header)=[background=lightblue] 'חודש_קודם+חודש_נוכחי' ; define p_nr/Display style(header)=[background=lightblue] 'ש' ; define p_tamhil_nr/Display style(header)=[background=lightblue] 'חודש' ; define p_dif_tamhil_nr/Display style(header)=[background=lightblue] 'חודש' ; compute P_IND_Prob; IF P_IND_Prob=1 then call define(_col_,"Style","style={background=RED}"); else call define(_col_,"Style","style={background=LIGHTGREEN}"); endcomp; Run; /*******PELETS7-NP---ALL months****/ /*******PELETS7-NP---ALL months****/ /*******PELETS7-NP---ALL months****/ ods excel options(sheet_name="S7_NP_All_Months" embedded_titles='yes' embedded_footnotes='yes' sheet_interval="PROC" /***One table in sheet**/ gridlines = 'yes' ); title; title1 j=l 'ללא פפר-מעברים בקבוצות ציון כא-הצגה עבור כלל חודשים'; proc report data=NP_Want_Mavarim_S7 missing; columns _CAT_ CAT _PREVCAT_ PREVCAT Ind_Same_Better_Worse dif_Categories NP_IND_Prob,mon NP_nr,mon NP_tamhil_nr,mon NP_Dif_Tamhil_nr,mon ; define _CAT_/group; define CAT/group; define _PREVCAT_/group; define PREVCAT/group; define Ind_Same_Better_Worse/group; define dif_Categories/group; define mon/across order=data ' '; define NP_IND_Prob/mean; compute NP_IND_Prob; IF NP_IND_Prob=1 then call define(_col_,"Style","style={background=RED}"); else call define(_col_,"Style","style={background=LIGHTGREEN}"); endcomp; run; /*******PELETS7-Pepper---ALL months****/ /*******PELETS7-Pepper---ALL months****/ /*******PELETS7-Pepper---ALL months****/ ods excel options(sheet_name="S7_P_All_Months" embedded_titles='yes' embedded_footnotes='yes' sheet_interval="PROC" /***One table in sheet**/ gridlines = 'yes' ); title; title1 j=l 'פפר-מעברים בקבוצות ציון כא-הצגה עבור כלל חודשים'; proc report data=P_Want_Mavarim_S7 missing; columns _CAT_ CAT _PREVCAT_ PREVCAT Ind_Same_Better_Worse dif_Categories P_IND_Prob,mon P_nr,mon P_tamhil_nr,mon P_Dif_Tamhil_nr,mon ; define _CAT_/group; define CAT/group; define _PREVCAT_/group; define PREVCAT/group; define Ind_Same_Better_Worse/group; define dif_Categories/group; define mon/across order=data ' '; define P_IND_Prob/mean; compute P_IND_Prob; IF P_IND_Prob=1 then call define(_col_,"Style","style={background=RED}"); else call define(_col_,"Style","style={background=LIGHTGREEN}"); endcomp; run; /****Pelet S8--No pepper--Last month****/ /****Pelet S8--No pepper--Last month****/ /****Pelet S8--No pepper--Last month****/ proc sort data=R_R.NP_CrdRate_MavarimKeshel_S8 out=NP_S8; by combine mon; Run; data NP_S8_b; set NP_S8; by combine; NP_Dif_Tamhil_nr=NP_tamhil_nr-LAG(NP_tamhil_nr); NP_Dif_Tamhil_nr_ra=NP_tamhil_nr_ra-LAG(NP_tamhil_nr_ra); NP_Dif_PCT_ra=NP_PCT_ra-LAG(NP_PCT_ra); Format NP_Dif_Tamhil_nr NP_Dif_PCT_ra NP_Dif_Tamhil_nr NP_Dif_Tamhil_nr_ra percent10.2; if first.combine then do; NP_Dif_Tamhil_nr=.; NP_Dif_PCT_ra=.; NP_Dif_Tamhil_nr=.; NP_Dif_Tamhil_nr_ra=.; end; IF abs(NP_Dif_Tamhil_nr)>0.03 OR abs(NP_Dif_Tamhil_nr_ra)>0.03 /*OR abs(NP_Dif_PCT_ra)>0.03 */ then NP_IND_Prob=1; else NP_IND_Prob=0; Run; data NP_S8_c; retain NP_IND_Prob mon; set NP_S8_b; Run; Data NP_S8_Want1; retain NP_IND_Prob mon; length Ind_Same_Better_Worse $20.; set NP_S8_c; IF _PREVCAT_=0 then Ind_Same_Better_Worse='NO_EXIST_PREV_MON'; else IF _PREVCAT_=1 AND _CAT_=1 then Ind_Same_Better_Worse='NULL_TO_NULL'; else IF _PREVCAT_=1 AND _CAT_ ne 1 then Ind_Same_Better_Worse='NULL_TO_POS'; else IF _PREVCAT_ ne 1 AND _CAT_ = 1 then Ind_Same_Better_Worse='POS_TO_NULL'; else IF _CAT_=_PREVCAT_ then Ind_Same_Better_Worse='SAME'; else IF _CAT_>_PREVCAT_ then Ind_Same_Better_Worse='WORSE'; else IF _CAT_<_PREVCAT_ then Ind_Same_Better_Worse='BETTER'; IF _PREVCAT_ not in (0,1) then dif_Categories=abs(sum(_CAT_,-_PREVCAT_)); Run; proc sort data= NP_S8_Want1; by descending mon _CAT_; Run; data NP_S8_Want_LastMon; set NP_S8_Want1(Where=(mon=&YYMM2.)); Run; proc sql noprint; select MAX(NP_IND_Prob) as Ind_NP_S8_lastMon into :Ind_NP_S8_lastMon TRIMMED from NP_S8_Want_LastMon where mon=&YYMM2. ; quit; %put Ind_NP_S8_lastMon=&Ind_NP_S8_lastMon.; proc sql noprint; select case when &Ind_NP_S8_lastMon.=1 then 'Red' else 'Green' end as Ind_Color_Sheet_S8_lastMon into : NP_Ind_Color_Sheet_S8_lastMon TRIMMED from NP_S8_Want_LastMon ; quit; %put NP_Ind_Color_Sheet_S8_lastMon=&NP_Ind_Color_Sheet_S8_lastMon.; ods excel options (sheet_name='S8-NP_LastMon' sheet_interval="PROC" /**one table in sheet**/ absolute_column_width="13,30,16" row_heights='30,16,20,20,20' gridlines = 'yes' tab_color="&NP_Ind_Color_Sheet_S8_lastMon." ); title; title1 j=l bold color=red height=5 font=simplex 'הצגה עבור חודש אחרון '; title2 j=l bold color=red height=5 font=simplex 'קריטריון להדלקה- שינוי בתמהיל במעל 3%'; title3 j=l bold color=red height=5 font=simplex 'אוכ-יישויות נקיות מחטיבה 833 לא מקושרים שאינן פפר'; proc report data=NP_S8_Want_LastMon; column _ALL_; compute NP_IND_Prob; IF NP_IND_Prob=1 then call define(_col_,"Style","style={background=lightred}"); else call define(_col_,"Style","style={background=LIGHTGREEN}"); endcomp; Run; /****Pelet S8--No pepper--All months****/ /****Pelet S8--No pepper--All months****/ /****Pelet S8--No pepper--All months****/ ods excel options (sheet_name='S8-NP_all_months' sheet_interval="PROC" /**one table in sheet**/ absolute_column_width="13,30,16" row_heights='30,16,20,20,20' embedded_titles='yes' embedded_footnotes='yes' gridlines = 'yes' ); title; title1 j=l bold color=red height=5 font=simplex 'הצגה עבור כלל חודשים'; title2 j=l bold color=red height=5 font=simplex 'קריטריון להדלקה- שינוי בתמהיל במעל 3%'; title3 j=l bold color=red height=5 font=simplex 'אוכ-יישויות נקיות מחטיבה 833 לא מקושרים שאינן פפר'; proc report data=NP_S8_Want1 missing; columns _CAT_ CAT _PREVCAT_ PREVCAT Ind_Same_Better_Worse dif_Categories NP_IND_Prob,mon NP_nr,mon NP_nr_RA,mon NP_PCT_ra,mon NP_tamhil_nr,mon NP_tamhil_nr_ra,mon NP_Dif_PCT_ra,mon NP_Dif_Tamhil_nr,mon NP_Dif_Tamhil_nr_ra,mon ; define _CAT_/group; define CAT/group; define _PREVCAT_/group; define PREVCAT/group; define Ind_Same_Better_Worse/group; define dif_Categories/group; define mon/across order=data ' '; define NP_IND_Prob/mean; compute NP_IND_Prob; IF NP_IND_Prob=1 then call define(_col_,"Style","style={background=lightred}"); else call define(_col_,"Style","style={background=LIGHTGREEN}"); endcomp; run; /****Pelet S8--pepper--Last month****/ /****Pelet S8--pepper--Last month****/ /****Pelet S8--pepper--Last month****/ proc sort data=R_R.P_CrdRate_MavarimKeshel_S8 out=P_S8; by combine mon; Run; data P_S8_b; set P_S8; by combine; P_Dif_Tamhil_nr=P_tamhil_nr-LAG(P_tamhil_nr); P_Dif_Tamhil_nr_ra=P_tamhil_nr_ra-LAG(P_tamhil_nr_ra); P_Dif_PCT_ra=P_PCT_ra-LAG(P_PCT_ra); Format P_Dif_Tamhil_nr P_Dif_PCT_ra P_Dif_Tamhil_nr P_Dif_Tamhil_nr_ra percent10.2; if first.combine then do; P_Dif_Tamhil_nr=.; P_Dif_PCT_ra=.; P_Dif_Tamhil_nr=.; P_Dif_Tamhil_nr_ra=.; end; IF abs(P_Dif_Tamhil_nr)>0.03 OR abs(P_Dif_Tamhil_nr_ra)>0.03 /*OR abs(P_Dif_PCT_ra)>0.03 */ then P_IND_Prob=1; else P_IND_Prob=0; Run; data P_S8_c; retain P_IND_Prob mon; set P_S8_b; Run; Data P_S8_Want1; retain P_IND_Prob mon; length Ind_Same_Better_Worse $20.; set P_S8_c; IF _PREVCAT_=0 then Ind_Same_Better_Worse='NO_EXIST_PREV_MON'; else IF _PREVCAT_=1 AND _CAT_=1 then Ind_Same_Better_Worse='NULL_TO_NULL'; else IF _PREVCAT_=1 AND _CAT_ ne 1 then Ind_Same_Better_Worse='NULL_TO_POS'; else IF _PREVCAT_ ne 1 AND _CAT_ = 1 then Ind_Same_Better_Worse='POS_TO_NULL'; else IF _CAT_=_PREVCAT_ then Ind_Same_Better_Worse='SAME'; else IF _CAT_>_PREVCAT_ then Ind_Same_Better_Worse='WORSE'; else IF _CAT_<_PREVCAT_ then Ind_Same_Better_Worse='BETTER'; IF _PREVCAT_ not in (0,1) then dif_Categories=abs(sum(_CAT_,-_PREVCAT_)); Run; proc sort data= P_S8_Want1; by descending mon _CAT_; Run; data P_S8_Want_LastMon; set P_S8_Want1(Where=(mon=&YYMM2.)); Run; proc sql noprint; select MAX(P_IND_Prob) as Ind_P_S8_lastMon into :Ind_P_S8_lastMon TRIMMED from P_S8_Want_LastMon where mon=&YYMM2. ; quit; %put Ind_P_S8_lastMon=&Ind_P_S8_lastMon.; proc sql noprint; select case when &Ind_P_S8_lastMon.=1 then 'Red' else 'Green' end as Ind_Color_Sheet_S8_lastMon into : P_Ind_Color_Sheet_S8_lastMon TRIMMED from P_S8_Want_LastMon ; quit; %put P_Ind_Color_Sheet_S8_lastMon=&P_Ind_Color_Sheet_S8_lastMon.; ods excel options (sheet_name='S8-P_LastMon' sheet_interval="PROC" /**one table in sheet**/ absolute_column_width="13,30,16" row_heights='30,16,20,20,20' embedded_titles='yes' embedded_footnotes='yes' gridlines = 'yes' tab_color="&P_Ind_Color_Sheet_S8_lastMon." ); title; title1 j=l bold color=red height=5 font=simplex 'הצגה עבור חודש אחרון '; title2 j=l bold color=red height=5 font=simplex 'קריטריון להדלקה- שינוי בתמהיל במעל 3%'; title3 j=l bold color=red height=5 font=simplex 'אוכ-יישויות נקיות פפר'; proc report data=P_S8_Want_LastMon; column _ALL_; compute P_IND_Prob; IF P_IND_Prob=1 then call define(_col_,"Style","style={background=lightred}"); else call define(_col_,"Style","style={background=LIGHTGREEN}"); endcomp; Run; /****Pelet S8-- pepper--All months****/ /****Pelet S8-- pepper--All months****/ /****Pelet S8-- pepper--All months****/ ods excel options (sheet_name='S8-P_all_months' sheet_interval="PROC" /**one table in sheet**/ absolute_column_width="13,30,16" row_heights='30,16,20,20,20' embedded_titles='yes' embedded_footnotes='yes' gridlines = 'yes' ); title; title1 j=l bold color=red height=5 font=simplex 'הצגה עבור כלל חודשים'; title2 j=l bold color=red height=5 font=simplex 'קריטריון להדלקה- שינוי בתמהיל במעל 3%'; title3 j=l bold color=red height=5 font=simplex 'אוכ-יישויות נקיות מחטיבה 833 לא מקושרים שאינן פפר'; proc report data=P_S8_Want1 missing; columns _CAT_ CAT _PREVCAT_ PREVCAT Ind_Same_Better_Worse dif_Categories P_IND_Prob,mon P_nr,mon P_nr_RA,mon P_PCT_ra,mon P_tamhil_nr,mon P_tamhil_nr_ra,mon P_Dif_PCT_ra,mon P_Dif_Tamhil_nr,mon P_Dif_Tamhil_nr_ra,mon ; define _CAT_/group; define CAT/group; define _PREVCAT_/group; define PREVCAT/group; define Ind_Same_Better_Worse/group; define dif_Categories/group; define mon/across order=data ' '; define P_IND_Prob/mean; compute P_IND_Prob; IF P_IND_Prob=1 then call define(_col_,"Style","style={background=lightred}"); else call define(_col_,"Style","style={background=LIGHTGREEN}"); endcomp; run; /***Pelet S9***/ /***Pelet S9***/ /***Pelet S9***/ proc sort data=R_R.CrdRate_Gini_S9 out=sorted_CrdRate_Gini_S9; by descending mon; Run; ods excel options (sheet_name='S9_Gini' sheet_interval="PROC" /***One table in sheet**/ embedded_titles='yes' embedded_footnotes='yes' gridlines = 'yes' tab_color='white' ); title; title1 j=l color=red height=3 'אוכ-יישויות נקיות בעלות ציון כא חיובי'; title2 j=l color=red height=3 'מדד גני -בודק ציון כא מול כשל בפועל'; title3 j=l 'לא הוגדר קריטירון להדלקה'; /*proc print data=sorted_CrdRate_Gini_S9 noobs;Run;*/ proc print data=sorted_CrdRate_Gini_S9 noobs; var mon / style =[width=3in]; var NR_Y / style =[width=3in]; var gini / style =[width=3in]; run; /***Pelet S10***/ /***Pelet S10***/ /***Pelet S10***/ proc sort data=R_R.CrdRate_Tfisa_S10 out=sorted_CrdRate_Tfisa_S10; by descending mon; Run; ods excel options (sheet_name='S10_תפיסה' sheet_interval="PROC" /***One table in sheet**/ embedded_titles='yes' embedded_footnotes='yes' gridlines = 'yes' tab_color='white' ); title; title1 j=l color=red height=2 'אוכ-יישויות נקיות עם ציון כא חיובי'; title2 j=l color=red height=2 'אחוז_תפיסה'; title3 j=l 'לא הוגדר קריטירון להדלקה'; proc print data=sorted_CrdRate_Tfisa_S10 noobs; var mon / style =[width=14in]; var PCT_Tfisa20 / style =[width=14in]; run; ods excel close; /****SEND EMAIL****/ /****SEND EMAIL****/ /****SEND EMAIL****/ /****SEND EMAIL****/ FILENAME mail EMAIL from="XXX.EXXX@XXXX" TO=("XXX.EXXX@XXXX") SUBJECT=" --&last_mon_in_Report_YYMM1.-בקרת ציון כא-דוח לפני פרסום סקור" encoding='utf-8' /**To show Hebrew!!!**/ CONTENT_TYPE="text/plain" attach=(&path. content_type="excel"); ODS LISTING CLOSE; /*ODS HTML BODY=mail;*/ data _null_; file mail; put 'היי'; put 'מצורף קובץ בקרה -&last_mon_in_Report_YYMM1.'; put ' '; put 'בברכה, XXX XXX'; run;     ... View more

Join this webinar and learn how to manage the complexity of marketing decisioning in an everchanging and often real-time environment.   You will learn how: To operationalize customer insights into real-time, personalized decisions that scale. To use arbitration, business rules and contextual data to drive the next best action. Other industries use decisioning excellence that translates into measurable business impact. Register Now   ... View more