One way:
data have;
input WOT $ Nb Mbr Type $;
datalines;
A 5 100 PYGT
B 2 20 PYGM
C 1 3 CLAS
D 20 400 FGP
E 1 80 TPEO
F 3 10 TPEM
G 1 3 WCQ
H 20 400 VXZ
I 6 140 DIX
Z 30 600 YNR
;
data temp;
set have;
if wot in ('A' 'B' 'E' 'F') then type=substr(type,1,3);
run;
proc format;
value $wotgroup
'A','B' = 'A-B'
'E','F' = 'E-F'
;
proc summary data=temp nway;
class wot type;
format wot $wotgroup.;
var nb mbr;
output out=want (drop=_:) sum=;
run;
The first data set just provides something to code against.
The second uses the apparent rule to trim the Type to 3 characters for some values. This might need to be changed for more complex "rules". If there is only one value to be associated with the WOT (grouped or not) then another approach would be create a second format.
The Proc format creates groups for values of WOT. The groups created with formats will be honored by analysis, reporting and most graphing procedures (exceptions are mostly custom date/time/datetime formats).
Proc summary with CLASS creates groups of values, by could be used as well. The output requests the sum of the variables for the grouped WOT / Type combinations.
Caution: The underlying values in the WOT variable is still extremely likely to be 'A' and 'E' but the format will display the text as shown.
This actually can overcome some issues not previously addressed. If your WOT variable is only one character in length there is no way to stuff 'A-B' into it and more work involving creating a longer variable would be needed. The Format will display just about any text you want for the value. Which is another option of using a format for the TYPE as well to trim the displayed value.
Or possible create reports from the Temp data set:
proc tabulate data=temp;
class wot type;
format wot $wotgroup.;
var nb mbr;
table wot*type,
(nb mbr)* sum=' '*f=best5.
;
run;
proc report data=temp;
columns wot nb mbr type;
define wot /group format=$wotgroup.;
define type/group;
run;
Click image to register for webinar
