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

Accepted Solution Solved
Reply
Occasional Contributor svh
Occasional Contributor
Posts: 17
Accepted Solution

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;
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;

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


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
  • 1 reply
  • 76 views
  • 1 like
  • 2 in conversation