a month ago  last edited a month ago
Hello,
The director of my unit has asked me to augment certain metrics with a 1year and 5year change (in the form of a percentage). The audience needs to quickly see the change in time. So there is one row per metric in the Excel spreadsheet that I have to create, but the director would like the two percentages placed in one cell in this form:
Metric  Current Value  Change 
Graduation Rate  81%  1yrΔ+0.4% 5yrΔ+1.6% 


I have been able to get the Greek delta into the output, but I have not been able to determine how to embed a character that will make the two values into one cell. Here is my code to make the data set, and I'm wondering if someone can point me toward a resource about how you merge (for lack of a better word) two values across two rows into one row when they share a common identifier (in this case, Graduation Rate).
data x;
input year $4. order rate;
Metric='Graduation';
cards;
2007 1 0.750
2010 2 0.800
2011 3 0.815
;
run;
data y1;
set x;
if year in ('2010' '2011') then do;
pctchng = dif(rate) / lag(rate) ;
period = '1yr';
output;
putlog year= order= rate= pctchng=;
end;
if year in ('2007' '2011') then do;
pctchng = dif(rate) / lag(rate) ;
period = '5yr';
output;
putlog year= order= rate= pctchng=;
end;
run;
data y1;
set y1;
if pctchng = . then delete;
run;
data y1;
set y1;
Z= "(*ESC*){unicode delta_U}";
format pctchng percent14.1;
text_var = STRIP(PUT(pctchng, percent14.1));
if pctchng > 0 then do;
newvar = strip(period)' 'Z'+'(text_var);
end;
else if pctchng < 0 then do;
newvar = strip(period)' 'Z''(text_var);
end;
else if pctchng = 0 then do;
newvar = strip(period)' 'Z(text_var);
end;
keep Metric rate newvar;
run;
4 weeks ago
Have you looked at this paper?
"Preserving Line Breaks When Exporting to Excel"
https://www.pharmasug.org/proceedings/2014/CC/PharmaSUG2014CC07.pdf
4 weeks ago
Have you looked at this paper?
"Preserving Line Breaks When Exporting to Excel"
https://www.pharmasug.org/proceedings/2014/CC/PharmaSUG2014CC07.pdf
Need further help from the community? Please ask a new question.