topic Re: collapse data and show frequency and sum in New SAS User

collapse data and show frequency and sum

chriswong1386 — Mon, 29 Nov 2021 04:48:55 GMT

The final result want to be:

name	yes	no	sum of yes	sum of no
chris	4	2	140	60
ken	3	1	110	30
tom	1	3	70	60

please help!

I just know need to proc sort it first by name. I don't know how to collapse it afterwards

Re: collapse data and show frequency and sum

chriswong1386 — Mon, 29 Nov 2021 04:53:21 GMT

proc freq is not allow, it need to be done within a data step, but can assume the data about is already create and can use
data answer;
set try;
...

Re: collapse data and show frequency and sum

andreas_lds — Mon, 29 Nov 2021 06:19:39 GMT

Looks like homework, so i post only some hints until you showed more effort solving this yourself.

- BY statement to tell sas that data should be processed by name, enabling first.name to reset counters and last.name to output the summary observation

- LENGTH statement to define variables for the counters, those should be RETAINed, note: names like "sum of yes" are not allowed, you may want to attach a label

Re: collapse data and show frequency and sum

chriswong1386 — Mon, 29 Nov 2021 08:15:00 GMT

proc sort data=try ;
by name yesno;

data try1;
set try;
by name yesno;
x=first.name;
y=last.name;
if first.name=1 then
b=0;
b+1;
if first.yesno=1 then
c=0;
c+1;
acc_price+price;

/*if last.name=1 or last.yesno=1 then output;*/
drop x y;

name yesno price b c acc_price

chris

yes

200

chris

yes

chris

yes

140

chris

yes

100

ken

230

ken

yes

340

ken

yes

330

ken

yes

290

tom

yes

470

tom

400

tom

370

tom

350

chris

yes

200

ken

230

ken

yes

340

tom

400

tom

yes

470

really stuck, nowhere near the answer, please help me!

Re: collapse data and show frequency and sum

chriswong1386 — Mon, 29 Nov 2021 08:26:53 GMT

proc sort data=try ;
by name yesno;

data try1;
set try;
by name yesno;
x=first.name;
y=last.name;
if first.name=1 then
b=0;
b+1;
if first.yesno=1 then
c=0;
c+1;
acc_price+price;

if last.name=1 or last.yesno=1 then output;
drop x y;

chris

yes

200

ken

230

ken

yes

340

tom

400

tom

yes

470

help !, I am stuck! still no where near!

Re: collapse data and show frequency and sum

AndreaVianello — Mon, 29 Nov 2021 08:54:42 GMT

data try;
input name $ yesno $ price;
datalines;
chris yes 10
chris no 10
chris yes 30
chris yes 40
chris no 50
chris yes 60
ken yes 60
ken yes 40
ken no 30
ken yes 10
tom no 10
tom no 20
tom no 30
tom yes 70
; run;

proc sql;
select name , sum(yesno='yes') as yes, sum(yesno='no') as no ,
sum( price * (yesno='yes')) as sum_of_yes, sum( price * (yesno='no')) as sum_of_no
from try
group by name
; quit;

Re: collapse data and show frequency and sum

Ksharp — Mon, 29 Nov 2021 12:16:47 GMT

data try;
input name $ yesno $ price;
datalines;
chris yes 10
chris no 10
chris yes 30
chris yes 40
chris no 50
chris yes 60
ken yes 60
ken yes 40
ken no 30
ken yes 10
tom no 10
tom no 20
tom no 30
tom yes 70
;


data want;
 do until(last.name);
   set try;
   by name;
   length yes no sumofyes sumofno 8 ;
   if yesno='yes' then do;yes+1;sumofyes+price;end;
   if yesno='no' then do;no+1;sumofno+price;end;
 end;
 output;
 call missing(yes ,no, sumofyes, sumofno );
 keep name yes no sumofyes sumofno ;
run;