BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Yilikal_Tesfaye
Fluorite | Level 6

 

 Hi,

   

I am working on the prescription dataset from a health organization.  the dataset contains the following variables in the snapshot below.  I want to count a number prescription per patient based on the following criteria, 

  1. If the deliv_date2 is less than seven and the same ATC code for the individual patient then set a number of prescription is equal to one and sum DDD otherwise count the number of prescription.

Thank you in advance!!

 

data thesis.presc2;
set thesis.presc;
by patient;
if first.patient then lastdate=.;
else lastdate = lag(deliv_date2);
datediff = deliv_date2-lastdate;
run;

proc sql;
CREATE table thesis.presc3 as
select patient,  reimburs_cat, source,  atc, AdminRoute, N_presc, count(N_presc) as Co_Pres, Center, deliv_date2, DDD,datediff 
From thesis.presc2
where datediff >= 7 
group by patient ;
quit;

Capture.PNG

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

Try something like this:

 

data temp;
set have; by patient atc notsorted;
lagDate = lag(date);
if first.atc then seq + 1;
else if intck("day", lagDate, date) > 7 then seq +1;
drop lagdate;
run;

data want;
do until(last.seq);
    set temp; by patient atc seq notsorted;
    newDDD = sum(newDDD, DDD);
    end;
drop seq DDD;
rename newDDD=DDD;
run;
PG

View solution in original post

7 REPLIES 7
Reeza
Super User

Note that I've edited your post to make your question more description. "help" is fairly generic and makes it hard to search for similar answers in the future. 

 

Please post your data as text, especially if you would like tested code as an answer. Currently, to work with your code or come up with a solution I'd have to type it out or mock up my own. Instructions on how include your data as a data step are here:

https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...

 

It also helps if you post what you would expect as output from your input data so that we can test solutions. 

 

Good Luck. 

 


@Yilikal_Tesfaye wrote:

 

 Hi,

   

I am working on the prescription dataset from a health organization.  the dataset contains the following variables in the snapshot below.  I want to count a number prescription per patient based on the following criteria, 

  1. If the deliv_date2 is less than seven and the same ATC code for the individual patient then set a number of prescription is equal to one and sum DDD otherwise count the number of prescription.

Thank you in advance!!

 

data thesis.presc2;
set thesis.presc;
by patient;
if first.patient then lastdate=.;
else lastdate = lag(deliv_date2);
datediff = deliv_date2-lastdate;
run;

proc sql;
CREATE table thesis.presc3 as
select patient,  reimburs_cat, source,  atc, AdminRoute, N_presc, count(N_presc) as Co_Pres, Center, deliv_date2, DDD,datediff 
From thesis.presc2
where datediff >= 7 
group by patient ;
quit;

Capture.PNG

 

 

 

 


 

Kurt_Bremser
Super User

Your use of the lag() function is incorrect:

if first.patient then lastdate=.;
else lastdate = lag(deliv_date2);

This will cause a wrong entry for lastdate in the second observation for a patient, as you will get the last deliv_date2 of the previous patient.

Never use lag() conditionally (unless you have a very specific reason for that skip).

Do it like that:

lastdate = lag(deliv_date2);
if first.patient then lastdate = .;
ballardw
Super User

@Yilikal_Tesfaye wrote:

 

 Hi,

   

I am working on the prescription dataset from a health organization.  the dataset contains the following variables in the snapshot below.  I want to count a number prescription per patient based on the following criteria, 

  1. If the deliv_date2 is less than seven and the same ATC code for the individual patient then set a number of prescription is equal to one and sum DDD otherwise count the number of prescription.

Thank you in advance!!

  


Incomplete specification for the highlighted bit. I think many of us will assume less than 7 days given that it is a date but what specific other date(s) is it to be compared with?

 

You may be interested to know there is a function DIF that works like LAG to do the difference of the current value from the previous. So you can use:

datediff = dif( deliv_date2);

Same note about conditional use from @Kurt_Bremser about LAG applies.

 

 

Yilikal_Tesfaye
Fluorite | Level 6

Dear Sir/ Madam,

 

 I want to compute the number prescription per patient.  I would like to use the following criteria count the prescription:
 1. If the deliv_date2 was within seven days per patient(i.e less than 7 days)  and the  ATC code similar then set the number of prescription equal to one and sum DDD.  otherwise, count the DDD per patient and set the count to the number of prescription.
 
  I tried using the following code but I didn't get what I want.
 
data thesis.presc2;
set thesis.presc;
by patient;
lastdate = lag(deliv_date2);
LAG_DDD = LAG(DDD);
if first.patient then lastdate = .;
datediff = deliv_date2-lastdate;
run;

data thesis.presc21;
set thesis.presc2;
by patient ;
If atc = lag(atc) and datediff < 7 then
Sum_DDD = sum(LAG_DDD,DDD); 
run;

Capture.PNG

PGStats
Opal | Level 21

Try something like this:

 

data temp;
set have; by patient atc notsorted;
lagDate = lag(date);
if first.atc then seq + 1;
else if intck("day", lagDate, date) > 7 then seq +1;
drop lagdate;
run;

data want;
do until(last.seq);
    set temp; by patient atc seq notsorted;
    newDDD = sum(newDDD, DDD);
    end;
drop seq DDD;
rename newDDD=DDD;
run;
PG
Kurt_Bremser
Super User

I merged the last two posts into the older thread concerning the same issue, as there were already several answers given.

To repeat myself (what I usually hat to do): if you supply data in text form, also include the code that reads that text into a SAS dataset.

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!

How to Concatenate Values

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.

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
  • 7 replies
  • 1569 views
  • 1 like
  • 5 in conversation