318.HOSPITAL SERVICE QUALITY ANALYSIS DATASET CREATION WITH DATE DERIVATIONS | SERVICE SCORE AUTOMATION USING MACROS | PROC SQL | PROC MEANS | PROC CORR | PROC FORMAT | PROC SGPLOT

HOSPITAL SERVICE QUALITY ANALYSIS DATASET CREATION WITH DATE DERIVATIONS | SERVICE SCORE AUTOMATION USING MACROS | PROC SQL | PROC MEANS | PROC CORR | PROC FORMAT | PROC SGPLOT

 options nocenter;

1. CREATE HOSPITAL SERVICE QUALITY DATASET USING PROC SQL

proc sql;

  create table work.hospital_raw as

  select 

    "CityCare General"  as Hospital_Name length=30,

    "Hyderabad"          as City length=20,

    250                  as Bed_Count,

    80                   as Doctor_Count,

    45                   as Avg_Wait_Time,

    2.1                  as Mortality_Rate,

    88                   as Patient_Satisfaction,

    '15JAN2018'd         as Accreditation_Date,

    '10OCT2024'd         as Survey_Date

  from sashelp.class(obs=1)

  union all

  select 

    "Sunrise Multispecialty",

    "Bengaluru",

    320,

    120,

    30,

    1.5,

    92,

    '01JUL2016'd,

    '20SEP2024'd

  from sashelp.class(obs=1)

  union all

  select 

    "GreenLeaf Hospital",

    "Chennai",

    180,

    60,

    55,

    2.8,

    81,

    '10MAR2019'd,

    '05AUG2024'd

  from sashelp.class(obs=1)

  union all

  select 

    "RiverView Medical",

    "Mumbai",

    400,

    150,

    35,

    1.9,

    90,

    '22NOV2015'd,

    '12NOV2024'd

  from sashelp.class(obs=1)

  union all

  select 

    "Lotus Heart Institute",

    "Pune",

    220,

    90,

    40,

    2.3,

    86,

    '05MAY2017'd,

    '28OCT2024'd

  from sashelp.class(obs=1)

  union all

  select 

    "MetroLife Care",

    "Delhi",

    520,

    210,

    50,

    2.6,

    79,

    '18DEC2014'd,

    '15SEP2024'd

  from sashelp.class(obs=1)

  union all

  select 

    "Harmony Health Center",

    "Kolkata",

    260,

    100,

    42,

    2.0,

    84,

    '09APR2018'd,

    '08OCT2024'd

  from sashelp.class(obs=1)

  union all

  select 

    "SilverLine Hospital",

    "Hyderabad",

    150,

    55,

    38,

    1.7,

    89,

    '01JAN2020'd,

    '25AUG2024'd

  from sashelp.class(obs=1)

  union all

  select 

    "Skyline Supercare",

    "Bengaluru",

    300,

    110,

    33,

    1.6,

    91,

    '11FEB2016'd,

    '05NOV2024'd

  from sashelp.class(obs=1)

  union all

  select 

    "Coastal Life Hospital",

    "Visakhapatnam",

    190,

    70,

    47,

    2.4,

    83,

    '20SEP2019'd,

    '30SEP2024'd

  from sashelp.class(obs=1)

  union all

  select 

    "TruHealth Specialty",

    "Chennai",

    210,

    85,

    52,

    2.9,

    77,

    '14AUG2017'd,

    '18OCT2024'd

  from sashelp.class(obs=1)

  union all

  select 

    "EverCare Clinic",

    "Mumbai",

    130,

    45,

    28,

    1.3,

    94,

    '02FEB2021'd,

    '01NOV2024'd

  from sashelp.class(obs=1)

  ;

quit;

proc print data=work.hospital_raw;

run;

OUTPUT:

Obs	Hospital_Name	City	Bed_Count	Doctor_Count	Avg_Wait_Time	Mortality_Rate	Patient_Satisfaction	Accreditation_Date	Survey_Date
1	CityCare General	Hyderabad	250	80	45	2.1	88	21199	23659
2	Sunrise Multispecialty	Bengaluru	320	120	30	1.5	92	20636	23639
3	GreenLeaf Hospital	Chennai	180	60	55	2.8	81	21618	23593
4	RiverView Medical	Mumbai	400	150	35	1.9	90	20414	23692
5	Lotus Heart Institute	Pune	220	90	40	2.3	86	20944	23677
6	MetroLife Care	Delhi	520	210	50	2.6	79	20075	23634
7	Harmony Health Center	Kolkata	260	100	42	2.0	84	21283	23657
8	SilverLine Hospital	Hyderabad	150	55	38	1.7	89	21915	23613
9	Skyline Supercare	Bengaluru	300	110	33	1.6	91	20495	23685
10	Coastal Life Hospital	Visakhapatnam	190	70	47	2.4	83	21812	23649
11	TruHealth Specialty	Chennai	210	85	52	2.9	77	21045	23667
12	EverCare Clinic	Mumbai	130	45	28	1.3	94	22313	23681

