## Merge Two Values Across Two Observations Into One Value in One Observation

Solved
Occasional Contributor
Posts: 17

# Merge Two Values Across Two Observations Into One Value in One Observation

[ Edited ]

Hello,

The director of my unit has asked me to augment certain metrics with a 1-year and 5-year 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;
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;``````

Accepted Solutions
Solution
‎03-23-2018 09:09 AM
PROC Star
Posts: 2,375

## Re: Merge Two Values Across Two Observations Into One Value in One Observation

Have you looked at this paper?

"Preserving Line Breaks When Exporting to Excel"

https://www.pharmasug.org/proceedings/2014/CC/PharmaSUG-2014-CC07.pdf

All Replies
Solution
‎03-23-2018 09:09 AM
PROC Star
Posts: 2,375

## Re: Merge Two Values Across Two Observations Into One Value in One Observation

Have you looked at this paper?

"Preserving Line Breaks When Exporting to Excel"

https://www.pharmasug.org/proceedings/2014/CC/PharmaSUG-2014-CC07.pdf

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats