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.
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;
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;
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
Linlin
You are amazing. Thanks so much for the speedy reply!
I will try this out and let you know. Thanks again!
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!!
Hi,
I was wondering why Linlins method wouldnt work???
It should give you the result as well!!!
I dont know why???
Regards
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
Hi,
This can also be written as
if last.chart?????
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.