Thank you!! That works ... View more

  I'm using the proc sql code below (below Proc tabulate) to create a data set. In the varibale "WorkType" there are 3 options: "Contact Maint", "Contact New", and "Agency Maint".   I want to create a summary table that gives me a count of each WorkType. I have tried the following proc tabulate and cannot get it. It's my first time creating a table from my data set and I need some help, Please Any suggestions?     Title 1 'Singular Table'; proc tabulate data=CPIM_Automation; Table WorkType; Class 'Contact Maint' 'Contact New' 'Agency New'; run; proc sql; create table CPIM_Automation as SELECT DISTINCT CPC.CHNL_PTNR_CASE_ID AS TrackingRecord ,CPT2.CHNL_PTNR_CASE_TYP_DE AS WorkType ,CPT1.CHNL_PTNR_CASE_TYP_DE AS SubType ,CPC.CHNL_PTNR_CASE_STAT_CD AS CaseStatusCode ,CPCS.SRC_SYS_CD AS SourceCode ,CPC.CHNL_PTNR_CASE_BGN_DT AS CaseStartDate ,AGET.CHNL_PTNR_CASE_SBJ_VAL_ID AS AgentType ,AGEY.CHNL_PTNR_CASE_SBJ_VAL_ID AS AgencyType ,CPCS.CHNL_PTNR_CASE_STEP_NB AS StepNumber FROM CPM1P1.CHNL_PTNR_CASE_STEP CPCS LEFT JOIN CPM1P1.CHNL_PTNR_CASE CPC ON CPCS.CHNL_PTNR_CASE_ID = CPC.CHNL_PTNR_CASE_ID LEFT JOIN CPM1P1.CHNL_PTNR_CASE_TYP CPT1 ON CPC.CHNL_PTNR_CASE_TYP_CD = CPT1.CHNL_PTNR_CASE_TYP_CD LEFT JOIN CPM1P1.CHNL_PTNR_CASE_TYP CPT2 ON CPT1.PRNT_CHNL_PTNR_CASE_TYP_CD = CPT2.CHNL_PTNR_CASE_TYP_CD LEFT JOIN (SELECT DISTINCT SUB2.CHNL_PTNR_CASE_ID, SUB2.CHNL_PTNR_CASE_SBJ_VAL_ID FROM CPM1P1.CHNL_PTNR_CASE_SBJ SUB2 WHERE SUB2.CHNL_PTNR_CASE_SBJ_TYP_CD = 'CONTACTAGENTTYPE') AGET ON CPCS.CHNL_PTNR_CASE_ID = AGET.CHNL_PTNR_CASE_ID LEFT JOIN (SELECT DISTINCT SUB3.CHNL_PTNR_CASE_ID, SUB3.CHNL_PTNR_CASE_SBJ_VAL_ID FROM CPM1P1.CHNL_PTNR_CASE_SBJ SUB3 WHERE SUB3.CHNL_PTNR_CASE_SBJ_TYP_CD = 'AGENCYTYPE') AGEY ON CPCS.CHNL_PTNR_CASE_ID = AGEY.CHNL_PTNR_CASE_ID WHERE CaseStartDate between (intnx('month', today(), -1, 'b')+19) and (intnx('month', today(), 0, 'b')+19) /*CaseStartDate between '20Oct2021'd and '20Nov2021'd*/ and SourceCode = 'SSPortal' and StepNumber = 1 ORDER BY CPC.CHNL_PTNR_CASE_ID ASC ; quit; data CPIM_Automation; set CPIM_Automation; if AgencyType='' and AgentType^='' then AgencyAgentType = AgentType; if AgencyType^='' and AgentType='' then AgencyAgentType = AgencyType; if AgencyType='' and AgentType='' then AgencyAgentType = 'Missing'; run;       ... View more

Yes, that is correct. I do NOT want overlapping periods? Can I make this adjustment in the INTNX Function or will it require additional logic? I do not plan to run this daily, it will be a monthly report. it will begin on the 21st of January and will include the first 20 days of the current month, in this case January and I will pull (December 20-31 - Jan 1-20). The rest of the schedule will look like the list below: Do you see an issue with this logic? I do not, I think this will work. Dec 20, 2021 - Jan 20, 2022 Jan 21, 2022 - Feb 20, 2022 Feb 21, 2022 - March 20, 2022 March 21, 2022 - April 20, 2022 April 21, 2022 - May 20, 2022 May 21, 2022 - June 20, 2022 June 21, 2022 - July 20, 2022 July 21, 2022 - August 20, 2022 Aug 21, 2022 - Sept 20, 2022 Sept 21, 2022 - Oct 20, 2022 Oct 21, 2022 - Nov 20, 2022 Nov 21, 2022 - Dec 20, 2022 ... View more

Thank you for your response, you caused me to think. And yes, my logic is flawed. My concern is that different months have a different number of days, so how will the program know how many days from today(), will get me to the 20th of the month. This may be what you are explaining with the 20th of the month appearing in two intervals and the rest of the days in the month appear in one interval, yes? Regarding the Case Start Date values in January 2022, that date will have passed by the time this runs for the Dec - Jan report. So I will run the 20Dec2021 – 20Jan2022 on Jan 21st. The Jan – Feb will run on Feb 21st. So Starting on Jan 21st it will run to include the previous days. ... View more

Thank you for your response, you caused me to think. And yes, my logic is flawed. My concern is that different months have a different number of days, so how will the program know how many days from today(), will get me to the 20th of the month. This may be what you are explaining with the 20th of the month appearing in two intervals and the rest of the days in the month appear in one interval.. Regarding the Case Start Date values in January 2022, that date will have passed by the time this runs for the Dec - Jan report. So I will run the 20Dec2021 - 20Jan2022 on Jan 21st. The Jan - Feb will run on Feb 21st. So Starting on Jan 21st it will run to include the previous days. ... View more

Thanks so much, that worked!!! I appreciate it!! ... View more

It would be from the 20th of month through the 20th of the next month. So CaseStartDate 20Dec2021 - 20Jan2022 and for Jan report 20Jan2022 - 20Feb2022, etc etc... If Today () returns December 29, 2021 then it would be Today (),-9 ... View more

Thank you for your time! ... View more

Thank you for your time! ... View more

Thank you for your time ! ... View more

Tom, Thank you, this worked!! I appreciate the help 🙂 ... View more

Thank you, I will use these. i appreciate the inforamtion ... View more