topic Re: Using first. and last. statements to count a sub-variable in SAS Programming

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

Newtrix — Thu, 30 Jan 2025 20:50:35 GMT

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;

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

quickbluefish — Thu, 30 Jan 2025 20:59:11 GMT

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'

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

Newtrix — Thu, 30 Jan 2025 21:05:36 GMT

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

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

quickbluefish — Thu, 30 Jan 2025 21:11:06 GMT

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;

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

ballardw — Thu, 30 Jan 2025 21:29:21 GMT

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.

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

Tom — Thu, 30 Jan 2025 21:31:37 GMT

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;

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

Kurt_Bremser — Fri, 31 Jan 2025 08:07:08 GMT

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;