I'm using a couple of across columns to formulate a report. and I want to traffic light the grid. However I only seem to be able to traffic light the aggregated variable as shown and not the rest of the values under the across variables.    ods results=on; ods select all; proc SQL; DROP TABLE WORK.ALL_Data; quit; Data WORK.All_Data; Length ID_COL $100. GroupCol1 $100. GroupCol2 $100.; INFILE DATALINES DELIMITER="#" dsd; INPUT ID_COL $ NumCol GroupCol1 $ GroupCol2 $; datalines; A#1#G1_A#G2_A A#2#G1_A#G2_B B#3#G1_A#G2_C B#6#G1_B#G2_A B#4#G1_B#G2_B B#0#G1_B#G2_C C#4#G1_C#G2_A C#9#G1_C#G2_B C#2#G1_C#G2_C C#1#G1_D#G2_A A#1#G1_A#G2_A A#2#G1_A#G2_B B#3#G1_A#G2_C B#6#G1_B#G2_A B#4#G1_B#G2_B B#0#G1_B#G2_C C#4#G1_C#G2_A C#9#G1_C#G2_B A#1#G1_A#G2_A A#2#G1_A#G2_B A#3#G1_A#G2_C B#6#G1_B#G2_A B#4#G1_B#G2_B B#0#G1_B#G2_C C#4#G1_C#G2_A C#6#G1_D#G2_B C#8#G1_D#G2_C A#3#G1_E#G2_A D#2#G1_E#G2_B D#7#G1_E#G2_C A#7#G1_F#G2_A E#8#G1_F#G2_B F#3#G1_F#G2_C C#9#G1_C#G2_B C#6#G1_D#G2_B A#8#G1_D#G2_C D#3#G1_E#G2_A D#2#G1_E#G2_B D#7#G1_E#G2_C D#7#G1_F#G2_A E#8#G1_F#G2_B B#3#G1_F#G2_C F#4#G1_A#G2_A F#9#G1_A#G2_B A#3#G1_A#G2_C F#6#G1_B#G2_A F#3#G1_B#G2_B F#9#G1_B#G2_C F#3#G1_C#G2_A F#7#G1_C#G2_B F#2#G1_C#G2_C G#0#G1_D#G2_A G#8#G1_D#G2_B G#9#G1_D#G2_C G#3#G1_E#G2_A B#2#G1_E#G2_B G#2#G1_E#G2_C B#8#G1_F#G2_A H#4#G1_F#G2_B H#6#G1_F#G2_C G#9#G1_D#G2_C B#3#G1_E#G2_A G#2#G1_E#G2_B G#2#G1_E#G2_C H#8#G1_F#G2_A B#0#G1_B#G2_C C#4#G1_B#G2_A C#9#G1_C#G2_B C#2#G1_C#G2_C C#1#G1_D#G2_A C#6#G1_B#G2_B C#8#G1_D#G2_C D#3#G1_E#G2_A D#2#G1_E#G2_B D#7#G1_E#G2_C C#4#G1_F#G2_B H#6#G1_F#G2_C run; Proc Report data=WORK.All_Data() nowd style(header)={height=40 vjust=middle just=center}; columns ID_COL NumCol NumCol_N (GroupCol2,GroupCol1) ; define ID_COL / group "ID"; define NumCol / noprint analysis n; define GroupCol1 / across "GroupCol1"; define GroupCol2 / across "GroupCol2"; define NumCol_N / computed; compute NumCol_N; NumCol_N=NumCol.n; if NumCol_N eq 2 then call define(_col_,"style","style={background=cx11DD22}"); endcomp; run; title; this is what I'm trying to achieve.... I wanted to know if this can be done in proc report with the across columns. I have used a work around by tabulating this table manually and then using helper columns to define each color for each column, but if I could do it using just the simple proc report code and raw data, that would be more efficient coding.    ... View more

