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.
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;
Why is Lee with Dcode = PS1234 ignored?
@andreas_lds sorry it's my bad, updated now.
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: