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
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
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
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 25. 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.