topic Re: How to convert a row based on a condition into column in SAS Programming

How to convert a row based on a condition into column

sruthi — Sat, 15 Oct 2016 06:02:13 GMT

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?

Re: How to convert a row based on a condition into column

Ksharp — Sat, 15 Oct 2016 07:05:59 GMT

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;

Re: How to convert a row based on a condition into column

sruthi — Sun, 16 Oct 2016 14:16:55 GMT

Thank you the code...Is it possible to add the two columns and produce a total column using proc tabulate?

Re: How to convert a row based on a condition into column

Ksharp — Mon, 17 Oct 2016 02:24:45 GMT

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;