2. ADD DATE-BASED DERIVED VARIABLES (INTCK, INTNX)

data work.hospital_final;

  set work.hospital_raw;

  /* Format the date variables for readability */

  format Accreditation_Date Survey_Date Next_Survey_Due date9.;

  /* Years since accreditation using INTCK */

  Years_Since_Accred = intck('year', Accreditation_Date, Survey_Date, 'c');

  /* Next survey due exactly 1 year after current survey using INTNX */

  Next_Survey_Due    = intnx('year', Survey_Date, 1, 'same');

  /* Days between accreditation and latest survey */

  Days_Since_Accred  = intck('day', Accreditation_Date, Survey_Date);

run;

proc print data=work.hospital_final;

run;

OUTPUT:

Obs	Hospital_Name	City	Bed_Count	Doctor_Count	Avg_Wait_Time	Mortality_Rate	Patient_Satisfaction	Accreditation_Date	Survey_Date	Next_Survey_Due	Years_Since_Accred	Days_Since_Accred
1	CityCare General	Hyderabad	250	80	45	2.1	88	15JAN2018	10OCT2024	10OCT2025	6	2460
2	Sunrise Multispecialty	Bengaluru	320	120	30	1.5	92	01JUL2016	20SEP2024	20SEP2025	8	3003
3	GreenLeaf Hospital	Chennai	180	60	55	2.8	81	10MAR2019	05AUG2024	05AUG2025	5	1975
4	RiverView Medical	Mumbai	400	150	35	1.9	90	22NOV2015	12NOV2024	12NOV2025	8	3278
5	Lotus Heart Institute	Pune	220	90	40	2.3	86	05MAY2017	28OCT2024	28OCT2025	7	2733
6	MetroLife Care	Delhi	520	210	50	2.6	79	18DEC2014	15SEP2024	15SEP2025	9	3559
7	Harmony Health Center	Kolkata	260	100	42	2.0	84	09APR2018	08OCT2024	08OCT2025	6	2374
8	SilverLine Hospital	Hyderabad	150	55	38	1.7	89	01JAN2020	25AUG2024	25AUG2025	4	1698
9	Skyline Supercare	Bengaluru	300	110	33	1.6	91	11FEB2016	05NOV2024	05NOV2025	8	3190
10	Coastal Life Hospital	Visakhapatnam	190	70	47	2.4	83	20SEP2019	30SEP2024	30SEP2025	5	1837
11	TruHealth Specialty	Chennai	210	85	52	2.9	77	14AUG2017	18OCT2024	18OCT2025	7	2622
12	EverCare Clinic	Mumbai	130	45	28	1.3	94	02FEB2021	01NOV2024	01NOV2025	3	1368

3. DEFINE FORMATS FOR REPORTING USING PROC FORMAT

proc format;

  /* Patient satisfaction categories */

  value satsf

    0 - 69  = 'Low'

    70 - 84 = 'Medium'

    85 - 100= 'High';

LOG:

NOTE: Format SATSF has been output.

  /* Simple bed-count category */

  value bedcat

    0   - 199 = 'Small'

    200 - 349 = 'Medium'

    350 - high= 'Large';

run;

LOG:

NOTE: Format BEDCAT has been output.

4. MACRO FOR SERVICE-SCORE AUTOMATION

%macro calc_service_score(indata=, outdata=, 

                          w_wait=0.30, 

                          w_mort=0.30, 

                          w_satisf=0.40);

  data &outdata;

    set &indata;

    /* Assume realistic OPD waits < 100 minutes: lower wait is better */

    Wait_Score = 100 - Avg_Wait_Time;   

    /* Mortality rate assumed between 0 and 10%; penalise higher values */

    Mort_Score = 100 - (Mortality_Rate * 10);

    /* Patient satisfaction already a 0–100 type metric */

    Sat_Score  = Patient_Satisfaction;

    /* Weighted composite service score */

    Service_Score = (&w_wait  * Wait_Score) +

                    (&w_mort  * Mort_Score) +

                    (&w_satisf* Sat_Score);

    format Service_Score 6.2 Patient_Satisfaction satsf. Bed_Count bedcat.;

  run;

  proc print data=&outdata;

  run;

%mend calc_service_score;

%calc_service_score(indata=work.hospital_final,

                    outdata=work.hospital_scored,

                    w_wait=0.30,

                    w_mort=0.30,

                    w_satisf=0.40);

OUTPUT:

