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!
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.
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.
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.
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.
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.
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.