topic Re: SAS Code help! in SAS Programming

SAS Code help!

buszhangsy — Mon, 29 Sep 2014 17:52:21 GMT

I am working on a health-related project and I want to identify a medication use index date for each patient. The medication use index date is defined as the first service date for each patient. In most cases, it's just the first service date regardless of medications. However, the first service date can't be used if there is no additional service for that medication within 6 months. In this case, we have to use the second service date as the medication use index date.

Here is an example.

Patient ID Medication name Service date

0000120 Qvar 08/07/2007

0000120 SPIRIVA 08/14/2007

0000120 SPIRIVA 11/14/2007

0000120 SPIRIVA 01/16/2008

0000120 QVar 12/05/2008

For this patient, the first service date (08/07/2007) can't be used as the index date because the next service record (12/05/2008) for QVAR is beyond 6 months. I want to use the second service date (08/14/2007) as the index date.

How can I write a SAS code to address this?

Thanks!!

Re: SAS Code help!

DBailey — Mon, 29 Sep 2014 18:17:34 GMT

how about something like this?

proc sql;

create table want as

select

patient_id,

medication_name,

min(Service_Date) as Medication_Use_Index_Date

from

have t1

where exists (select * from want where patient_id=t1.patient_id and medication_name=t1.medication_name and service_date > t1.service_date and intck('month',t1.service_date,service_date)<=6);

quit;

Re: SAS Code help!

buszhangsy — Mon, 29 Sep 2014 19:51:55 GMT

Thanks! I only learned little SQL language. What does "from have t1" do in the program? I submitted the program and saw error in the log: file work.have.data do not exist, file work.want.data do not exist..

Re: SAS Code help!

ballardw — Mon, 29 Sep 2014 20:00:17 GMT

The convention on this forum is to use "have" for the name of the dataset that you have to start with and "want" is the desired output. That saves us trying to spend lots of time with convoluted library/dataset name combinations.

So where you see "have" replace with your library and dataset.

Re: SAS Code help!

buszhangsy — Mon, 29 Sep 2014 20:07:18 GMT

Still got an error in the log: File WORK.WANT.DATA does not exist....

Re: SAS Code help!

ballardw — Mon, 29 Sep 2014 20:24:40 GMT

Post the code you ran along with the full log result.

Re: SAS Code help!

PGStats — Mon, 29 Sep 2014 20:28:56 GMT

For a dataset (/view) solution:

data have;

input PatientID MedName $ ServiceDate :mmddyy10.;

MedName = upcase(Medname);

datalines;

0000120 Qvar 08/07/2007

0000120 SPIRIVA 08/14/2007

0000120 SPIRIVA 11/14/2007

0000120 SPIRIVA 01/16/2008

0000120 QVar 12/05/2008

;

proc sort data=have; by PatientID MedName ServiceDate; run;

data temp / view=temp;

if not skip then

set have(keep=ServiceDate rename=ServiceDate=NextDate firstObs=2) end=skip;

set have; by PatientID MedName;

if last.medName then call missing (NextDate);

run;

data want;

do until(last.MedName);

set temp; by PatientID MedName;

if missing(indexDate) then

if missing(NextDate) or intck("MONTH", ServiceDate, NextDate, 'C') < 6 then

indexDate = ServiceDate;

end;

drop ServiceDate NextDate;

format indexDate mmddyy10.;

run;

proc print data=want noobs; run;

Re: SAS Code help!

buszhangsy — Mon, 29 Sep 2014 21:34:02 GMT

CODE:

proc sql;

create table index2 as

select

baseid,

FDB_GNN,

min(filldate) as indexdate

from

index1 t1

where exists(select * from index2 where baseid=t1.baseid and FDB_GNN=t1.FDB_GNN and filldate>t1.filldate

and intck('month',t1.filldate,filldate)<=6);

quit;

LOG:

444 proc sql;

445 create table index2 as

446 select

447 baseid,

448 FDB_GNN,

449 min(filldate) as indexdate

450 from

451 index1 t1

452 where exists(select * from index2 where baseid=t1.baseid and FDB_GNN=t1.FDB_GNN and filldate>t1.filldate

453 and intck('month',t1.filldate,filldate)<=6);

ERROR: File WORK.INDEX2.DATA does not exist.

454 quit;

NOTE: The SAS System stopped processing this step because of errors.

NOTE: PROCEDURE SQL used (Total process time):

real time 0.07 seconds

cpu time 0.00 seconds

Re: SAS Code help!

buszhangsy — Mon, 29 Sep 2014 21:47:45 GMT

For most patients there were 2 index date created....I believe one of them was correct, but not sure how the other one generated.....

Re: SAS Code help!

PGStats — Tue, 30 Sep 2014 00:09:12 GMT

