Summarizing and Adding a total column in the dataset

[Ranjeeta]

Hello 

everyone 

I have attached some dummy data 

I would like to add a total column into the dataset which is a count of the total visits per patient for all service types combined

And I would also like ti create a summary as displayed in the output

Appreciate any help

Regards

[PaigeMiller]

Most of us will not download Microsoft Office files, as they are security risks.

Please provide your example data in the text of your message, as DATA step code. You can show us a screen capture of the desired output.

[Ranjeeta]

data sasdata; 
  input Patient $ Admission $ Service_Date Service;
cards;
ABC   ABCD   77   5
ABC   ABCD   84   5
ABC   ABCD  100   5
ABC   ABCD  105   5
ABC   ABCD  170   6
ABC   ABCD  201   6
ABC   ABCD   82   6
ABC   ABCD  181   7
ABC   ABCD  126   7
ABC   ABCD  230   7
DEF   DEFA  77    7
DEF   DEFA  84    8
DEF   DEFA  100   13
DEF   DEFA  105   6
DEF   DEFA  170   8
DEF   DEFA  201  13
DEF   DEFA  82    5
DEF   DEFA  181   5
DEF   DEFA  126   5
DEF   DEFA  230   5
;
run;

I need to add a column to the above dataset that also calculates the total visits per patient 

ABC   10

DEF   10

[PaigeMiller]

---

@Ranjeeta wrote:

I need to add a column to the above dataset that also calculates the total visits per patient 

---

Each record in the data set is considered a visit?

[Ranjeeta]

Yes
every unique combination of service date and admission is considered a visit

[Reeza · Accepted solution]

proc sql;
create table want as

/*By Service level calculations*/
select put(service, z8.) as Service, count(*) as total_visits, count(distinct patient) as Patients, count(distinct admission) as Admissions
from have
group by service

union

/*Total calculations*/
select "Total" as Service, count(*) as Total_Visits, count(distinct Patient) as Patients, count(distinct admission) as Admissions
from have;

quit;

Note I converted Service to a character variable, if it's already a character variable you can drop that but if you want a total line that's how you do it. You cannot do a total using PROC MEANS because it won't work correctly for distinct counts.

---

@Ranjeeta wrote:

data sasdata; 
  input Patient $ Admission $ Service_Date Service;
cards;
ABC   ABCD   77   5
ABC   ABCD   84   5
ABC   ABCD  100   5
ABC   ABCD  105   5
ABC   ABCD  170   6
ABC   ABCD  201   6
ABC   ABCD   82   6
ABC   ABCD  181   7
ABC   ABCD  126   7
ABC   ABCD  230   7
DEF   DEFA  77    7
DEF   DEFA  84    8
DEF   DEFA  100   13
DEF   DEFA  105   6
DEF   DEFA  170   8
DEF   DEFA  201  13
DEF   DEFA  82    5
DEF   DEFA  181   5
DEF   DEFA  126   5
DEF   DEFA  230   5
;
run;

I need to add a column to the above dataset that also calculates the total visits per patient 

ABC   10

DEF   10

---

[Ranjeeta]

Many thanks This works 

Apologies i should have indicated this earlier

But i also have to seperate out the  admisions that had total visits/admission  ❤️ and greater than or equal to 3

How would i do that?

[Reeza]

That's a different question entirely. Please show your input and expected output, preferably in a new question.

[Reeza]

What are the rules for calculating admissions and patients?
Please ensure this accurately represents your actual data so it will work as designed. 

Additionally, if you can provide data as a data step it helps others generate working solutions faster.

Here are instructions on how to provide sample data as a data step:
https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...

---

@Ranjeeta wrote:

Hello 

everyone 

I have attached some dummy data 

I would like to add a total column into the dataset which is a count of the total visits per patient for all service types combined

And I would also like ti create a summary as displayed in the output

Appreciate any help

Regards

---