How to create cumulative of character rows on the basis of Id

[umeshgiri48]

Hi,

I would like to create a new column i.e Rev_Class on the basis of a Class variable across Id, Item, Item Qty, Class for eg:-

Id	Item	Item Qty	Class	Rev_Class
1	LARGE COMBO - POPCORN SALTED + COKE	1	F&B	F&B
2	ROYAL COMBO-POPCORN TUB CHEESE+COKE (L)	1	F&B	F&B+B
2	THUMS UP LARGE	2	B	F&B+B

In this case Id 1 has only one class i.e F&B so the Rev_class will be F&B but in the case of Id - 2 it has two different class on the basis of item i.e F&B and B, in that case, the Rev_class for Id 2 is F&B+B.

I am not getting any idea of how to do this, please help me on this. I am also attaching the sample file for the ref.

kind regards

[PeterClemmensen · Accepted solution]

Do like this

data have;
input Id Item $ 4-44 ItemQty Class $ 47-50;
infile datalines dlm=',';
datalines;
1 ,LARGE COMBO - POPCORN SALTED + COKE     ,1,F&B
2 ,ROYAL COMBO-POPCORN TUB CHEESE+COKE (L) ,1,F&B
2 ,THUMS UP LARGE                          ,2,B
3 ,POPCORN CHEESE TUB                      ,1,F
4 ,WOW COMBO - POPCORN SALTED + COKE (S)   ,2,F&B
5 ,WOW COMBO - POPCORN SALTED + COKE (S)   ,3,F&B
5 ,SAMOSA (2PCS.)                          ,1,F
6 ,BURGRER VEGETABLE CHEESE                ,1,F
6 ,DIET COKE CANS 330 ML                   ,1,B
6 ,MINUTE MAID PULPY ORANGE CAN (300 ML)   ,1,B
7 ,SAMOSA (2PCS.)                          ,1,F
8 ,SAMOSA (2PCS.)                          ,1,F
9 ,LARGE COMBO - POPCORN SALTED + COKE     ,1,F&B
10,LARGE COMBO - POPCORN SALTED + COKE     ,1,F&B
11,WOW COMBO - POPCORN SALTED + COKE (S)   ,1,F&B
12,COKE LARGE                              ,1,B
12,POPCORN SALTED TUB                      ,1,F
12,SCHWEPPES (1LTR)                        ,1,B
13,POPCORN SALTED LARGE                    ,1,F
14,POPCORN CHEESE LARGE                    ,3,F
14,POPCORN SALTED LARGE                    ,1,F
15,SCHWEPPES (1LTR)                        ,1,B
16,CAPPUCINO PREMIX GEORGIA                ,1,B
17,FAMILY COMBO-POPCORN CHEESE+ COKE+SAMOSA,1,F&B
18,WOW COMBO - POPCORN CHEESE + COKE (S)   ,1,F&B
19,WOW COMBO - POPCORN SALTED + COKE (S)   ,1,F&B
20,COKE LARGE                              ,1,B
21,WOW COMBO - POPCORN CHEESE + COKE (S)   ,4,F&B
22,ROYAL COMBO-POPCORN TUB CHEESE+COKE (L) ,1,F&B
23,SCHWEPPES 500 ML                        ,1,B
24,MINUTE MAID PULPY ORANGE CAN (300 ML)   ,1,B
25,KETTLE CHIPS - NAKED SEASALT            ,1,F
26,SCHWEPPES (1LTR)                        ,1,B
27,DIET COKE CANS 330 ML                   ,4,B
28,WOW COMBO - POPCORN SALTED + COKE (S)   ,2,F&B
29,POPCORN CHEESE LARGE                    ,2,F
29,SANDWICH CHEESE CHUTNEY                 ,1,F
30,COKE LARGE                              ,1,B
30,WOW COMBO - POPCORN SALTED + COKE (S)   ,1,F&B
30,SAMOSA (2PCS.)                          ,2,F
31,COOKIE CAKE CHOCOLATE                   ,4,F
32,WOW COMBO - POPCORN SALTED + COKE (S)   ,1,F&B
33,POPCORN CHEESE LARGE                    ,1,F
34,WOW COMBO - POPCORN SALTED + COKE (S)   ,3,F&B
35,LARGE COMBO - POPCORN CHEESE +TH UP     ,1,F&B
36,PIZZA FARM HOUSE (9INCH)                ,1,F
37,COOKIE CAKE CHOCOLATE                   ,5,F
38,LARGE COMBO - POPCORN SALTED + COKE     ,1,F&B
39,SAMOSA (2PCS.)                          ,2,F
;

