The count statement is not showing the correct numbers.

[Sandeep77]

Hi all,

I have a dataset and I am trying to count the number of accountkey. I used the count statement and it gives me a number but actually it is the wrong number. When I manually check the accountkey in the previous table, it is lesser than what it gives me when I use the count statement. The code runs fine with no error. Can you please check and let me know what is the issue here. Please check a sample data set and my code below:

Data Test;
input icustomerid debt_code Trace_Date End_Trace_DT Manual_Trace_Result Month Postcode_Traced_To AccountKey CompanyKey PortfolioKey CorrespondenceKey ContactTypeKey CorrespondenceID ContactSentTo DateKey LetterCode LetterName LetterGroup
;
Datalines;
1234 456853 07APR2022:08:30:58.000 23JUN2022:23:43:00 NEW Apr-22 za2 1ab 7543513 2 6999 6996 7 802492605 za2 1ab 20221205 BPNL3DEF Broken Plan Non NON DD DEFAULT
5462 254954 01JUN2022:15:38:13.000 06SEP2022:21:34:00 LAS Jun-22 sh2 4cz 8462465 2 8945 7224 7 804042769 sh2 4cz 20221209 HPHL5DEF Helping Hand DISCOUNT
4595 854325 18MAY2022:09:07:41.000 26MAY2022:16:39:06 NEG May-22 2467265 2 8910 7224 7 805471845 Fa2 9ca 20221215 HPHL5DEF Helping Hands DISCOUNT
;
run;

proc sql; 
create table Letters_After as 
	select distinct a.icustomerid,
					a.debt_code ,
					a.Trace_Date,
		a.End_Trace_Dt,
		a.Manual_Trace_Result,
		a.Month,
		b.accountkey ,
					count(b.AccountKey) as Number_of_Letters,
		case when count (b.AccountKey) >= 1 then 1 else 0 end as Lettered
			from Joining_Dataset as a 
				left join letters_dwhdw as b on a.accountkey = b.accountkey
					group by a.debt_code, a.Trace_Date ; 
quit; 

[PaigeMiller]

When providing data as SAS data step code, the code must work. Your code produces lots of errors. Please fix it so we can help you.

[Kurt_Bremser]

In your SQL, you use datasets joining_datasets and letters. Provide example data for both datasets in working (no ERRORs, WARNINGs, or invalid data NOTEs) data steps.

[Sandeep77]

Okay, below is the data sample for joining_Dataset table:

Data Joining_Dataset;
input icustomerid debt_code Trace_Date End_Trace_DT Manual_Trace_Result Month Postcode_Traced_To AccountKey CompanyKey PortfolioKey CorrespondenceKey ContactTypeKey CorrespondenceID ContactSentTo DateKey LetterCode LetterName LetterGroup
;
Datalines;
1234 456853 07APR2022:08:30:58.000 23JUN2022:23:43:00 NEW Apr-22 za2 1ab 7543513 2 6999 6996 7 802492605 za2 1ab 20221205 BPNL3DEF Broken Plan Non NON DD DEFAULT
5462 254954 01JUN2022:15:38:13.000 06SEP2022:21:34:00 LAS Jun-22 sh2 4cz 8462465 2 8945 7224 7 804042769 sh2 4cz 20221209 HPHL5DEF Helping Hand DISCOUNT
4595 854325 18MAY2022:09:07:41.000 26MAY2022:16:39:06 NEG May-22 2467265 2 8910 7224 7 805471845 Fa2 9ca 20221215 HPHL5DEF Helping Hands DISCOUNT
;
run;

Sample Dataset for Letters_After:

Data Letters_After;
input
icustomerid debt_code Trace_Date End_Trace_DT Manual_Trace_Result Month AccountKey Number_of_Letters Lettered;
Datalines;
4545 679154 12JAN2022:13:23:02.000 01FEB2022:14:50:38 NEG Jan-22 34428257 100 1
4545 689715 12JAN2022:13:23:03.000 01FEB2022:14:51:13 NEG Jan-22 35321022 841 1
12546 254654 21OCT2022:12:41:06.000 01JAN2100:00:00:00 LAS Oct-22 28096910 1 1
12546 245687 21OCT2022:12:41:06.000 01JAN2100:00:00:00 LAS Oct-22 33915294 1 1
12546 245679 21OCT2022:12:41:05.000 01JAN2100:00:00:00 LAS Oct-22 38995267 169 1
;
Run;