I have a macro below that can be run in 1 program. It produces 2 variables for use for the user &computeblockprocreport. and &formatlines. FormatLines are intended to be used in a proc format statement but it's not working as demo'd below. The computeblockprocreport is used in Proc report and is used to apply a gradient to a table if required in the compute block. THIS IS WORKING.      %macro generate_gradient_colors(start_value,end_value, start_color, end_color, num_intervals,rpt_col_num); /* Extract RGB components of start and end colors */ %let start_red = %substr(&start_color, 3, 2); %let start_green = %substr(&start_color, 5, 2); %let start_blue = %substr(&start_color, 7, 2); %let end_red = %substr(&end_color, 3, 2); %let end_green = %substr(&end_color, 5, 2); %let end_blue = %substr(&end_color, 7, 2); %put &end_red; %put &end_green; %put &end_blue; %let start_red_decimal = %sysfunc(inputn(&start_red.,hex8.)); %let start_green_decimal = %sysfunc(inputn(&start_green.,hex8.)); %let start_blue_decimal = %sysfunc(inputn(&start_blue.,hex8.)); %let end_red_decimal = %sysfunc(inputn(&end_red.,hex8.)); %let end_green_decimal = %sysfunc(inputn(&end_green.,hex8.)); %let end_blue_decimal = %sysfunc(inputn(&end_blue.,hex8.)); /* Calculate the increment for each color component */ %let red_increment = (%sysevalf(&end_red_decimal - &start_red_decimal)) / &num_intervals; %let green_increment = (%sysevalf(&end_green_decimal - &start_green_decimal)) / &num_intervals; %let blue_increment = (%sysevalf(&end_blue_decimal - &start_blue_decimal)) / &num_intervals; %let value_increment = %sysevalf((%sysevalf(&end_value - &start_value + 1) / &num_intervals)); %let prev_increment = &start_value; /* Generate format lines with gradient colors */ %GLOBAL formatlines; %GLOBAL computeblockprocreport; %do i = 1 %to &num_intervals; %let red_value = %sysevalf(&start_red_decimal + &red_increment * &i); %let green_value = %sysevalf(&start_green_decimal + &green_increment * &i); %let blue_value = %sysevalf(&start_blue_decimal + &blue_increment * &i); %let hex_color = cx%sysfunc(strip(%sysfunc(putn(&red_value, hex2.))))%sysfunc(strip(%sysfunc(putn(&green_value, hex2.))))%sysfunc(strip(%sysfunc(putn(&blue_value, hex2.)))); %if &i. eq 1 %then %do; /*for Proc Format*/%let formatlines = low - &start_value. = %str(%')&hex_color.%str(%'); /*for Proc Report*/%let computeblockprocreport = if _C&rpt_col_num._ le &start_value. then do%str(;) call define(_col_,%str(%')style%str(%'),%str(%')STYLE={BACKGROUND=&hex_color.}%str(%'))%str(;) end%str(;); %end; %if &i. lt &num_intervals %then %do; /*for Proc Format*/%let formatlines = &formatlines. &prev_increment - %sysevalf(&i. * &value_increment) = %str(%')&hex_color.%str(%'); /*for Proc Report*/%let computeblockprocreport = &computeblockprocreport. if _C&rpt_col_num._ gt &prev_increment and _C&rpt_col_num._ le %sysevalf(&i. * &value_increment) then do%str(;) call define(_col_,%str(%')style%str(%'),%str(%')STYLE={BACKGROUND=&hex_color.}%str(%'))%str(;) end%str(;); %end; %if &i. eq &num_intervals %then %do; /*for Proc Format*/%let formatlines = &formatlines. &prev_increment - high = %str(%')&hex_color.%str(%'); /*for Proc Report*/%let computeblockprocreport = &computeblockprocreport. if _C&rpt_col_num._ gt &prev_increment then do%str(;) call define(_col_,%str(%')style%str(%'),%str(%')STYLE={BACKGROUND=&hex_color.}%str(%'))%str(;) end%str(;); %end; %let prev_increment = %sysevalf(&i. * &value_increment); %end; %mend; /* Example usage */ %let start_value = 1; %let end_value = 10; %let start_color = cxFFFFFF; %let end_color = cx00A5B9; %let num_intervals = 5; %let rpt_col_num = 1; /* Generate gradient colors */ %generate_gradient_colors(&start_value, &end_value, &start_color, &end_color, &num_intervals,&rpt_col_num); %put &formatlines.; %put &computeblockprocreport.; proc format; value heatfmt &formatlines. ; quit; proc format; value heatfmtmanual low - 1 = 'cxCCEDF1' 1 - 2 = 'cxCCEDF1' 2 - 4 = 'cx99DBE3' 4 - 6 = 'cx66C9D5' 6 - 8 = 'cx33B7C7' 8 - high = 'cx00A5B9' ; quit; data test; do col1 = &start_value to &end_value; rnum = STRIP(PUT(col1,best32.)); output; end; run; /*TEST Proc Report COMPUTE BACKGROUND*/ Proc Report Data=work.test; columns _ALL_; define col1 / "col1"; compute col1; &computeblockprocreport.; endcomp; ; run; /*TEST Proc TABULATE */ Proc Tabulate DAta=WORK.test style=[background=heatfmt.]; ; class rnum; var col1; table rnum,col1 ; run; /*TEST Proc TABULATE */ Proc Tabulate DAta=WORK.test style=[background=heatfmtmanual.]; ; class rnum; var col1; table rnum,col1 ; run; %put &computeblockprocreport.; %put ; %put &formatlines.;       The text in &formatlines. is:   low - 1 = 'cxCCEDF1' 1 - 2 = 'cxCCEDF1' 2 - 4 = 'cx99DBE3' 4 - 6 = 'cx66C9D5' 6 - 8 = 'cx33B7C7' 8 - high = 'cx00A5B9'   line 75 has the proc format and &formatlines variable that isn't working.  line 81 has the manual version types out instead of using the &formatlines var.   Line 100 and Line 111 are tests for Proc tabulate using the two formats.  Only the manual format is working...    Have I come too far just to realize this isn't possible to use macro vars in proc format?       Thanks; ... View more

