Proc Transpose

[aj34321]

Hi there.. 

Looking for urgent help on the below. I have create data infile step for what i have and what i'm looking for thru proc transpose or directly from proc sql. 

Request if someone can help on this. 

DATA have;
LENGTH grp_key $10. dcsn_key $10. catg $10. cust_amt 4. bank_amt 4.;
INFILE DATALINES DLM='|';
INPUT grp_key $ dcsn_key $ catg $ cust_amt bank_amt;
DATALINES;
GRP101|DCSN121|Income|100|90
GRP101|DCSN121|Expense|200|250
;
RUN;

DATA want;
LENGTH grp_key $10. dcsn_key $10. cust_amt_income 4. bank_amt_income 4. cust_amt_expense 4. bank_amt_expense 4.;
INFILE DATALINES DLM='|';
INPUT grp_key $ dcsn_key $ cust_amt_income bank_amt_income cust_amt_expense bank_amt_expense;
DATALINES;
GRP101|DCSN121|100|90|200|250
;
RUN;

Thanks, Anil

[novinosrin · Accepted solution]

Hi @aj34321 

DATA have;
LENGTH grp_key $10. dcsn_key $10. catg $10. cust_amt 4. bank_amt 4.;
INFILE DATALINES DLM='|';
INPUT grp_key $ dcsn_key $ catg $ cust_amt bank_amt;
DATALINES;
GRP101|DCSN121|Income|100|90
GRP101|DCSN121|Expense|200|250
;
RUN;

proc transpose data=have out=_have;
by grp_key dcsn_key catg notsorted;
var cust_amt bank_amt;
run;
proc transpose data=_have out=want(drop=_name_) delim=_;
by grp_key dcsn_key  notsorted;
var col1;
id _name_ catg;
run;

[aj34321]

Wow... superb...  This worked exactly as i wanted.

Thanks a lot.