Hello,
The director has asked me to complete a workbook of metrics in a spreadsheet with one row per metric. However, I am also to add a 1-year and 5-year change figure into one cell. It should look something like this:
Metric | Value | Change |
Graduation Rate | 81% | 1yrΔ+0.4% 5yrΔ+1.6% |
|
|
Can someone direct me to a resource that shows how to concatenate values that are across observations. My code will produce two rows per metric, but this will not fit the form of the workbook. I am trying to find a solution that can work with PROC REPORT, but I can't find documentation for this.
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; *different if/then blocks for positive and negative change;
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;
Hi:
Well, it is possible. The easiest thing to do would be to generate 2 rows, as you are doing, and then use SPANROWS to get this look and feel (note how row 2 and 3 are merged for rate and metric because of SPANROWS):
But, if you make one long string like this so that instead of 2 rows, you have 1 row -- here I made a variable called YRVAR and used ODS ESCAPECHAR with NEWLINE and note that there is now only 1 row:
to get this with PROC REPORT:
I changed your code slightly because I am not in the habit of doing this:
data a;
set a;
(naming my output file the same name as my input file -- it is not a best practice and since I teach my students not to do it, I feel I need to set an example, no matter how expeditious it might seem to be).
Anyway, here's the code I used:
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 y1a;
set y1;
if pctchng = . then delete;
run;
data y1b;
set y1a;
Z= "(*ESC*){unicode delta_U}";
format pctchng percent14.1;
text_var = STRIP(PUT(pctchng, percent14.1));
if pctchng > 0 then do; *different if/then blocks for positive and negative change;
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;
ods excel file='c:\temp\span_report.xlsx';
proc report data=y1b spanrows;
title '1 Use spanrows';
column rate Metric newvar ;
define rate / order style(column)={vjust=m};
define Metric / order style(column)={vjust=m};
define newvar / display;
run;
ods excel close;
title;
options ls=200;
proc sort data=y1b; by rate metric;
run;
data onerow;
length yrvar $200;
set y1b; by rate metric;
retain yrvar;
if first.rate and first.metric then do;
yrvar = newvar;
end;
else yrvar = catx('^{newline 1}',yrvar,newvar);
if last.metric then do;
output;
putlog _n_= rate= metric= yrvar=;
putlog ' ';
end;
run;
ods escapechar='^';
ods excel file='c:\temp\span_report2.xlsx';
title '2 use ODS Escapechar';
proc report data=onerow;
column rate Metric yrvar ;
define rate / order style(column)={vjust=m};
define Metric / order style(column)={vjust=m};
define yrvar / display;
run;
ods excel close;
title;
I used ODS EXCEL to create an output file in XLSX format. Hope this helps.
Cynthia
And what application are you going to view the results in? Since the requirement to have two values "within in cell" and apparently stacked (not explicitly stated), the approach for "stacking" will vary somewhat between html, PDF, Rtf or other output.
Excel output is ideal, for we deliver a large workbook of metrics to the client.
Hi:
Well, it is possible. The easiest thing to do would be to generate 2 rows, as you are doing, and then use SPANROWS to get this look and feel (note how row 2 and 3 are merged for rate and metric because of SPANROWS):
But, if you make one long string like this so that instead of 2 rows, you have 1 row -- here I made a variable called YRVAR and used ODS ESCAPECHAR with NEWLINE and note that there is now only 1 row:
to get this with PROC REPORT:
I changed your code slightly because I am not in the habit of doing this:
data a;
set a;
(naming my output file the same name as my input file -- it is not a best practice and since I teach my students not to do it, I feel I need to set an example, no matter how expeditious it might seem to be).
Anyway, here's the code I used:
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 y1a;
set y1;
if pctchng = . then delete;
run;
data y1b;
set y1a;
Z= "(*ESC*){unicode delta_U}";
format pctchng percent14.1;
text_var = STRIP(PUT(pctchng, percent14.1));
if pctchng > 0 then do; *different if/then blocks for positive and negative change;
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;
ods excel file='c:\temp\span_report.xlsx';
proc report data=y1b spanrows;
title '1 Use spanrows';
column rate Metric newvar ;
define rate / order style(column)={vjust=m};
define Metric / order style(column)={vjust=m};
define newvar / display;
run;
ods excel close;
title;
options ls=200;
proc sort data=y1b; by rate metric;
run;
data onerow;
length yrvar $200;
set y1b; by rate metric;
retain yrvar;
if first.rate and first.metric then do;
yrvar = newvar;
end;
else yrvar = catx('^{newline 1}',yrvar,newvar);
if last.metric then do;
output;
putlog _n_= rate= metric= yrvar=;
putlog ' ';
end;
run;
ods escapechar='^';
ods excel file='c:\temp\span_report2.xlsx';
title '2 use ODS Escapechar';
proc report data=onerow;
column rate Metric yrvar ;
define rate / order style(column)={vjust=m};
define Metric / order style(column)={vjust=m};
define yrvar / display;
run;
ods excel close;
title;
I used ODS EXCEL to create an output file in XLSX format. Hope this helps.
Cynthia
Thank you! You are clearly an expert. That DATA step is an elegant solution. I would not have thought to use CATX with an escape character.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.