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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.