DATA Step, Macro, Functions and more

Conditionally count occurrences

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

Conditionally count occurrences

[ Edited ]

 

 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

 

 

 

 


Accepted Solutions
Solution
‎04-08-2018 07:32 PM
Esteemed Advisor
Posts: 5,540

Re: Conditionally count occurrences

Posted in reply to Yilikal_Tesfaye

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


All Replies
Super User
Posts: 23,771

Re: Conditionally count occurrences

Posted in reply to Yilikal_Tesfaye

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

 

 

 

 


 

Super User
Posts: 10,278

Re: Conditionally count occurrences

Posted in reply to Yilikal_Tesfaye

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 = .;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super User
Posts: 10,278

Re: Conditionally count occurrences

Posted in reply to Yilikal_Tesfaye

PS if you attach a text file, also supply the code that reads it into SAS, so we get an exact replica of your dataset.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super User
Posts: 13,583

Re: Conditionally count occurrences

Posted in reply to Yilikal_Tesfaye

@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 @KurtBremser about LAG applies.

 

 

New Contributor
Posts: 4

Conditionally count occurrences

Posted in reply to Yilikal_Tesfaye

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

Solution
‎04-08-2018 07:32 PM
Esteemed Advisor
Posts: 5,540

Re: Conditionally count occurrences

Posted in reply to Yilikal_Tesfaye

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
Super User
Posts: 10,278

Re: Conditionally count occurrences

Posted in reply to Yilikal_Tesfaye

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 273 views
  • 1 like
  • 5 in conversation