Counting within clustered observations

[ImSame]

Hi all,

I have the following dataset,

RAW DATA

Var A	Agency #	ID #	Admission #	Service #	Service Type	Var Z
AAA	555	1	1	1	Phamacotherapy	YYY
AAA	555	1	1	2	Counseling	YYY
AAA	555	1	1	3	Counseling	YYY
BBB	999	10	5	1	Counseling	ZZZ
BBB	999	10	5	2	Rehabilitative Care	ZZZ

This dataset contains services utilized as part of admissions. Each admission is defined as the combination of Agency #, ID  # and Admission #. I want to count the services utilized as part of each admission, and ascertain if pharmacotherapy services were utilized as a part of the admission. The desired output should be as follows,

DESIRED OUTPUT

Var A	Agency #	ID #	Admission #	Service #	Service Type	Var Z	Total # of Services Utilized in Admission	Pharmacotherapy Part of Admission
AAA	555	1	1	1	Phamacotherapy	YYY	3	Yes
AAA	555	1	1	2	Counseling	YYY	3	Yes
AAA	555	1	1	3	Counseling	YYY	3	Yes
BBB	999	10	5	1	Counseling	ZZZ	2	No
BBB	999	10	5	2	Rehabilitative Care	ZZZ	2	No

Any help would be much appreciated.

Thank you.

[mkeintz · Accepted solution]

Since you do not provide a problem with variable names specified, the below is basically a logic outline of two phases:

1. Read and count all the rec's for a given admission.  Also check for presence of any pharmacotherepy services.
2. With the count established (var TOTALSRV) and the PHARMPART var set to "Yes" or "No", reread and output each record.

data want;

  /* Read and count all recs for an admission */

  do totalsrv=1 by 1 until (last.admission);

    set have;

    by agency id admission;

    if svt_type='Pharmacotherapy' then pharmpart='Yes';

  end;

  if Pharmpart^='Yes' then Pharmpart='No';

  /* Reread and output */

  do until (last.admission);

    set have;

    by agency id admission;

    output;

  end;

run;

Notes:

• This double-do-until-last technique is a common approach to getting group values assigned to individual group member records
• It depends on the data set HAVE being sorted by the group vars (agency/id/admission in this case).