topic OccurNo and TotalOccur Calculations in SAS Procedures

OccurNo and TotalOccur Calculations

shellp55 — Thu, 30 May 2013 17:49:53 GMT

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.

Re: OccurNo and TotalOccur Calculations

Reeza — Thu, 30 May 2013 18:00:05 GMT

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;

Re: OccurNo and TotalOccur Calculations

Linlin — Thu, 30 May 2013 18:05:50 GMT

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

Re: OccurNo and TotalOccur Calculations

shellp55 — Thu, 30 May 2013 18:09:12 GMT

Linlin

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

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

Re: OccurNo and TotalOccur Calculations

shellp55 — Thu, 30 May 2013 18:32:51 GMT

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!!

Re: OccurNo and TotalOccur Calculations

robertrao — Thu, 30 May 2013 18:59:20 GMT

Hi,

I was wondering why Linlins method wouldnt work???

It should give you the result as well!!!

I dont know why???

Regards

Re: OccurNo and TotalOccur Calculations

Linlin — Thu, 30 May 2013 19:00:01 GMT

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

Re: OccurNo and TotalOccur Calculations

robertrao — Fri, 31 May 2013 16:46:56 GMT

Hi,

This can also be written as

if last.chart?????