BookmarkSubscribeRSS Feed
srinivaschary
Calcite | Level 5

Hi,

My data is like below i need to compare last month and current month data and need to highlight with up arrow or down arrow for growth or loss please provide the code thanks in advance

 

data input

MonthLocationStores
11/1/2018Chennai16
11/1/2018Hyderabad12
11/1/2018Chennai14
11/1/2018Chennai12
11/1/2018Hyderabad32
11/1/2018Hyderabad14
12/1/2018Chennai17
12/1/2018Hyderabad16
12/1/2018Chennai1
12/1/2018Chennai21
12/1/2018Hyderabad12
12/1/2018Hyderabad12

 

Expected output:

attached image file

 

3 REPLIES 3
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Not here to type in test data, so just some example code - untested:

proc sort data=have;
  by location month;
run;
proc means data=have;
  var stores;
  by location month;
  output out=inter sum=want;
run;
proc transpose data=inter out=want;
  by location month;
  var want;
  idlabel month;
run;
data want;
  length col $2;
  set want;
  array var{2};
  if var{2} < var{1} then col=cats("^s={font_weight=bold font_color=red}↓ ^s={font_weight=normal font_color=black}",put(var{2},best.));
  else if var{2} > var{1} then col=cats("^s={font_weight=bold font_color=green}↑ ^s={font_weight=normal font_color=black}",put(var{2},best.));
  else col=strip(put(var{2},best.));
run;
ods escapechar="^";
proc report data=want;
  columns _all_;
run;

srinivaschary
Calcite | Level 5

Hi While am using that code showing below error.

 

 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

You will need to get the special character to the output somehow, as you are not using a unicode system (and why not?) then you would need to push the symbol out some other way, maybe:

proc sort data=have;
  by location month;
run;
proc means data=have;
  var stores;
  by location month;
  output out=inter sum=want;
run;
proc transpose data=inter out=want;
  by location month;
  var want;
  idlabel month;
run;
data want;
  length col $2;
  set want;
  array var{2};
  if var{2} < var{1} then col=cats("^s={font_weight=bold font_color=red}^{unicode 28A9} ^s={font_weight=normal font_color=black}",put(var{2},best.));
  else if var{2} > var{1} then col=cats("^s={font_weight=bold font_color=green}^{unicode 26A9} ^s={font_weight=normal font_color=black}",put(var{2},best.));
  else col=strip(put(var{2},best.));
run;
ods escapechar="^";
proc report data=want;
  columns _all_;
run;

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 3 replies
  • 933 views
  • 0 likes
  • 2 in conversation