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?????
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.
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.