Count by Group Returning Duplicate Rows Proc SQL

[jwilliams]

I am trying to get a count of members per network, product, line of business, zip and county. It works until I add in County. Then I get duplicate rows and it splits the totals as if the county is different, when it's not (All rows are Los Angeles County). I have tried removing county from the group by, but not select statement, and then I still get duplicate rows only with the same count on the dup row.

The count only works if I remove the County from both the group by and the Select (and order by). I have tried format and length statements as well as trimming the County. Nothing works. My code and sample data examples are below:

Proc SQL;
Create Table ActualMbrshp AS
Select Distinct

TRIM(NTWK_NM) as NTWK_NM
,TRIM(NWID.NetworkID) as NetworkID
,CAT(TRIM(ProdLvl2)," ",TRIM(MBULvl2)) as LineofBusiness
,TRIM(ProdNm) as ProdNm
,TRIM(UPPER(County)) as County format =$20. length=20
,TRIM(Zip) as Zip
,Count (*) as Count

From Mbrshp.PSHMO_Membership as mbr
left join Mbrshp.NetworkIds as NWID on UPPER(NWID.NetworkName)=mbr.NTWK_NM
Where Zip = "90001"
Group by NTWK_NM, LineofBusiness, ProdNm, County, Zip
Order By NTWK_NM, LineofBusiness, ProdNm, County, Zip;

Result:

Desired Result (only with county name included):

Any help is much appreciated!

[Patrick · Accepted solution]

First thing I'd be doing is to run below code.

proc sort data=ActualMbrshp out=test;
  by <variables from order by clause>;
run;

If the "duplicates" remain then you know that there are actual differences in your group by variables - for example tabs that don't print.

If there aren't duplicates anymore then you know that you need to tweak your SQL.

Let us know what case applies and we go from there.

[jwilliams]

Thank you! I ran this and still got dup rows so I went back to the original source file (XLSX) and trimmed/cleaned the County column there and saved as txt (for infile). I reran my code and I no longer get dups! Not sure why the trim/clean in SAS SQL didn't take care of that but perhaps it was because that column was originally saved as General instead of Text/ I converted it  to text this time before saving the file as txt. 

[Tom]

Looks to me like you are selecting 6 variables,

1 -> TRIM(NTWK_NM) as NTWK_NM
2 -> ,TRIM(NWID.NetworkID) as NetworkID
3 -> ,CAT(TRIM(ProdLvl2)," ",TRIM(MBULvl2)) as LineofBusiness
4 -> ,TRIM(ProdNm) as ProdNm
5 -> ,TRIM(UPPER(County)) as County format =$20. length=20
6 -> ,TRIM(Zip) as Zip

but grouping by only 5 of them. 

Group by 
1 -> NTWK_NM
2 -> , LineofBusiness
3 -> , ProdNm
4 -> , County
5 -> , Zip

So the 6th variable must include different values.

[Reeza]

NetWORKID is on your SELECT but not your GROUP BY. You should see a message in your log about remerging statistics? Either include it in the GROUP BY or remove it from the SELECT.

Most SQL will actually error out on this query, but it's also a very handy feature when you want to add summary statistics to a data set quickly.

[Kurt_Bremser]

Also use the CALCULATED keyword in the GROUP BY clause for newly created variables that have the same name as the source variables in the dataset.