topic Re: Proc SQL count distinct Question in SAS Programming

Proc SQL count distinct Question

LMSSAS — Fri, 28 Jan 2022 20:03:41 GMT

I am using proc sql to do a summary count of next-step automation and next-step manual, by Worktype: Contact New, Contact Maint, Agency New, and Agency Maint. There are a total of 4 WorkTypes. I am not sure how to structure the query for this. I have started with whats below and getting the output shown, clearly it isn’t right. Does anyone provide s suggestion?

proc sql; 
create table Summary_Combine_NS as
Select 
	'WorkType'n,
	'Next_Step'n,
	 count(Distinct 'Next_Step'n) as Counts
From CPIM_NS_Automation
Group by 'WorkType'n
;
quit;

Re: Proc SQL count distinct Question

novinosrin — Fri, 28 Jan 2022 20:06:51 GMT

What is your data(sample) and the required output?

Re: Proc SQL count distinct Question

LMSSAS — Fri, 28 Jan 2022 20:15:30 GMT

I'm not sure if this is what you asking for. Its the original code with the output. Thank you!

PROC SQL;
   CONNECT TO DB2 AS DB1 (DATAsrc=IDS1P1 AUTHDOMAIN="DEFAULTAUTH");
CREATE TABLE CPIM_OPEN_INVENTORY_&SYSDATE._1 AS 
SELECT *
      FROM CONNECTION TO DB1
         (SELECT DISTINCT
			CPC.CHNL_PTNR_CASE_ID "TrackingCase"
			,CPT2.CHNL_PTNR_CASE_TYP_DE "WorkType"
			,CPT1.CHNL_PTNR_CASE_TYP_DE "SubWorkType"
			,CPC.CHNL_PTNR_CASE_BGN_DT "CreateDate"
			,TMP1.VAL_DE "Status"
			,TMP2.VAL_DE "SubStatus"
			,CPCS.SRC_SYS_CD "SourceCode"
			,AGET.CHNL_PTNR_CASE_SBJ_VAL_ID AS "AgentType"
			,AGEY.CHNL_PTNR_CASE_SBJ_VAL_ID AS "AgencyType"
			,CPC.CHNL_PTNR_DE AS 'Next_Step'n


FROM
CPM1P1.CHNL_PTNR_CASE CPC
LEFT JOIN CPM1P1.CHNL_PTNR_CASE_STEP CPCS ON CPC.CHNL_PTNR_CASE_ID = CPCS.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 CPM1P1.CHNL_PTNR_CASE_PTY CPCP ON UPPER(CPCS.RESP_PTY_ID) = UPPER(CPCP.CHNL_PTNR_CASE_PTY_ID)
LEFT JOIN CPM1P1.TMP_CD_VAL TMP1 ON CPCS.CHNL_PTNR_CASE_STEP_STAT_CD = TMP1.VAL_CD
LEFT JOIN CPM1P1.TMP_CD_VAL TMP2 ON CPCS.CASE_STEP_SUB_STAT_CD = TMP2.VAL_CD

INNER JOIN (
		SELECT DISTINCT CPCS2.CHNL_PTNR_CASE_ID, 
			MAX(CPCS2.CHNL_PTNR_CASE_STEP_NB) NMBR
            FROM CPM1P1.CHNL_PTNR_CASE_STEP CPCS2
            GROUP BY CHNL_PTNR_CASE_ID) SUB
            ON CPCS.CHNL_PTNR_CASE_ID = SUB.CHNL_PTNR_CASE_ID 
			AND CPCS.CHNL_PTNR_CASE_STEP_NB = SUB.NMBR
            
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
          
LEFT JOIN (SELECT DISTINCT
          SUB4.CHNL_PTNR_CASE_ID, SUB4.CHNL_PTNR_CASE_SBJ_VAL_ID
          FROM CPM1P1.CHNL_PTNR_CASE_SBJ SUB4
          WHERE SUB4.CHNL_PTNR_CASE_SBJ_TYP_CD = 'CONTACTAGENTTYPE') AGET
          ON CPCS.CHNL_PTNR_CASE_ID = AGET.CHNL_PTNR_CASE_ID
            
WHERE
  CPCS.CHNL_PTNR_CASE_STEP_STAT_CD <> 'CL'
/*  AND TMP2.VAL_DE <> 'Argentinian'*/ /* do not uncomment this filters out some o  the subworktypes that are needed*/
/*  AND TMP2.VAL_DE <> 'Arkansas'*/ /* do not uncomment this filters out some of the subworktypes that are needed*/
ORDER BY CPC.CHNL_PTNR_CASE_ID ASC);
DISCONNECT FROM DB1;  
QUIT;

Re: Proc SQL count distinct Question

LMSSAS — Fri, 28 Jan 2022 20:44:29 GMT

Next_Step	Contact New	Contact Maint	Agency New	Agency Maint
Automation	Count	Count	Count	Count
Manaul	Count	Count	Count	Count
Blank	Count	Count	Count	Count

Re: Proc SQL count distinct Question

Tom — Fri, 28 Jan 2022 22:50:25 GMT

Read your SAS log. It will show that SAS had to remerge the COUNT() aggregate value onto the detailed rows.

If you only want one observation per WORK_TYPE do not include NEXT_STEP in the list of variables to select.

create table Summary_Combine_NS as
select 
  'WorkType'n
 , count(distinct 'Next_Step'n) as Distinct_Steps
 , count(*) as N_observations
  from CPIM_NS_Automation
  group by 'WorkType'n
;

Note that most other implementations of SQL will not allow a query using GROUP BY where there are variables being selected that are neither GROUP BY variables nor aggregate function results.

Re: Proc SQL count distinct Question

LMSSAS — Mon, 31 Jan 2022 13:24:10 GMT

Thank you, Tom

I used your query and it definitely got me closer in format to what I'm looking for. My ouput is below. The N_observations colum is actually counting the number of each Work type in the ouput data. What I need to count is the Number of Automation in the Next_Step column and the mumber of Manual in the Next-Step Column.

Is that possible?

Re: Proc SQL count distinct Question

Tom — Mon, 31 Jan 2022 13:39:01 GMT

Sounds like you just want to add that as one of the GROUP BY variables.

create table Summary_Combine_NS as
select 
   WorkType
 , Next_Step
 , count(*) as N_observations
  from CPIM_NS_Automation
  group by WorkType, Next_Step
;

Re: Proc SQL count distinct Question

LMSSAS — Mon, 31 Jan 2022 13:45:01 GMT

Yes, that worked and gave me what I'm looking for..
Thank you!!