BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
svh
Lapis Lazuli | Level 10 svh
Lapis Lazuli | Level 10

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;
1 ACCEPTED SOLUTION

Accepted Solutions
Cynthia_sas
SAS Super FREQ

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

4 REPLIES 4
ballardw
Super User

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.

svh
Lapis Lazuli | Level 10 svh
Lapis Lazuli | Level 10

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

Cynthia_sas
SAS Super FREQ

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

 

svh
Lapis Lazuli | Level 10 svh
Lapis Lazuli | Level 10

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. 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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