Obs	Hospital_Name	City	Bed_Count	Doctor_Count	Avg_Wait_Time	Mortality_Rate	Patient_Satisfaction	Accreditation_Date	Survey_Date	Next_Survey_Due	Years_Since_Accred	Days_Since_Accred	Wait_Score	Mort_Score	Sat_Score	Service_Score
1	CityCare General	Hyderabad	Medium	80	45	2.1	High	15JAN2018	10OCT2024	10OCT2025	6	2460	55	79	88	75.40
2	Sunrise Multispecialty	Bengaluru	Medium	120	30	1.5	High	01JUL2016	20SEP2024	20SEP2025	8	3003	70	85	92	83.30
3	GreenLeaf Hospital	Chennai	Small	60	55	2.8	Medium	10MAR2019	05AUG2024	05AUG2025	5	1975	45	72	81	67.50
4	RiverView Medical	Mumbai	Large	150	35	1.9	High	22NOV2015	12NOV2024	12NOV2025	8	3278	65	81	90	79.80
5	Lotus Heart Institute	Pune	Medium	90	40	2.3	High	05MAY2017	28OCT2024	28OCT2025	7	2733	60	77	86	75.50
6	MetroLife Care	Delhi	Large	210	50	2.6	Medium	18DEC2014	15SEP2024	15SEP2025	9	3559	50	74	79	68.80
7	Harmony Health Center	Kolkata	Medium	100	42	2.0	Medium	09APR2018	08OCT2024	08OCT2025	6	2374	58	80	84	75.00
8	SilverLine Hospital	Hyderabad	Small	55	38	1.7	High	01JAN2020	25AUG2024	25AUG2025	4	1698	62	83	89	79.10
9	Skyline Supercare	Bengaluru	Medium	110	33	1.6	High	11FEB2016	05NOV2024	05NOV2025	8	3190	67	84	91	81.70
10	Coastal Life Hospital	Visakhapatnam	Small	70	47	2.4	Medium	20SEP2019	30SEP2024	30SEP2025	5	1837	53	76	83	71.90
11	TruHealth Specialty	Chennai	Medium	85	52	2.9	Medium	14AUG2017	18OCT2024	18OCT2025	7	2622	48	71	77	66.50
12	EverCare Clinic	Mumbai	Small	45	28	1.3	High	02FEB2021	01NOV2024	01NOV2025	3	1368	72	87	94	85.30

5. DESCRIPTIVE STATISTICS USING PROC MEANS

proc means data=work.hospital_scored

           n mean std min max maxdec=2;

  class City;

  var Bed_Count Doctor_Count Avg_Wait_Time Mortality_Rate 

      Patient_Satisfaction Service_Score Years_Since_Accred;

run;

OUTPUT:

The MEANS Procedure

City	N Obs	Variable	N	Mean	Std Dev	Minimum	Maximum
Bengaluru	2	Bed_Count Doctor_Count Avg_Wait_Time Mortality_Rate Patient_Satisfaction Service_Score Years_Since_Accred	2 2 2 2 2 2 2	310.00 115.00 31.50 1.55 91.50 82.50 8.00	14.14 7.07 2.12 0.07 0.71 1.13 0.00	300.00 110.00 30.00 1.50 91.00 81.70 8.00	320.00 120.00 33.00 1.60 92.00 83.30 8.00
Chennai	2	Bed_Count Doctor_Count Avg_Wait_Time Mortality_Rate Patient_Satisfaction Service_Score Years_Since_Accred	2 2 2 2 2 2 2	195.00 72.50 53.50 2.85 79.00 67.00 6.00	21.21 17.68 2.12 0.07 2.83 0.71 1.41	180.00 60.00 52.00 2.80 77.00 66.50 5.00	210.00 85.00 55.00 2.90 81.00 67.50 7.00
Delhi	1	Bed_Count Doctor_Count Avg_Wait_Time Mortality_Rate Patient_Satisfaction Service_Score Years_Since_Accred	1 1 1 1 1 1 1	520.00 210.00 50.00 2.60 79.00 68.80 9.00	. . . . . . .	520.00 210.00 50.00 2.60 79.00 68.80 9.00	520.00 210.00 50.00 2.60 79.00 68.80 9.00
Hyderabad	2	Bed_Count Doctor_Count Avg_Wait_Time Mortality_Rate Patient_Satisfaction Service_Score Years_Since_Accred	2 2 2 2 2 2 2	200.00 67.50 41.50 1.90 88.50 77.25 5.00	70.71 17.68 4.95 0.28 0.71 2.62 1.41	150.00 55.00 38.00 1.70 88.00 75.40 4.00	250.00 80.00 45.00 2.10 89.00 79.10 6.00
Kolkata	1	Bed_Count Doctor_Count Avg_Wait_Time Mortality_Rate Patient_Satisfaction Service_Score Years_Since_Accred	1 1 1 1 1 1 1	260.00 100.00 42.00 2.00 84.00 75.00 6.00	. . . . . . .	260.00 100.00 42.00 2.00 84.00 75.00 6.00	260.00 100.00 42.00 2.00 84.00 75.00 6.00
Mumbai	2	Bed_Count Doctor_Count Avg_Wait_Time Mortality_Rate Patient_Satisfaction Service_Score Years_Since_Accred	2 2 2 2 2 2 2	265.00 97.50 31.50 1.60 92.00 82.55 5.50	190.92 74.25 4.95 0.42 2.83 3.89 3.54	130.00 45.00 28.00 1.30 90.00 79.80 3.00	400.00 150.00 35.00 1.90 94.00 85.30 8.00
Pune	1	Bed_Count Doctor_Count Avg_Wait_Time Mortality_Rate Patient_Satisfaction Service_Score Years_Since_Accred	1 1 1 1 1 1 1	220.00 90.00 40.00 2.30 86.00 75.50 7.00	. . . . . . .	220.00 90.00 40.00 2.30 86.00 75.50 7.00	220.00 90.00 40.00 2.30 86.00 75.50 7.00
Visakhapatnam	1	Bed_Count Doctor_Count Avg_Wait_Time Mortality_Rate Patient_Satisfaction Service_Score Years_Since_Accred	1 1 1 1 1 1 1	190.00 70.00 47.00 2.40 83.00 71.90 5.00	. . . . . . .	190.00 70.00 47.00 2.40 83.00 71.90 5.00	190.00 70.00 47.00 2.40 83.00 71.90 5.00

