Hi,
I have a dataset like this and i want to group it into two categories. then want to create two columns for the groups and put the corresponding amount to it.
Client Name | Airlines | Amount |
A 1ABC2 | Air India | 41302 |
A 1ABC2 | Indigo Airlines | 168422 |
A 1ABC2 | Jet Airways | 13839 |
A 1ABC2 | Kingfisher Airlines | -6628 |
A 1ABC2 | Air India | 61346 |
A 1ABC2 | Indigo Airlines | 254848 |
A 1ABC2 | Jet Airways | 36317 |
A 1ABC2 | Kingfisher Airlines | 18370 |
A 1ABC2 | Spice Jet Airlines | 4369 |
Ar 1ABC2 | Air India | 7368 |
Ar 1ABC2 | Indigo Airlines | 44100 |
Ar 1ABC2 | Jet Airways | 29724 |
Ar 1ABC2 | Kingfisher Airlines | 6230 |
Bh 1ABC2 | Air India | 16254 |
Bh 1ABC2 | Indigo Airlines | 65653 |
Bh 1ABC2 | Jet Airways | 22320 |
This is how the ouput should be
Air India and Jet | Rest of the carriers | |
Client | Group1 | Group2 |
Ca 1ABC2 | 5266269 | 7040320 |
Ve 1ABC2 | 2815593 | 2675886 |
Ma 1ABC2 | 1286686 | 437843 |
Th 1ABC2 | 723268 | 701712 |
Ec 1ABC2 | 113517 | 627734 |
A 1ABC2 | 152804 | 439381 |
Ka 1ABC2 | 341420 | 227916 |
Co 1ABC2 | 213023 | 215554 |
Mi 1ABC2 | 139090 | 195104 |
Ce 1ABC2 | 246520 | 85519 |
Or 1ABC2 | 167316 | 158024 |
Se 1ABC2 | 127126 | 192618 |
Dh 1ABC2 | 220735 | 57169 |
Ct 1ABC2 | 72928 | 157722 |
UN 1ABC2 | 59560 | 112873 |
I tried using this code,but i couldnt get the above ouput.Can anyone help with this?
data have;
infile cards dlm='09'x;
input ClientName : $20. Airlines :$20. Amount;
cards;
A 1ABC2 Air India 41302
A 1ABC2 Indigo Airlines 168422
A 1ABC2 Jet Airways 13839
A 1ABC2 Kingfisher Airlines -6628
A 1ABC2 Air India 61346
A 1ABC2 Indigo Airlines 254848
A 1ABC2 Jet Airways 36317
A 1ABC2 Kingfisher Airlines 18370
A 1ABC2 Spice Jet Airlines 4369
Ar 1ABC2 Air India 7368
Ar 1ABC2 Indigo Airlines 44100
Ar 1ABC2 Jet Airways 29724
Ar 1ABC2 Kingfisher Airlines 6230
Bh 1ABC2 Air India 16254
Bh 1ABC2 Indigo Airlines 65653
Bh 1ABC2 Jet Airways 22320
;
run;
proc format;
value $ fmt
'Air India','Jet Airways'='Air India and Jet'
other='Rest of the carriers';
run;
proc tabulate data=have;
class clientname airlines;
format airlines $fmt32.;
var amount;
table clientname,airlines=' '*amount=' '*sum=' ';
run;
Sure.
data have;
infile cards dlm='09'x;
input ClientName : $20. Airlines :$20. Amount;
cards;
A 1ABC2 Air India 41302
A 1ABC2 Indigo Airlines 168422
A 1ABC2 Jet Airways 13839
A 1ABC2 Kingfisher Airlines -6628
A 1ABC2 Air India 61346
A 1ABC2 Indigo Airlines 254848
A 1ABC2 Jet Airways 36317
A 1ABC2 Kingfisher Airlines 18370
A 1ABC2 Spice Jet Airlines 4369
Ar 1ABC2 Air India 7368
Ar 1ABC2 Indigo Airlines 44100
Ar 1ABC2 Jet Airways 29724
Ar 1ABC2 Kingfisher Airlines 6230
Bh 1ABC2 Air India 16254
Bh 1ABC2 Indigo Airlines 65653
Bh 1ABC2 Jet Airways 22320
;
run;
proc format;
value $ fmt
'Air India','Jet Airways'='Air India and Jet'
other='Rest of the carriers';
run;
proc tabulate data=have;
class clientname airlines;
format airlines $fmt32.;
var amount;
table clientname,airlines=' '*amount=' '*sum=' ' amount='total'*sum=' ';
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 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.