Using first. and last. statements to count a sub-variable

[Newtrix]

Hello,

I'm trying to count total aspirin dosages per patient in a dataset for which each patient has one entry per day.  Code is below. As you can probably gather, I'm getting counts of 1 in each "count' cell rather than an incremental total for each patient. Any help you might provide would be appreciated. Thanks.

DATA Count;
SET Medications;
BY Patient;
IF First.Patient THEN DO;
IF Medication = 'Aspirin' THEN DO;
Count = 0;
Count + 1; IF LAST.SubjectNumber; END; END;
RUN;

[quickbluefish]

This is one of those things where proper indentation really helps.  Though if you're just trying to get total number of aspirin administrations by person, you can just do this.

DATA Count;
SET Medications;
BY Patient;
IF First.Patient THEN count=0;
count+(medication='Aspirin');
if last.patient then output;
RUN;

 ...that said, it's not clear to me from your code / description what the difference is between 'Patient' and 'SubjectNumber'

[Newtrix]

Yeah, I was trying to clarify the code and missed a spot. SubjectNumber should be Patient. Each patient has multiple records, one per day. I'm trying to count the days during which each patient was given aspirin. The dataset looks something like what I have below. Thanks.

Patient  Date  Medication

1              Jan 1     Aspirin

1              Jan 2

1              Jan 3    Aspirin

2             Jan 5    

2             Jan 6    Aspirin

2             Jan 7 

3             Jan 10  Aspirin

3             Jan 11    Aspirin

3             Jan 12

[quickbluefish]

Yes, you can use the code above I sent, or one of many other ways, e.g., 

proc sql;
create table want as
select patient, sum(medication='Aspirin') as count
from have
group by patient;
quit;

[ballardw]

If your observations are "once per day" and you want to count days then count of observations should work. Which quite means that Proc Freq is likely to be viable.

You haven't stated if you actually need a data set or not as output though.

Proc freq data=medications;
   tables Patient*medication /list nopercent nocum;
run;

If you might have other values in the medication variable then restrict to counting Aspirin values only with a where clause

Proc freq data=medications;
   where medication='Aspirin';
   tables Patient*medication /list nopercent nocum;
run;

OUT option on the Tables statement would create a data set

As an aside: "dates" without years are a poor choice.

[Tom]

If you have more than one Aspirin record per day you might need to include DATE in your BY statement.

data want;
  set have;
  by patient date;
  retain count any;
  if first.patient then count=0;
  if first.date then any=0;
  if medication='Aspirin' then any=1;
  if last.date then count+any;
  if last.patient;
  keep patient count;
run;
        

[Kurt_Bremser]

Writing ugly spaghetti code like this is a recipe for problems.

DATA Count;
SET Medications;
BY Patient;
IF First.Patient THEN DO;
  IF Medication = 'Aspirin' THEN DO;
    Count = 0;
    Count + 1; 
    IF LAST.SubjectNumber;
  END;
END;
RUN;

Now you can clearly see that the increment is part of the FIRST. block and therefore executed only once per patient.

You also used a variable not included in the BY for your LAST.

data count;
set medications;
by patient;
if first.patient then count = 0;
if medication = 'Aspirin' then count + 1;
if last.patient;
run;