6. CORRELATION ANALYSIS USING PROC CORR

proc corr data=work.hospital_scored

          nosimple

          plots=none;

  var Bed_Count Doctor_Count Avg_Wait_Time Mortality_Rate 

      Patient_Satisfaction Service_Score Years_Since_Accred;

run;

OUTPUT:

The CORR Procedure

7 Variables:	Bed_Count Doctor_Count Avg_Wait_Time Mortality_Rate Patient_Satisfaction Service_Score Years_Since_Accred

Pearson Correlation Coefficients, N = 12 Prob > |r| under H0: Rho=0
	Bed_Count	Doctor_Count	Avg_Wait_Time	Mortality_Rate	Patient_Satisfaction	Service_Score	Years_Since_Accred
Bed_Count	1.00000	0.99056 <.0001	0.04148 0.8982	0.10836 0.7375	-0.15547 0.6295	-0.09845 0.7608	0.85997 0.0003
Doctor_Count	0.99056 <.0001	1.00000	0.06091 0.8508	0.14741 0.6475	-0.21388 0.5045	-0.13665 0.6719	0.86329 0.0003
Avg_Wait_Time	0.04148 0.8982	0.06091 0.8508	1.00000	0.95582 <.0001	-0.93218 <.0001	-0.98414 <.0001	0.06230 0.8475
Mortality_Rate	0.10836 0.7375	0.14741 0.6475	0.95582 <.0001	1.00000	-0.95078 <.0001	-0.98295 <.0001	0.20446 0.5239
Patient_Satisfaction	-0.15547 0.6295	-0.21388 0.5045	-0.93218 <.0001	-0.95078 <.0001	1.00000	0.97773 <.0001	-0.20871 0.5151
Service_Score	-0.09845 0.7608	-0.13665 0.6719	-0.98414 <.0001	-0.98295 <.0001	0.97773 <.0001	1.00000	-0.14969 0.6424
Years_Since_Accred	0.85997 0.0003	0.86329 0.0003	0.06230 0.8475	0.20446 0.5239	-0.20871 0.5151	-0.14969 0.6424	1.00000

7. VISUALIZATION USING PROC SGPLOT

proc sgplot data=work.hospital_scored;

  title "Hospital Service Score vs Average Wait Time by City";

  scatter x=Avg_Wait_Time y=Service_Score / group=City datalabel=Hospital_Name;

  xaxis label="Average Wait Time (minutes)";

  yaxis label="Service Score";

run;

OUTPUT:

/*Bar chart of service score by hospital */

proc sgplot data=work.hospital_scored;

  title "Service Score by Hospital";

  vbar Hospital_Name / response=Service_Score datalabel;

  xaxis display=(nolabel);

  yaxis label="Service Score";

run;

OUTPUT:

To Visit My Previous Online Streaming Flatform Dataset:Click Here

To Visit My Previous Statistical Evaluation Of Clinical Trials:Click Here

To Visit My Previous Unlocking Retail Insights Dataset:Click Here

To Visit My Previous Sas Interview Questions-1:Click Here  

Follow Us On : 

 

--- FOLLOW OUR BLOG FOR MORE INFORMATION.

--->PLEASE DO COMMENTS AND SHARE OUR BLOG.