topic Re: Summarizing and Adding a total column in the dataset in SAS Programming

Summarizing and Adding a total column in the dataset

Ranjeeta — Fri, 13 Mar 2020 15:32:50 GMT

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

Re: Summarizing and Adding a total column in the dataset

PaigeMiller — Fri, 13 Mar 2020 15:36:35 GMT

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.

Re: Summarizing and Adding a total column in the dataset

Reeza — Fri, 13 Mar 2020 15:38:34 GMT

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-data-AKA-generate/ta-p/258712

@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

Re: Summarizing and Adding a total column in the dataset

Ranjeeta — Fri, 13 Mar 2020 15:39:47 GMT

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

Re: Summarizing and Adding a total column in the dataset

PaigeMiller — Fri, 13 Mar 2020 15:42:17 GMT

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

Re: Summarizing and Adding a total column in the dataset

Ranjeeta — Fri, 13 Mar 2020 15:45:05 GMT

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

Re: Summarizing and Adding a total column in the dataset

Reeza — Fri, 13 Mar 2020 15:53:43 GMT

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

Re: Summarizing and Adding a total column in the dataset

Ranjeeta — Fri, 13 Mar 2020 16:26:00 GMT

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?

Re: Summarizing and Adding a total column in the dataset

Reeza — Fri, 13 Mar 2020 16:45:31 GMT

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