BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
jwilliams
Calcite | Level 5

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:

jwilliams_0-1639173239345.png

Desired Result (only with county name included):

jwilliams_1-1639173325876.png

Any help is much appreciated!

1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

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.

View solution in original post

5 REPLIES 5
Patrick
Opal | Level 21

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
Calcite | Level 5

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
Super User Tom
Super User

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
Super User
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.

SAS Innovate 2025: Call for Content

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!

Submit your idea!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 2396 views
  • 0 likes
  • 5 in conversation