I use the below proc sql to create a data set. The varibale "WorkType" contains three options: 'Contact Maint', 'Contact New', and 'Agency Maint'. How do I create a single table providing a count of each of the WorkTypes? I want to send as a summary table.
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;
@LMSSAS wrote:
I use the below proc sql to create a data set. The varibale "WorkType" contains three options: 'Contact Maint', 'Contact New', and 'Agency Maint'. How do I create a single table providing a count of each of the WorkTypes? I want to send as a summary table.
proc freq data=CPIM_Automation ;
tables worktype/out=_counts_;
run;
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;
Please don't type plain text in a code box, this means to read your text we have to constantly scroll to the right.
I would appreciate it if you could re-format everything so the text is readable without constantly scrolling to the right.
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;
@LMSSAS wrote:
I use the below proc sql to create a data set. The varibale "WorkType" contains three options: 'Contact Maint', 'Contact New', and 'Agency Maint'. How do I create a single table providing a count of each of the WorkTypes? I want to send as a summary table.
proc freq data=CPIM_Automation ;
tables worktype/out=_counts_;
run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.