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
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;
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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.