Add a column with line graph and indicator

[nickspencer]

I am doing this in an excel but want to automate using SAS. I have a dataset with sales over month. I want to create two new columns. One called sales_mth and another sales_ind.
Sales_month should have a little line graph to show the sales increase or decrease over month. Sales_ind should have colored circle (red, yellow or green ) based on current month sales.
And finally export that to excel.
Is that possible to achieve two columns with SAS?

If so, how can I do that.

Hope the question makes sense.

Thanks,
Nick

[ed_sas_member · Accepted solution]

Hi @nickspencer 

I think the following material could be a great resource: https://support.sas.com/resources/papers/proceedings/proceedings/sugi31/116-31.pdf

Hereafter is an example of a possible output.

Hope this helps 

Best,

ods excel file="/path/retail_dashboard.xlsx";

data retail;
	set sashelp.retail (keep=date sales);
	_lag_sales = lag(sales);
	year = year(date);
	month = month(date);
run;
	
proc report data=retail nowd;
	column year month sales _lag_sales Sales_ind Sales_month;
	define year 	   / group;
	define month 	   / group;
	define sales	   / display format=dollar8.2;
	define _lag_sales  / display noprint;
	define Sales_ind   / computed;
	define Sales_month / computed;

	/*Sales_ind: colored circle (red, yellow or green) based on current month sales */
	compute Sales_ind / character;
		if 0   <= sales < 500 then do; /* determine the threshold */
			Sales_ind ="O";
			call define(_col_, 'style', 'style=[foreground=red font_weight=bold');
		end;
		else if 500 <= sales < 800 then do; /* determine the threshold */
			Sales_ind ="O";
			call define(_col_, 'style', 'style=[foreground=yellow font_weight=bold');
		end;
		else if 800 <= sales then do; /* determine the threshold */
			Sales_ind ="O";
			call define(_col_, 'style', 'style=[foreground=green font_weight=bold]');
		end;
	endcomp;

	/*Sales_month should have a little line graph to show the sales increase or decrease over month.*/
	compute Sales_month / character;
		if Sales < _lag_sales then do;
			Sales_month="➘";
			call define(_col_, 'style', 'style=[foreground=red font_weight=bold ');
		end;
		if Sales = _lag_sales then do;
			Sales_month="➙";
			call define(_col_, 'style', 'style=[foreground=yellow font_weight=bold');
		end;
		if Sales > _lag_sales then do;
			Sales_month="➚";
			call define(_col_, 'style', 'style=[foreground=green font_weight=bold');
		end;
	endcomp;
run;

ods excel close;