OccurNo and TotalOccur Calculations

[shellp55]

Hello

I am using Base SAS 9.3.

I have a table where one line of data is one patient visit.  I have transposed the diagnoses from wide to long format successfully, including an occurrence number per hospital visit.  But how do I get a total diagnosis occurrence?  What I want is for it to look like below but I have all that I need except TotalOccur:

Chart	Acct	Dx	DxOccur	TotalOccur
1234	5678	A048	1	3
1234	5678	I228	2	3
1234	5678	J18	3	3

Thanks very much.

[Reeza · Accepted solution]

It looks like you want to add in a total count of the number of events.

Generally there's several ways to do this, but I find a SQL approach the easiest, because it's one 'coding' step even if its two passes through the data.

proc sql;

create table want as

select *, count (*) as TotalOccur

from have

group by chart, acct;

quit;

[Linlin]

example:

data have;

input id dxoccur;

cards;

1 1

1 2

1 3

2 1

2 2

2 3

2 4

;

data temp;

set have;

by id;

if last.id;

data want;

   merge have temp(rename=dxoccur=totaloccur);

   by id;

proc print;run;

Obs    id    dxoccur    totaloccur

1      1       1            3

2      1       2            3

3      1       3            3

4      2       1            4

5      2       2            4

6      2       3            4

7      2       4            4

[shellp55]

Linlin

You are amazing.  Thanks so much for the speedy reply!

I will try this out and let you know.  Thanks again!

[shellp55]

Sorry Linlin but I couldn't get your version to work but probably because many more "by" statements.  Reeza, sorry I didn't acknowledge you at first because I didn't realize two people had responded within minutes of my query!  I used Reeza's technique which worked amazingly well.

Thanks so much to both of you!!

[robertrao]

Hi,

I was wondering why Linlins method wouldnt work???

It should give you the result as well!!!

I dont know why???

Regards

[Linlin]

it should work. but not as efficient as Reeza's code:smileysilly:.

data have;

input chart acct dx $ dxoccur;

cards;

1234 5678 qqq 1

1234 5678 bbb 2

1234 5678 ccc 3

2234 5678 qqq 1

2234 5678 bbb 2

2234 5678 ccc 3

2234 5678 ccc 4

;

data temp (keep=chart acct dxoccur);

set have;

by chart acct;

if last.acct;

data want;

   merge have temp(rename=(dxoccur=totaloccur));

   by chart acct;

proc print;run;

Obs    chart    acct    dx     dxoccur    totaloccur

1      1234    5678    qqq       1            3

2      1234    5678    bbb       2            3

3      1234    5678    ccc       3            3

4      2234    5678    qqq       1            4

5      2234    5678    bbb       2            4

6      2234    5678    ccc       3            4

7      2234    5678    ccc       4            4

[robertrao]

Hi,

This can also be written as

if last.chart?????