BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
shellp55
Quartz | Level 8


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.


1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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;

View solution in original post

7 REPLIES 7
Reeza
Super User

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
Lapis Lazuli | Level 10

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
Quartz | Level 8

Linlin

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

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

shellp55
Quartz | Level 8

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
Quartz | Level 8

Hi,

I was wondering why Linlins method wouldnt work???

It should give you the result as well!!!

I dont know why???

Regards

Linlin
Lapis Lazuli | Level 10

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
Quartz | Level 8

Hi,

This can also be written as

if last.chart?????

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 805 views
  • 1 like
  • 4 in conversation