[Kurt_Bremser]

 69         Data Joining_Dataset;
 70         input icustomerid debt_code Trace_Date End_Trace_DT Manual_Trace_Result Month Postcode_Traced_To AccountKey CompanyKey
 70       ! PortfolioKey CorrespondenceKey ContactTypeKey CorrespondenceID ContactSentTo DateKey LetterCode LetterName LetterGroup
 71         ;
 72         Datalines;
 
 NOTE: Invalid data for Trace_Date in line 73 13-34.
 NOTE: Invalid data for End_Trace_DT in line 73 36-53.
 NOTE: Invalid data for Manual_Trace_Result in line 73 55-57.
 NOTE: Invalid data for Month in line 73 59-64.
 NOTE: Invalid data for Postcode_Traced_To in line 73 66-68.
 NOTE: Invalid data for AccountKey in line 73 70-72.
 NOTE: Invalid data for DateKey in line 73 106-108.
 NOTE: Invalid data for LetterCode in line 73 110-112.
 NOTE: Invalid data for LetterGroup in line 73 123-130.
 REGEL:     ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+----0                     
 73         1234 456853 07APR2022:08:30:58.000 23JUN2022:23:43:00 NEW Apr-22 za2 1ab 7543513 2 6999 6996 7 80249
       101  2605 za2 1ab 20221205 BPNL3DEF Broken Plan Non NON DD DEFAULT
 icustomerid=1234 debt_code=456853 Trace_Date=. End_Trace_DT=. Manual_Trace_Result=. Month=. Postcode_Traced_To=. AccountKey=.
 CompanyKey=7543513 PortfolioKey=2 CorrespondenceKey=6999 ContactTypeKey=6996 CorrespondenceID=7 ContactSentTo=802492605 DateKey=.
 LetterCode=. LetterName=20221205 LetterGroup=. _ERROR_=1 _N_=1
 NOTE: Invalid data for Trace_Date in line 74 13-34.
 NOTE: Invalid data for End_Trace_DT in line 74 36-53.
 NOTE: Invalid data for Manual_Trace_Result in line 74 55-57.
 NOTE: Invalid data for Month in line 74 59-64.
 NOTE: Invalid data for Postcode_Traced_To in line 74 66-68.
 NOTE: Invalid data for AccountKey in line 74 70-72.
 NOTE: Invalid data for DateKey in line 74 106-108.
 NOTE: Invalid data for LetterCode in line 74 110-112.
 NOTE: Invalid data for LetterGroup in line 74 123-130.
 74         5462 254954 01JUN2022:15:38:13.000 06SEP2022:21:34:00 LAS Jun-22 sh2 4cz 8462465 2 8945 7224 7 80404
       101  2769 sh2 4cz 20221209 HPHL5DEF Helping Hand DISCOUNT
 icustomerid=5462 debt_code=254954 Trace_Date=. End_Trace_DT=. Manual_Trace_Result=. Month=. Postcode_Traced_To=. AccountKey=.
 CompanyKey=8462465 PortfolioKey=2 CorrespondenceKey=8945 ContactTypeKey=7224 CorrespondenceID=7 ContactSentTo=804042769 DateKey=.
 LetterCode=. LetterName=20221209 LetterGroup=. _ERROR_=1 _N_=2
 NOTE: Invalid data for Trace_Date in line 75 13-34.
 NOTE: Invalid data for End_Trace_DT in line 75 36-53.
 NOTE: Invalid data for Manual_Trace_Result in line 75 55-57.
 NOTE: Invalid data for Month in line 75 59-64.
 NOTE: Invalid data for CorrespondenceID in line 75 98-100.
 NOTE: Invalid data for ContactSentTo in line 75 102-104.
 NOTE: Invalid data for LetterCode in line 75 115-122.
 NOTE: Invalid data for LetterName in line 75 124-130.
 NOTE: Invalid data for LetterGroup in line 75 132-136.
 75         4595 854325 18MAY2022:09:07:41.000 26MAY2022:16:39:06 NEG May-22 2467265 2 8910 7224 7 805471845 Fa2
       101   9ca 20221215 HPHL5DEF Helping Hands DISCOUNT
 icustomerid=4595 debt_code=854325 Trace_Date=. End_Trace_DT=. Manual_Trace_Result=. Month=. Postcode_Traced_To=2467265 AccountKey=2
 CompanyKey=8910 PortfolioKey=7224 CorrespondenceKey=7 ContactTypeKey=805471845 CorrespondenceID=. ContactSentTo=. DateKey=20221215
 LetterCode=. LetterName=. LetterGroup=. _ERROR_=1 _N_=3

 69         Data Letters_After;
 70         input
 71         icustomerid debt_code Trace_Date End_Trace_DT Manual_Trace_Result Month AccountKey Number_of_Letters Lettered;
 72         Datalines;
 
 NOTE: Invalid data for Trace_Date in line 73 13-34.
 NOTE: Invalid data for End_Trace_DT in line 73 36-53.
 NOTE: Invalid data for Manual_Trace_Result in line 73 55-57.
 NOTE: Invalid data for Month in line 73 59-64.
 REGEL:     ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+----0                     
 73         4545 679154 12JAN2022:13:23:02.000 01FEB2022:14:50:38 NEG Jan-22 34428257 100 1
 icustomerid=4545 debt_code=679154 Trace_Date=. End_Trace_DT=. Manual_Trace_Result=. Month=. AccountKey=34428257
 Number_of_Letters=100 Lettered=1 _ERROR_=1 _N_=1
 NOTE: Invalid data for Trace_Date in line 74 13-34.
 NOTE: Invalid data for End_Trace_DT in line 74 36-53.
 NOTE: Invalid data for Manual_Trace_Result in line 74 55-57.
 NOTE: Invalid data for Month in line 74 59-64.
 74         4545 689715 12JAN2022:13:23:03.000 01FEB2022:14:51:13 NEG Jan-22 35321022 841 1
 icustomerid=4545 debt_code=689715 Trace_Date=. End_Trace_DT=. Manual_Trace_Result=. Month=. AccountKey=35321022
 Number_of_Letters=841 Lettered=1 _ERROR_=1 _N_=2
 NOTE: Invalid data for Trace_Date in line 75 14-35.
 NOTE: Invalid data for End_Trace_DT in line 75 37-54.
 NOTE: Invalid data for Manual_Trace_Result in line 75 56-58.
 NOTE: Invalid data for Month in line 75 60-65.
 75         12546 254654 21OCT2022:12:41:06.000 01JAN2100:00:00:00 LAS Oct-22 28096910 1 1
 icustomerid=12546 debt_code=254654 Trace_Date=. End_Trace_DT=. Manual_Trace_Result=. Month=. AccountKey=28096910 Number_of_Letters=1
 Lettered=1 _ERROR_=1 _N_=3
 NOTE: Invalid data for Trace_Date in line 76 14-35.
 NOTE: Invalid data for End_Trace_DT in line 76 37-54.
 NOTE: Invalid data for Manual_Trace_Result in line 76 56-58.
 NOTE: Invalid data for Month in line 76 60-65.
 76         12546 245687 21OCT2022:12:41:06.000 01JAN2100:00:00:00 LAS Oct-22 33915294 1 1
 icustomerid=12546 debt_code=245687 Trace_Date=. End_Trace_DT=. Manual_Trace_Result=. Month=. AccountKey=33915294 Number_of_Letters=1
 Lettered=1 _ERROR_=1 _N_=4
 NOTE: Invalid data for Trace_Date in line 77 14-35.
 NOTE: Invalid data for End_Trace_DT in line 77 37-54.
 NOTE: Invalid data for Manual_Trace_Result in line 77 56-58.
 NOTE: Invalid data for Month in line 77 60-65.
 77         12546 245679 21OCT2022:12:41:05.000 01JAN2100:00:00:00 LAS Oct-22 38995267 169 1
 icustomerid=12546 debt_code=245679 Trace_Date=. End_Trace_DT=. Manual_Trace_Result=. Month=. AccountKey=38995267
 Number_of_Letters=169 Lettered=1 _ERROR_=1 _N_=5

FIX THIS.