DATA Step, Macro, Functions and more

How to convert a row based on a condition into column

Reply
Occasional Contributor
Posts: 6

How to convert a row based on a condition into column

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 NameAirlinesAmount
A  1ABC2Air India41302
A  1ABC2Indigo Airlines168422
A  1ABC2Jet Airways13839
A  1ABC2Kingfisher Airlines-6628
A  1ABC2Air India61346
A  1ABC2Indigo Airlines254848
A  1ABC2Jet Airways36317
A  1ABC2Kingfisher Airlines18370
A  1ABC2Spice Jet Airlines4369
Ar 1ABC2Air India7368
Ar 1ABC2Indigo Airlines44100
Ar 1ABC2Jet Airways29724
Ar 1ABC2Kingfisher Airlines6230
Bh 1ABC2Air India16254
Bh 1ABC2Indigo Airlines65653
Bh 1ABC2Jet Airways22320

 

 

 

 

This is how the ouput should be

 

 

 

 Air India and JetRest of the carriers
ClientGroup1Group2
Ca 1ABC252662697040320
Ve 1ABC228155932675886
Ma 1ABC21286686437843
Th 1ABC2723268701712
Ec 1ABC2113517627734
A  1ABC2152804439381
Ka 1ABC2341420227916
Co 1ABC2213023215554
Mi 1ABC2139090195104
Ce 1ABC224652085519
Or 1ABC2167316158024
Se 1ABC2127126192618
Dh 1ABC222073557169
Ct 1ABC272928157722
UN 1ABC259560112873

 

I tried using this code,but i couldnt get the above ouput.Can anyone help with this?

Super User
Posts: 10,035

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

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;
Occasional Contributor
Posts: 6

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

Thank you the code...Is it possible to add the two columns and produce a total column using proc tabulate?
Super User
Posts: 10,035

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

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;
Ask a Question
Discussion stats
  • 3 replies
  • 292 views
  • 1 like
  • 2 in conversation