topic Re: Counting records in SAS in SAS Programming

Counting records in SAS

mbenedet — Tue, 31 Jul 2012 20:49:47 GMT

Hi everyone...sorry for the simplistic question, but I am a brand new (and I do mean new!) user to SAS. Just trying to pull some data and create an output showing number of records in each category. Example of data to be pulled:

Activity Date

Line of Business

Physician Association

Member Count

When I retrieve the data and sum by Member Count for each Physician Association, I then need to know how many of those Physician Associations fall under the following categories:

0 - 100 members

101 - 500 members

501 - 1000 members

1001 - 5000 members

5001 or greater

Is there a simple way to modify the query to accomplish this task?

Mike

Re: Counting records in SAS

ballardw — Tue, 31 Jul 2012 21:22:11 GMT

Assuming you have created a table or dataset with the counts per association I would normally use procs format and freq.

Instead of modifying query add as one way:

proc format library=work;

value members

0 - 100 = ' 0 - 100 members'

101 - 500 = ' 101 - 500 members'
501 - 1000 = ' 501 - 1000 members'

1001 - 5000 = '1001 - 5000 members

5001 - high = '5001 or more members'

;

run;

/*assuming MemberCount holds the sum of members for each association, use your variable name*/

proc freq data=<your data set name>;

table MemberCount ;

format MemberCount members.;

run;

Re: Counting records in SAS

mbenedet — Wed, 01 Aug 2012 12:54:37 GMT

Thank you for your reply. I copied the code and pasted below for what I have so far and all it does is group the data by Physician Association with their total member count. Are you saying I should add all of the code in your reply (starting with proc format library=work; to the end run; statement) to the bottom of my code to make it work?

%_eg_conditional_dropds(WORK.QUERY950);

PROC SQL;

CREATE TABLE WORK.QUERY950 AS SELECT DISTINCT SF_PROF_MEMBERDATA_201206.activity_date,

SF_PROF_MEMBERDATA_201206.master_ipa,

SF_PROF_MEMBERDATA_201206.sf_lob

FROM SERVFUND.SF_PROF_MEMBERDATA_201206 AS SF_PROF_MEMBERDATA_201206

GROUP BY SF_PROF_MEMBERDATA_201206.activity_date, SF_PROF_MEMBERDATA_201206.master_ipa, SF_PROF_MEMBERDATA_201206.sf_lob;

QUIT;