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

Solved
Occasional Contributor
Posts: 5

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

[ Edited ]

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

George Washington                  1                 None                    1

Thomas Jefferson                     3                 Dem-Rep             1

Thomas Jefferson                     3                 Dem-Rep             1

Thomas Jefferson                     3                 Dem-Rep             1

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

George Washington                  1                 None                    2

Thomas Jefferson                     3                 Dem-Rep             3

NOTE: I'm using SAS EG

Accepted Solutions
Solution
‎07-07-2016 12:09 PM
SAS Super FREQ
Posts: 9,329

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

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
"Thomas Jefferson", 3 , Dem-Rep,1
"Thomas Jefferson", 3 , Dem-Rep,1
"Thomas Jefferson", 3 , 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

All Replies
Super User
Posts: 23,357

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

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.

Occasional Contributor
Posts: 5

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

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;

Super User
Posts: 23,357

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

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.

Contributor
Posts: 25

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

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;```
Solution
‎07-07-2016 12:09 PM
SAS Super FREQ
Posts: 9,329

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

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
"Thomas Jefferson", 3 , Dem-Rep,1
"Thomas Jefferson", 3 , Dem-Rep,1
"Thomas Jefferson", 3 , 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

Occasional Contributor
Posts: 5