The SAS Output Delivery System and reporting techniques

Merging Text Values Across Observations and Sending Results to PROC REPORT

Accepted Solution Solved
Reply
Contributor svh
Contributor
Posts: 21
Accepted Solution

Merging Text Values Across Observations and Sending Results to PROC REPORT

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;

Accepted Solutions
Solution
‎03-23-2018 08:42 AM
SAS Super FREQ
Posts: 9,424

Re: Merging Text Values Across Observations and Sending Results to PROC REPORT

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):

span2_xlsx.png

 

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:

make_one_row.png

to get this with PROC REPORT:

one_row_with_line.png

 

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

 

View solution in original post


All Replies
Super User
Posts: 13,913

Re: Merging Text Values Across Observations and Sending Results to PROC REPORT

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.

Contributor svh
Contributor
Posts: 21

Re: Merging Text Values Across Observations and Sending Results to PROC REPORT

Excel output is ideal, for we deliver a large workbook of metrics to the client. 

Solution
‎03-23-2018 08:42 AM
SAS Super FREQ
Posts: 9,424

Re: Merging Text Values Across Observations and Sending Results to PROC REPORT

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):

span2_xlsx.png

 

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:

make_one_row.png

to get this with PROC REPORT:

one_row_with_line.png

 

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

 

Contributor svh
Contributor
Posts: 21

Re: Merging Text Values Across Observations and Sending Results to PROC REPORT

Posted in reply to Cynthia_sas

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. 

☑ This topic is solved.

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

Discussion stats
  • 4 replies
  • 187 views
  • 0 likes
  • 3 in conversation