I assumed you wanted one index date for each medication name, for each patientID. Is that right?

Re: SAS Code help!

buszhangsy — Tue, 30 Sep 2014 00:16:06 GMT

No, I only need one index date. In most cases, that index date should be the first service date regardless of medications. However, if the corresponding medication does not have a follow-up service within 6 months, its service date can not be used as index date. I need to examine the second service date and see if it meets the criteria.

Thanks!

Re: SAS Code help!

PGStats — Tue, 30 Sep 2014 00:28:16 GMT

Another detail. In the absence of a proper index date for a given patient, do you want missing values, or the last date as the index, or to omit that patientID ?

Re: SAS Code help!

buszhangsy — Tue, 30 Sep 2014 01:10:50 GMT

Omit the patient if we can not find a proper index date. Having an index date is one of the inclusion criteria for the study sample.

Thanks!!

Re: SAS Code help!

PGStats — Tue, 30 Sep 2014 01:59:03 GMT

OK, This should do it. I expanded your example data for better testing :

data have;

input PatientID MedName $ ServiceDate :mmddyy10.;

MedName = upcase(Medname);

datalines;

0000120 Qvar 08/07/2007

0000120 SPIRIVA 08/14/2007

0000120 SPIRIVA 11/14/2007

0000120 SPIRIVA 01/16/2008

0000120 QVar 12/05/2008

0000120 SPIRIVA2 08/13/2007

0000120 SPIRIVA2 11/13/2007

0000120 SPIRIVA2 01/15/2008

0000120 SPIRIVA3 08/15/2007

0000120 SPIRIVA3 11/15/2007

0000120 SPIRIVA3 01/17/2008

0000120 SPIRIVA4 01/11/2008

;

proc sort data=have; by PatientID MedName ServiceDate; run;

data temp / view=temp;

if not skip then

set have(keep=ServiceDate rename=ServiceDate=NextDate firstObs=2) end=skip;

set have; by PatientID MedName;

if last.medName then call missing (NextDate);

run;

data temp2;

do until(last.MedName);

set temp; by PatientID MedName;

if missing(indexDate) then

if not missing(NextDate) then

if intck("MONTH", ServiceDate, NextDate, 'C') < 6 then

indexDate = ServiceDate;

end;

if not missing(indexDate);

drop ServiceDate NextDate;

format indexDate mmddyy10.;

run;

proc sort data=temp2; by patientID indexDate; run;

data want;

set temp2; by PatientID;

if first.PatientID;

run;

proc print data=want noobs; run;

Re: SAS Code help!

buszhangsy — Tue, 30 Sep 2014 02:08:52 GMT

Thank you PG! I will run it first thing tomorrow morning!! The data can only be accessed through my office desktop computers.

I will let you know the result!

Re: SAS Code help!

Ksharp — Tue, 30 Sep 2014 12:48:26 GMT

If I understood what you mean.

data have;
input Patient_ID   $         Medication_name  $     Service_date : mmddyy10.;
format      Service_date  mmddyy10.;
cards;
0000120              QVar                           08/07/2007
0000120              SPIRIVA                     08/14/2007
0000120              SPIRIVA                     11/14/2007
0000120              SPIRIVA                     01/16/2008
0000120              QVar                           12/05/2008
0000120              SPIRIVA2                     08/13/2007
0000120              SPIRIVA2                     11/13/2007
0000120              SPIRIVA2                     01/15/2008
0000120              SPIRIVA3                     08/15/2007
0000120              SPIRIVA3                     11/15/2007
0000120              SPIRIVA3                     01/17/2008
0000120              SPIRIVA4                     01/11/2008
;
run;
data temp;
 if _n_ eq 1 then do;
  if 0 then set have;
  length rank 8;
  declare hash ha();
   ha.definekey('Medication_name');
   ha.definedata('rank');
   ha.definedone();
 end;
set have;
by      Patient_ID;
if ha.find() ne 0 then do;r+1;rank=r;ha.add();end;
if last.Patient_ID then do;ha.clear(); r=0;end;
drop r;
run;


proc sort data=temp ; by Patient_ID rank Service_date;run;

data x;
 set temp;
 by Patient_ID rank;
 if first.rank then n=0;
 n+1;
 lag_Service_date= lag(Service_date) ;
 month=intck('month',lag_Service_date,Service_date,'c');
 if n=2 and month le 6;
 keep  Patient_ID Medication_name lag_Service_date;
 format lag_Service_date mmddyy10.;
run;

data want;
 set x;
 by Patient_ID;
 if first.Patient_ID;
run;

Xia Keshan

Message was edited by: xia keshan

Re: SAS Code help!

buszhangsy — Tue, 30 Sep 2014 14:08:15 GMT

Hi PG, I examined the data on 20 patients and the code does its job!

Thanks a lot!