You should arrange your code in logical order. Your have the PROC TABUlATE at the top, but the code to create the data set below which doesn't really make sense. Note that I edited your post to make it legible.
I think you just want PROC FREQ?
proc freq data=CPIM_Automation;
table workType AgencyAgentType;
run;
Also, you should look into the COALESCEC()/COALESCE() functions - they're quite useful - would replace that last three IF statements into one.
data CPIM_Automation;
set CPIM_Automation;
AgencyAgentType = coalescec(AgentType, AgencyType, "Missing");
run;
@LMSSAS wrote:
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;
Click image to register for webinar
