## OccurNo and TotalOccur Calculations

Solved
Regular Contributor
Posts: 177

# OccurNo and TotalOccur Calculations

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.

Accepted Solutions
Solution
‎05-30-2013 02:00 PM
Super User
Posts: 23,754

## Re: OccurNo and TotalOccur Calculations

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;

All Replies
Solution
‎05-30-2013 02:00 PM
Super User
Posts: 23,754

## Re: OccurNo and TotalOccur Calculations

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;

Super Contributor
Posts: 1,636

## Re: OccurNo and TotalOccur Calculations

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

Regular Contributor
Posts: 177

## Re: OccurNo and TotalOccur Calculations

Linlin

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

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

Regular Contributor
Posts: 177

## Re: OccurNo and TotalOccur Calculations

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

Super Contributor
Posts: 1,041

## Re: OccurNo and TotalOccur Calculations

Hi,

I was wondering why Linlins method wouldnt work???

It should give you the result as well!!!

I dont know why???

Regards

Super Contributor
Posts: 1,636

## Re: OccurNo and TotalOccur Calculations

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

Super Contributor
Posts: 1,041

## Re: OccurNo and TotalOccur Calculations

Hi,

This can also be written as

if last.chart?????

🔒 This topic is solved and locked.