Handling Date variable in claims data

Reply
Occasional Contributor san
Occasional Contributor
Posts: 5

Handling Date variable in claims data

[ Edited ]

Hi there,

 

I am currently working with claims dataset. I need to get patient level claims dataset. My dataset looks like:

ClaimID  Patient ID   Rxflag  Filldate       Filldatethru

1              5                 1        08/09/10      09/08/10

2              5                 1        10/08/10      11/09/10

3              5                 0        10/08/10      12/13/10

4              6                 1        03/09/11       05/10/11

5              6                 0        06/10/12       07/13/11   

 

I need to create variables named start_date and end_date based on unique patient ID to capture the date of first and last fill. I tried using something like this:

 

 

data PATIENT_LEVEL_EVER_FLAGS (keep=pt_id drug_ever);
	set FLAGGED_CLAIMS;
	by pt_id;
	label drug_ever="Ever have drug code of interest";

	if first.pt_id then
		do;
			drug_ever=0;
		end;

	if rx_flag=1 then
		drug_ever=1;

	if drug_ever=1 then
		startdate=filldate;

	if last.pt_id then
		output;

	if last.pt_id then
		enddate=filldatethru;
run;

 

I am confused with the concept. Could someone please explain me the basics behind this?  Any suggestions on how to proceed will be of great help! Thanks.

Super User
Posts: 3,233

Re: Handling Date variable in claims data

A better option might be SQL. This should be fairly close:

 

proc sql;
  create table patients as
   select patientid
             ,count(*) as patientvisits
             ,min(filldate) as firstfilldate format = date9.
             ,max(filldatethru)  as lastfilldatethru format = date9.
  from flagged_claims
  where rx_flag = 1
  group by patientid
  ;
quit;
Occasional Contributor san
Occasional Contributor
Posts: 5

Re: Handling Date variable in claims data

Hi SASKiwi,

 

Thank you for your input and SQL codes. I will try using this approach to see if it works. 

Respected Advisor
Posts: 4,811

Re: Handling Date variable in claims data

Or, if you want to keep patients without any rx_flag=1 records 

 

proc sql;
  create table patients as
   select patientId
             ,count(*) as patientVisits
             ,min(case when rx_flag then filldate else . end) as firstfilldate format = date9.
             ,max(case when rx_flag then filldatethru else . end)  as lastfilldatethru format = date9.
  from flagged_claims
  group by patientId
  ;
quit;
PG
Respected Advisor
Posts: 4,811

Re: Handling Date variable in claims data

Or, if you prefer a data step (should be more efficient)

 

data patients;
do until(last.patientid);
	set flagged_claims; by patientId;
	if rx_flag then do;
		firstFillDate = min(firstFillDate, fillDate);
		lastFillDateThru = max(lastFillDateThru, fillDateThru);
		end;
	end;
keep patientId firstFillDate lastFillDateThru;
format firstFillDate lastFillDateThru yymmdd10.;
run;
PG
Occasional Contributor san
Occasional Contributor
Posts: 5

Re: Handling Date variable in claims data

Hello PGStats,

 

Thank you for providing the SQL method for keeping all patient-level observations. It worked perfectly. (I now have start date for every patient who was prescribed the drug and no start date for every patient who was not prescribed the drug).

 

Thanks a lot!

Occasional Contributor san
Occasional Contributor
Posts: 5

Re: Handling Date variable in claims data

Hi SASKiwi,

 

The SQL method worked great. It just keeps the observation with RX_flag=1(because of the Where clause). But, I needed to keep all the observations (those without Rx_flag=1 also), hence I modified your method using suggestions made by PGStats.

 

Thanks a ton! 

Super User
Posts: 19,051

Re: Handling Date variable in claims data

Based on the sample dataset abouve what would you expect as output?

And what are you having issues with in your code?

 

Hint: Look into RETAIN to keep variable value across rows.

Occasional Contributor san
Occasional Contributor
Posts: 5

Re: Handling Date variable in claims data

Hi Reeza,

Thanks for your inputs. I expect that the output will have all the variables given in the sample and will create two new variables 'startdate' and 'enddate'. Startdate represents when the first prescription was filled(is equal to filldate). Enddate is when the prescription was stopped(is equal to filldatethru). So, I need to keep start and end dates for each unique patient who was prescribed a drug (whose rx_flag=1). However, when I try running the code which I have mentioned, it gives me missing values for startdate even for rx_flag=1. So, I am not sure how I should be specifying that condition. Should it be

 

if rx_flag=1 then startdate=filldate?

 

I will look into the RETAIN statement that you mentioned. Thanks again!

New Contributor
Posts: 2

Re: Handling Date variable in claims data

Hi San, 

If I understand your question correctly, you want startdate = fill date and enddate = fillthrudate when rx_flag = 1 otherwise startdate = filldate. 

 

Try the solution below: 

 

data patientrx;
  set filldate ;
  by patient_id;
if rx_flag = 1 then 
  do ;
   startdate = filldate;
   enddate = filldatethru;
   end;
else; do;
  startdate = filldate;
  end;
  format startdate enddate mmddyy9.;
run;



 

Ask a Question
Discussion stats
  • 9 replies
  • 462 views
  • 0 likes
  • 5 in conversation