DATA Step, Macro, Functions and more

Counting within clustered observations

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 17
Accepted Solution

Counting within clustered observations

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.


Accepted Solutions
Solution
‎07-16-2017 01:46 PM
Trusted Advisor
Posts: 1,022

Re: Counting within clustered observations

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).

View solution in original post


All Replies
Solution
‎07-16-2017 01:46 PM
Trusted Advisor
Posts: 1,022

Re: Counting within clustered observations

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).
☑ This topic is solved.

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

Discussion stats
  • 1 reply
  • 110 views
  • 0 likes
  • 2 in conversation