topic Re: How do I collapse/combine identical observations using a Quantity variable? in ODS and Base Reporting

How do I collapse/combine identical observations using a Quantity variable?

jwinkle — Thu, 07 Jul 2016 14:33:43 GMT

**NOVICE CODER...PLEASE EXPLAIN ANSWERS AS THOROUGHLY AS POSSIBLE, THANK YOU!!!!**

I am working with a report generated through PROC REPORT with 7 variables. I would like to create a 'Quantity" column that will combine observations if the value of every variable is the same and tell me how many there were.

For example the first report below would be converted into the second one (the variables are PRESIDENT SECESSION PARTY QUANTITY):

George Washington 1 None 1

John Adams 2 Federalist 1

Thomas Jefferson 3 Dem-Rep 1

James Madison 4 Dem-Rep 1

------------------------------------------------------------------------------------------------------------------------------------------------------------

George Washington 1 None 2

John Adams 2 Federalist 1

Thomas Jefferson 3 Dem-Rep 3

James Madison 4 Dem-Rep 1

NOTE: I'm using SAS EG

Re: How do I collapse/combine identical observations using a Quantity variable?

Reeza — Thu, 07 Jul 2016 14:13:05 GMT

You have two option, either a summarize task or Query builder.

In a summary task select option to create an output table.

In a query builder add all fields to select, add the ones that need to be identical to group by clause, and a computed column that adds up quantity.

Re: How do I collapse/combine identical observations using a Quantity variable?

jwinkle — Thu, 07 Jul 2016 14:26:11 GMT

I have been trying Query Builder, but I cannot figure out how to use it in PROC REPORT and still retain all of the many format changes I have applied. Here is an example of the code and I just need to combine identical observations as the last task (I know this code is scrappy and inefficient, but it works hehe):

PROC report data=hedge_report;
columns (default_swap_ref_entity Issuer_id facility_id
long_description) ("Swap Option Information" mature_date crf fix_rate1 n);
define default_swap_ref_entity / order;
define Issuer_ID / order;
define Facility_ID / order format=facID.;
define long_description / order;
define mature_date / display;
define CRF / display format=dollar.;
define fix_rate1 / display format=percent.2;
compute fix_rate1;
do fix_rate1=(fix_rate1/100);
end;
endcomp;
compute mature_date;
if (today()-mature_date)<0
then do;
call define("_C5_","style","STYLE={background=cx5bff5b font=('serif,Gill Sans MT',2)}");
call define("mature_date","format","active.");
end;
else if (today()-mature_date)>=730
then do;
call define("_C5_","style","STYLE={background=cxff5b5b font=('serif,Times',2, bold)}");
call define("mature_date","format","expiry1plus.");
end;
else if (today()-mature_date)>=365 and (today()-mature_date)<730
then do;
call define("_C5_","style","STYLE={background=cxff855b font=('serif,Times',2, bold)}");
call define("mature_date","format","expiry1plus.");
end;
else if (today()-mature_date)>=0 and (today()-mature_date)<365
then do;
call define("_C5_","style","STYLE={background=cxffff5b font=('serif,Times',2, bold)}");
call define("mature_date","format","expiry01a.");
end;
endcomp;
compute crf;

if 0<=CRF<1000000 then do;
call define("_C6_","style","STYLE={color=GREEN fontweight=bold}");
end;
else if abs(CRF)>=1000000 then do;
call define("_C6_","style","STYLE={color=GREEN fontweight=bold}");
call define("crf","format","ProfLoss.");
end;
else if CRF<0 then
call define("_C6_","style","STYLE={color=RED fontweight=bold}");
endcomp;
run;

Re: How do I collapse/combine identical observations using a Quantity variable?

sh0e — Thu, 07 Jul 2016 14:26:19 GMT

This is a job for PROC SUMMARY.

proc summary data = in missing nway;
  class president secession party;
  var quantity;
  output out = reduced( drop = _type_ _freq_ ) sum=;
run;

Re: How do I collapse/combine identical observations using a Quantity variable?

Reeza — Thu, 07 Jul 2016 14:45:03 GMT

Ok. When I saw EG I assumed you were using GUI.

I think you should summarize and precalculate things before proc report. It will make you code cleaner and if you ever need edits you'll thank yourself.

Im going to move your post to reporting forum for a better response.

Re: How do I collapse/combine identical observations using a Quantity variable?

Cynthia_sas — Thu, 07 Jul 2016 15:06:19 GMT

Hi:

This is possible with PROC REPORT without using any other procedure. If this is what you want:

Then here's the code - -showing how to move from DISPLAY usage to GROUP usage to get the QUANTITY column to summarize;

data prez;
  length president $50 party $20;
  infile datalines dlm=',' dsd;
  input president $ succession party $ quantity;
datalines;
"George Washington" , 1 , None ,1
"George Washington" , 1 , None ,1
"John Adams", 2 , Federalist,1
"Thomas Jefferson", 3 , Dem-Rep,1
"Thomas Jefferson", 3 , Dem-Rep,1
"Thomas Jefferson", 3 , Dem-Rep,1
"James Madison", 4 , Dem-Rep,1
;
run;

ods html;
proc report data=prez;
title '1) show all data';
  column president succession party quantity;
  define president / display;
  define succession / display;
  define  party / display;
  define quantity / sum;
  rbreak after / summarize;
run;


proc report data=prez;
title '2) use ORDER for some variables';
  column succession president party quantity;
  define succession / order;
  define president / order;
  define  party / display;
  define quantity / sum;
  break after succession / summarize;
  rbreak after / summarize;
run;

proc report data=prez;
title '3) use GROUP for to collapse and summarize';
  column succession president party quantity;
  define succession / group;
  define president / group;
  define  party / group;
  define quantity / sum;
  rbreak after / summarize;
run;

cynthia

Re: How do I collapse/combine identical observations using a Quantity variable?

jwinkle — Thu, 07 Jul 2016 15:41:53 GMT

Thanks so much!! I got the observations collapsed, but the problem is that when some variables were summed, the formats I applied in a "call define" statement do not work. I cannot figure out how to apply formats when a variable is defined as "sum" or "mean" at the beginning a proc report-- from what I can tell, my user formats are only working on "display" variables.