data want;
    do until (last.id);
        set have;
        length Rev_Class $100;
        by id;
        Rev_Class=catx('+', Rev_Class, Class);
    end;

    do until (last.id);
        set have;
        by id;
        output;
    end;
run;

Result:

Id   Item                                         ItemQty Class  Rev_Class
1    LARGE COMBO - POPCORN SALTED + COKE          1       F&B    F&B
2    ROYAL COMBO-POPCORN TUB CHEESE+COKE (L)      1       F&B    F&B+B
2    THUMS UP LARGE                               2       B      F&B+B
3    POPCORN CHEESE TUB                           1       F      F
4    WOW COMBO - POPCORN SALTED + COKE (S)        2       F&B    F&B
5    WOW COMBO - POPCORN SALTED + COKE (S)        3       F&B    F&B+F
5    SAMOSA (2PCS.)                               1       F      F&B+F
6    BURGRER VEGETABLE CHEESE                     1       F      F+B+B
6    DIET COKE CANS 330 ML                        1       B      F+B+B
6    MINUTE MAID PULPY ORANGE CAN (300 ML)        1       B      F+B+B
7    SAMOSA (2PCS.)                               1       F      F
8    SAMOSA (2PCS.)                               1       F      F
9    LARGE COMBO - POPCORN SALTED + COKE          1       F&B    F&B
10    LARGE COMBO - POPCORN SALTED + COKE         1       F&B    F&B
11    WOW COMBO - POPCORN SALTED + COKE (S)       1       F&B    F&B
12    COKE LARGE                                  1       B      B+F+B
12    POPCORN SALTED TUB                          1       F      B+F+B
12    SCHWEPPES (1LTR)                            1       B      B+F+B
13    POPCORN SALTED LARGE                        1       F      F
14    POPCORN CHEESE LARGE                        3       F      F+F
14    POPCORN SALTED LARGE                        1       F      F+F
15    SCHWEPPES (1LTR)                            1       B      B
16    CAPPUCINO PREMIX GEORGIA                    1       B      B
17    FAMILY COMBO-POPCORN CHEESE+ COKE+SAMOSA    1       F&B    F&B
18    WOW COMBO - POPCORN CHEESE + COKE (S)       1       F&B    F&B
19    WOW COMBO - POPCORN SALTED + COKE (S)       1       F&B    F&B
20    COKE LARGE                                  1       B      B
21    WOW COMBO - POPCORN CHEESE + COKE (S)       4       F&B    F&B
22    ROYAL COMBO-POPCORN TUB CHEESE+COKE (L)     1       F&B    F&B
23    SCHWEPPES 500 ML                            1       B      B
24    MINUTE MAID PULPY ORANGE CAN (300 ML)       1       B      B
25    KETTLE CHIPS - NAKED SEASALT                1       F      F
26    SCHWEPPES (1LTR)                            1       B      B
27    DIET COKE CANS 330 ML                       4       B      B
28    WOW COMBO - POPCORN SALTED + COKE (S)       2       F&B    F&B
29    POPCORN CHEESE LARGE                        2       F      F+F
29    SANDWICH CHEESE CHUTNEY                     1       F      F+F
30    COKE LARGE                                  1       B      B+F&B+F
30    WOW COMBO - POPCORN SALTED + COKE (S)       1       F&B    B+F&B+F
30    SAMOSA (2PCS.)                              2       F      B+F&B+F
31    COOKIE CAKE CHOCOLATE                       4       F      F
32    WOW COMBO - POPCORN SALTED + COKE (S)       1       F&B    F&B
33    POPCORN CHEESE LARGE                        1       F      F
34    WOW COMBO - POPCORN SALTED + COKE (S)       3       F&B    F&B
35    LARGE COMBO - POPCORN CHEESE +TH UP         1       F&B    F&B
36    PIZZA FARM HOUSE (9INCH)                    1       F      F
37    COOKIE CAKE CHOCOLATE                       5       F      F
38    LARGE COMBO - POPCORN SALTED + COKE         1       F&B    F&B
39    SAMOSA (2PCS.)                              2       F      F