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

umeshgiri48 — Fri, 25 Oct 2019 07:22:47 GMT

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

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

PeterClemmensen — Fri, 25 Oct 2019 07:33:34 GMT

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

topic Re: How to create cumulative of character rows on the basis of Id in SAS Programming

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

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