I have what seems to be a simple problem. I have a set of vertices which define a polygon on a map. I'm working with Canada.  I'm using QGIS, here is my polygon: QGIS can output a list of vertices of the polygon, which I put into SAS: data Polygon; input vertex x y ; infile datalines delimiter="#"; datalines; 1#44.1598831520675#-79.7063565442144 2#44.2877110404308#-79.0767942900621 3#44.1184676463941#-78.3947685147305 4#43.6748950070277#-78.9806111679 5#44.1598831520675#-79.7063565442144 ; run; What I want to do is let's say I have this points:  data PointData; length City $100.; input City $ X Y; infile datalines delimiter="#"; datalines; Mississauga#43.58718486496601#-79.64392326388756 Ajax#43.85040430693342#-79.01840936353507 ; run; I want to know if any of these points are in the polygon or not. In the example, Ajax is within the polygon but Mississauga is not. so I'd expect the data output to be:     I'm not sure how to do this. I've looked at GINSIDE, but it seems you need an underlying map for that, and I don't know how to define a polygon within that proc. So I'm kind of lost as geocoding isn't my expertise.    any help would be appreciated.  ... View more

I have a web application which uses _webout as the ods destination and the procs return an xlsx file. I have the following application headers:  data _null_; rc = stpsrv_header('Content-type','application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); rc = stpsrv_header('Content-disposition','attachment; filename=SomeReport.xlsx'); run; When I call the stored proc from Edge, what happens is that the file download dialogue will appear like this:    However what I'd like is for the browser to download the file automatically using the flyout menu like this:    Through research, it may appear that the header's are incorrect and/or the stored proc is not returning the (recognizable) binary content of the xlsx file. Is this true? The file I'm getting is an excel file. I open it and it works fine. Where in the stored proc would I ensure the binary content is valid and recognizable as an xlsx? is it something I set in the SP targets wizard?     I've ruled out the java script in the webpage code as I can run the hyperlink of the stored process directly in my browser without javascript to test reports and it results in the same behavior.  ... View more