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

svh — Thu, 22 Mar 2018 17:19:04 GMT

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;

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

ChrisNZ — Fri, 23 Mar 2018 04:16:31 GMT

Have you looked at this paper?

"Preserving Line Breaks When Exporting to Excel"

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

topic Re: Merge Two Values Across Two Observations Into One Value in One Observation in SAS Programming

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

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