topic Re: Group by user - total and average in SAS Programming

Group by user - total and average

vnreddy — Mon, 11 Jul 2022 12:00:34 GMT

Hi, Could someone help me with below :

data want;
input REGION $ NAME $ DATE :datetime20. DCODE $ TRAVEL;
format date datetime20.;
datalines;
EAST	LEE		01JUN2022:00:00:00	AP64389	21.09
EAST	LEE		03JUN2022:00:00:00	AP64389	15.19
EAST	SAM		05JUN2022:00:00:00	PS1234	4.14
EAST	LEE		07JUN2022:00:00:00	GP38299	2.09
WEST	DAN		07JUN2022:00:00:00	CX78934	10.07
WEST	DAN		07JUN2022:00:00:00	CX78934	8.07
WEST	DAN		07JUN2022:00:00:00	PB53468	10.07
WEST	DAN		07JUN2022:00:00:00	PB53468	10.07
SOUTH	TIM		02JUN2022:00:00:00	SU18934	52.23
;
run;

Expected output :

I need to show the total count of codes based on the user level, from above NAME LEE has to AP64389 repeated two times so the count will be 2. and the tot_travel is total of AP64389 (21.09+15.19 = 36.28) and the avg_travel will be (36.28/2 = 18.14)

and the bottom of each name i need to show the total of count, tot_travel, and the avg_travel should be total of tot_travel/total of count i.e., 38.37/3 = 12.79

I need the report in same format, could someone help me how can i achive this.

Re: Group by user - total and average

andreas_lds — Mon, 11 Jul 2022 11:53:49 GMT

Why is Lee with Dcode = PS1234 ignored?

Re: Group by user - total and average

PaigeMiller — Mon, 11 Jul 2022 12:41:33 GMT

proc summary data=have;
    class region name dcode;
    types region*name region*name*dcode;
    var travel;
    output out=want sum=tot_travel mean=avg_travel;
run;

From there, sorting the data will get you the layout you want.

proc sort data=want;
    by region name descending _type_ dcode;
run;

Re: Group by user - total and average

vnreddy — Mon, 11 Jul 2022 12:01:12 GMT

@andreas_lds sorry it's my bad, updated now.

Re: Group by user - total and average

Ksharp — Mon, 11 Jul 2022 12:39:09 GMT

data want;
input REGION $ NAME $ DATE :datetime20. DCODE $ TRAVEL;
format date datetime20.;
datalines;
EAST	LEE		01JUN2022:00:00:00	AP64389	21.09
EAST	LEE		03JUN2022:00:00:00	AP64389	15.19
EAST	SAM		05JUN2022:00:00:00	PS1234	4.14
EAST	LEE		07JUN2022:00:00:00	GP38299	2.09
WEST	DAN		07JUN2022:00:00:00	CX78934	10.07
WEST	DAN		07JUN2022:00:00:00	CX78934	8.07
WEST	DAN		07JUN2022:00:00:00	PB53468	10.07
WEST	DAN		07JUN2022:00:00:00	PB53468	10.07
SOUTH	TIM		02JUN2022:00:00:00	SU18934	52.23
;
run;
proc report data=want nowd;
column  REGION  NAME DCODE n TRAVEL TRAVEL=TRAVEL1;
define REGION/group order=data;
define NAME/group;
define DCODE/group;
define n/'COUNT';
define TRAVEL/analysis sum 'TOT_TRAVEL';
define TRAVEL1/analysis mean 'AVG_TRAVEL';
compute after NAME;
REGION=' ';
endcomp;
break after NAME/summarize;
run;