topic Re: RAG (RED AMBER GREEN) rating on a proc sql cross tab in SAS Procedures

RAG (RED AMBER GREEN) rating on a proc sql cross tab

Abz_17 — Thu, 19 Mar 2020 12:28:56 GMT

Hi,

I have produced the table below and wanted to RAG rate the data against the first column e.g. if 17-19 is >Overall DNA then the section to be red, if 17-19 <Overall DNA then it would be green. Is this possible? Can someone please advise? Thanks

/*Ageband Breakdown*/
proc sql;
create table DNA_Age as 
     select unique sesdate_d as Session_Date, 
round (Count (perid),1) as Booked_Appointments,
           round (Count (case when appstate='D' then perid end),1) as DNA_Appointments
		   round (Count (case when Age_Band='17-19' then perid end),1) as Booked_17_19,
           round (Count (case when appstate= 'D' and Age_Band='17-19' then perid end),1) as DNA_17_19,
		   round (Count (case when Age_Band='20-29' then perid end),1) as Booked_20_29,
           round (Count (case when appstate= 'D' and Age_Band='20-29' then perid end),1) as DNA_20_29,
		   round (Count (case when Age_Band='30-39' then perid end),1) as Booked_30_39,
           round (Count (case when appstate= 'D' and Age_Band='30-39' then perid end),1) as DNA_30_39,
		   round (Count (case when Age_Band='40-49' then perid end),1) as Booked_40_49,
           round (Count (case when appstate= 'D' and Age_Band='40-49' then perid end),1) as DNA_40_49,
		   round (Count (case when Age_Band='50-59' then perid end),1) as Booked_50_59,
           round (Count (case when appstate= 'D' and Age_Band='50-59' then perid end),1) as DNA_50_59,
		   round (Count (case when Age_Band='60-69' then perid end),1) as Booked_60_69,
           round (Count (case when appstate= 'D' and Age_Band='60-69' then perid end),1) as DNA_60_69,
		   round (Count (case when Age_Band='70+' then perid end),1) as Booked_70Plus,
		   round (Count (case when appstate= 'D' and Age_Band='70+' then perid end),1) as DNA_70Plus
     from sessapp_final
group by Session_Date;
quit;
/*add percentage*/
data DNA_Age2 (drop=Percent Percent1 Percent2 Percent3 Percent4 Percent5 Percent6 Percent7);
set DNA_Age;
Percent=(DNA_appointments/Booked_appointments)*100;
Percent1=(DNA_17_19/Booked_17_19)*100;
Percent2=(DNA_20_29/Booked_20_29)*100;
Percent3=(DNA_30_39/Booked_30_39)*100;
Percent4=(DNA_40_49/Booked_40_49)*100;
Percent5=(DNA_50_59/Booked_50_59)*100;
Percent6=(DNA_60_69/Booked_60_69)*100;
Percent7=(DNA_70Plus/Booked_70Plus)*100;
Percent_DNA_Overall=round(percent, 01);
Percent_DNA_17_19=round(Percent1, .01);
Percent_DNA_20_29=round(Percent2, .01);
Percent_DNA_30_39=round(Percent3, .01);
Percent_DNA_40_49=round(Percent4, .01);
Percent_DNA_50_59=round(Percent5, .01);
Percent_DNA_60_69=round(Percent6, .01);
Percent_DNA_70Plus=round(Percent7, .01);
run;

/*reorder*/
proc sql;
create table Age_Final as
select Session_Date, Percent_DNA_Overall, Percent_DNA_17_19, Percent_DNA_20_29, Percent_DNA_30_39, 
Percent_DNA_40_49, Percent_DNA_50_59, Percent_DNA_60_69, 
Percent_DNA_70Plus
from DNA_Age2;
quit;

Re: RAG (RED AMBER GREEN) rating on a proc sql cross tab

PaigeMiller — Thu, 19 Mar 2020 12:39:00 GMT

This is not possible in SQL. It certainly is possible in PROC REPORT, which is a better tool to produce reports. For example, see https://communities.sas.com/t5/ODS-and-Base-Reporting/Problem-Traffic-Lighting-with-Proc-Report/m-p/150006

Re: RAG (RED AMBER GREEN) rating on a proc sql cross tab

Abz_17 — Thu, 19 Mar 2020 13:33:44 GMT

Thanks i will give it a go. I take it I will be able to export the proc report to excel?

Re: RAG (RED AMBER GREEN) rating on a proc sql cross tab

PaigeMiller — Thu, 19 Mar 2020 13:56:29 GMT

@Abz_17 wrote:

I take it I will be able to export the proc report to excel?

Sure. Use ODS EXCEL.

Re: RAG (RED AMBER GREEN) rating on a proc sql cross tab

ballardw — Thu, 19 Mar 2020 15:23:59 GMT

You may want to consider creating formats to group an AGE variable instead of creating variables such as your Age_band. With formats you can modify an analysis without having to create new variables. The groups created by a format are honored by most analysis, reporting and graphing procedures in SAS.

Here is a brief example using a data set you should have available that creates the same summary but with different ages in the "age bands".

proc format library=work;
value age3grp
10 - 12 = '10-12 Years'
13 - 15 = '13-15 Years'
16 - high= '16+ Years'
;value age2grp
10 - 13 = '10-13 Years'
13 - high= '13+ Years'
;
run;

proc report data=sashelp.class;
   columns sex age height,(n pctn);
   define sex / group ;
   define age / group format=age3grp.;
   define height/analysis;
run;
proc report data=sashelp.class;
   columns sex age height,(n pctn);
   define sex / group ;
   define age / group format=age2grp.;
   define height/analysis;
run;

Note that you can't give a name to a format that ends in a digit. My example uses 3grp and 2grp just to indicate they create 3 or 2 ranges respectively.

Also for values based on a single variable you might find the format syntax much shorter than a whole bunch of If/than/else statements. The format could use individual values, ranges with closed intervals (shown) or open interval (such as "less than but not equal to" ) or a mix.