topic Re: Help with creating a single table in SAS Programming

help creating single table

LMSSAS — Wed, 12 Jan 2022 21:46:00 GMT

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;

Help with creating a single table

LMSSAS — Wed, 12 Jan 2022 22:27:29 GMT

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;

Re: Help with creating a single table

PaigeMiller — Wed, 12 Jan 2022 21:38:42 GMT

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.

Re: help creating single table

PaigeMiller — Wed, 12 Jan 2022 22:03:34 GMT

@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;

Re: help creating single table

LMSSAS — Wed, 12 Jan 2022 22:08:59 GMT

Thank you!! That works

Re: Help with creating a single table

Reeza — Wed, 12 Jan 2022 22:27:51 GMT

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;

Re: Help with creating a single table

LMSSAS — Thu, 13 Jan 2022 12:56:01 GMT

Thank you, very helpfil