topic Re: Transpose rows to columns in SAS Enterprise Guide

Transpose rows to columns

kazimir900 — Sun, 22 Sep 2019 11:44:05 GMT

Hey Guys,

i have the following problem. I have a table like this:

Contract ID Company Account-Action Amount

A12345 XXXXX Provision 30,00

A12345 XXXXX Outstanding 450,00

B12345 YYYYY .....

Now I'd like to transpose this table to:

Contract ID Company Provision Outstanding

A12345 XXXXX 30,00 450,00

B12345 YYYYY .....

Thanks a lot for yout help!!!

Lars

Re: Transpose rows to columns

novinosrin — Sun, 22 Sep 2019 12:16:44 GMT

data have;
input (ContractID       Company    Account_Action      Amount) (:$20.);
cards;
A12345             XXXXX       Provision               30,00
A12345             XXXXX       Outstanding          450,00
;

proc transpose data=have out=want(drop=_name_);
by ContractID       Company ;
id Account_Action;
var amount;
run;

Re: Transpose rows to columns

r_behata — Sun, 22 Sep 2019 16:57:46 GMT

data have;
	input (ContractID       Company    Account_Action      Amount) (:$20.);
	infile cards truncover;
	cards;
A12345             XXXXX       Provision               30,00
A12345             XXXXX       Outstanding          450,00
B12345             YYYYY
;
run;

data want;
	set have;
	by ContractID;
	retain Provision Outstanding;
	format Provision Outstanding comma8.2;
	array var[*] 8 Provision Outstanding;
	i=1;

	if first.ContractID then
		do;
			call missing(Provision,Outstanding);
			var[i]=input(Amount,comma8.2);
		end;
	else
		do;
			i+1;
			var[i]=input(Amount,comma8.2);
		end;

			if last.ContractID ;
		keep ContractID Company Provision Outstanding;
run;