BookmarkSubscribeRSS Feed
Ronein
Meteorite | Level 14

Hello
For each month there is a regression model that run and produce score (Y).
I would like to display the data set via proc report and apply a rule ad
S following:
If there is increase from one month to next month then color it in red.
If there is decrease from one month to next month then color it in green.
The calculation should be done for each row separately.
(Please note that green mean better explentaory variable or better resulted score)
(Please note that red mean worse explentaory variable or worse resulted score)
The colors will help the user (who look at the report) to understand if the customer had improvement or deterioration from one month to next month.

Please note that number of columns with nameYYMM can be different .

so i am looking for dynamic code that will work with any mumber of columns.

Data Have;
Input VAR_name $ Mon2301 Mon2302 Mon2303 Mon2304 Mon2305;
cards;
X1 70 70 70 70 70
X2 70 70 90 90 90
X3 40 40 40 40 40
X4 30 30 50 50 20
X5 70 45 45 45 45
X6 30 30 30 20 40
TOTAL 310 285 325 315 305
Y 7 6 9 9 7
;
Run;





 

Ronein_0-1684465366347.png

 

3 REPLIES 3
Ksharp
Super User
Data Have;
Input VAR_name $ Mon2301 Mon2302 Mon2303 Mon2304 Mon2305;
cards;
X1 70 70 70 70 70
X2 70 70 90 90 90
X3 40 40 40 40 40
X4 30 30 50 50 20
X5 70 45 45 45 45
X6 30 30 30 20 40
TOTAL 310 285 325 315 305
Y 7 6 9 9 7
;
Run;

proc transpose data=have(obs=0) out=vname;
var _all_;
run;
data _null_;
 set vname(firstobs=2) end=last;
length list $ 2000;
retain list;
list=catx(' ',list,_name_);
if last then do;
  call symputx('list',list);
  call symputx('last',_name_);
end;
run;

ods excel file='c:\temp\want.xlsx';
proc report data=have nowd;
define _all_/display;
compute &last.;
 array x{*} &list. ;
 do i=2 to dim(x);
   if x{i}>x{i-1} then call define(vname(x{i}),'style','style={background=red}');
   if x{i}<x{i-1} then call define(vname(x{i}),'style','style={background=green}');
 end;
endcomp;
run;
ods excel close;

Ksharp_0-1684497440603.png

 

Ronein
Meteorite | Level 14

Thanks,

Can you please explain:

The calculation is done on all columns except of the first column .

You wrote compute only on the last column.

How SAS know to perform computation on all columns (Except first)While you type computation only on last column?

compute &last.;
Ksharp
Super User
It is special character of PROC REPORT.
PROC REPORT calculated is from left to right and first to last.
Here I used "compute &last.;" which &last. is the last variable name, to make sure I can refer to any variable before &last.
if you use "compute var_name;" ,you only can refer to itself because there are not variables at left side of "var_name".

So I use ARRAY to refer to Mon2301 Mon2302 Mon2303 Mon2304 Mon2305
"array x{*} &list. ;"

do i=2 to dim(x);
means use "Mon2301 " (a.k.a x{1}) to compare with other variables to get the result you want and STYLE it by red or green.

sas-innovate-2024.png

 

Secure your spot at the must-attend AI and analytics event of 2024: SAS Innovate 2024! Get ready for a jam-packed agenda featuring workshops, super demos, breakout sessions, roundtables, inspiring keynotes and incredible networking events.

 

Register by March 1 to snag the Early Bird rate of just $695! Don't miss out on this exclusive offer. 

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 581 views
  • 1 like
  • 2 in conversation