BookmarkSubscribeRSS Feed
gdaymte
Obsidian | Level 7

I am trying to eliminate some extra steps in my SAS code.

 

I need to use the same source data a few times to accurately count certain the numerator and denominator for a particular measure.

 

The first step in the process is to count distinct PERSONID by quarter for records where state=LA. This will be the count for my denominator.

 

DATA ONE (KEEP=RX_QTRYR PERSONID); SET O_TEST2012;
     IF STATE='LA';
RUN;

PROC SQL;
     CREATE TABLE ONE_LA AS
     SELECT RX_QTRYR, COUNT(DISTINCT PERSONID) AS NUM_LA
     FROM ONE
     GROUP BY RX_QTRYR
     ;
QUIT;

 

In order to calculate the numerator, I have to re-sort the original data by PERSONID, DT_FILLED and DT_COMPLETE. Next, I have to use a lag function with a by group and an IF/THEN/DO statement to output only the First PERSONID in each grouping. Then, I to sort the data agian using NODUPKEY by PERSONID and RX_QTRYR.  

 

PROC SORT DATA=O_TEST2012;
	BY PERSONID DT_FILLED DT_COMPLETE;
RUN;

DATA TWO; SET O_TEST2012;

	BY PERSONID DT_FILLED DT_COMPLETE;

	P_DT_COMPLETE=LAG(DT_COMPLETE);
	
	IF FIRST.PERSONID THEN DO;
		OUTPUT;
	END;

	FORMAT P_DT_COMPLETE MMDDYY10.;
RUN;

PROC SORT DATA=THREE OUT=FOUR NODUPKEY;
	BY PERSONID RX_QTRYR;
RUN;

 

Since I have to calculate the numerator by using the same original dataset, would there be a way to incorporate this distinct count of STATE=LA into the dataset to calculate the numerator from the beginning? I was thinking about using PROC SQL to eliminate some of the sorting, but was hoping to be able to include the counting piece as well.

 

PROC SQL;
	CREATE TABLE ONE_V2 AS
		SELECT
			PERSONID,
			DT_FILLED,
			DT_COMPLETE,
			RX_QTRYR,
			STATE,
			CASE
                             WHEN STATE='LA' THEN COUNT(DISTINCT PERSONID)
                             ELSE 0
                        END AS NUM_LA
		FROM O_TEST2012
		GROUP BY RX_QTRYR
		ORDER BY PERSONID, DT_FILLED, DT_COMPLETE
		;
QUIT;

Unfortunately, that case statement doesn't yield the same results as the count in table ONE_LA. Any assistance would be greatly appreciated.

 

2 REPLIES 2
PGStats
Opal | Level 21

So, the denominator is the number of personid in LA in each quarter. What is the numerator?

PG
gdaymte
Obsidian | Level 7

YOu are correct about the denominator...The numerator takes dataset FOUR a couple of steps further. Ultimately, the numerator will be the distict count of PERSONID by RX_QTRYR from the resulting dataset.

 

PROC SQL;
	CREATE TABLE FIVE AS
		SELECT
			A.PERSONID,
			A.RX_QTRYR,
			B.DT_FILLED ,
			B.DT_COMPLETE
		FROM FOUR A, O_TEST2012 B
		WHERE
			A.PERSONID=B.PERSONID AND
			B.STATE='LA' AND
			A.RX_QTRYR=B.RX_QTRYR AND
			B.DT_FILLED >= A.DT_FILLED 
		ORDER BY A.PERSONID, A.RX_QTRYR
		;
QUIT;

PROC SQL;
	CREATE TABLE SIX AS
		SELECT
			RX_QTRYR,
			COUNT(DISTINCT PERSONID) AS NUM_ON
		FROM FIVE
		GROUP BY RX_QTRYR
		;
QUIT;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 2 replies
  • 907 views
  • 0 likes
  • 2 in conversation