topic Re: Transposing Data- in SAS Procedures

Transposing Data-

erickbernard — Fri, 24 Oct 2014 10:56:42 GMT

Hi everyone,

I'm trying to transpose the following table into a certain format described below. I haven't been able to figure it out, so decided to seek some advice. So here's what data looks like;

field1	field2	date
CompanyABC	company	Mar-14
432	asset	Mar-14
54534	liabilities	Mar-14
4324	income	Mar-14
2324	expenses	Mar-14
CompanyABC	company	Jun-14
5435	asset	Jun-14
3435	liabilities	Jun-14
987	income	Jun-14
345	expenses	Jun-14
CompanyDEF	company	Mar-14
533	asset	Mar-14
6534	liabilities	Mar-14
5478	income	Mar-14
3456	expenses	Mar-14
CompanyDEF	company	Jun-14
754	asset	Jun-14
435	liabilities	Jun-14
343	income	Jun-14
453	expenses	Jun-14

and this is what I'm after;

	company	Mar-14	Jun-14
asset	CompanyABC	432	5435
liabilities	CompanyABC	54534	3435
income	CompanyABC	4324	987
expenses	CompanyABC	2324	345
asset	CompanyDEF	533	754
liabilities	CompanyDEF	6534	435
income	CompanyDEF	5478	343
expenses	CompanyDEF	3456	453

Can anyone help?

Re: Transposing Data-

data_null__ — Fri, 24 Oct 2014 11:28:29 GMT

I think I might want the new "date" variable to be numeric but you did not specify and the following does not do that. That could be fixed.

I named the column you did not label "Type" to show how the column can be named in PROC TRANSPOSE>

data company;
   infile cards dsd; 
   input (field1-field2)(:$16.) date :$6.; 
   cards; 
CompanyABC,company,14-Mar
432,asset,14-Mar
54534,liabilities,14-Mar
4324,income,14-Mar
2324,expenses,14-Mar
CompanyABC,company,14-Jun
5435,asset,14-Jun
3435,liabilities,14-Jun
987,income,14-Jun
345,expenses,14-Jun
CompanyDEF,company,14-Mar
533,asset,14-Mar
6534,liabilities,14-Mar
5478,income,14-Mar
3456,expenses,14-Mar
CompanyDEF,company,14-Jun
754,asset,14-Jun
435,liabilities,14-Jun
343,income,14-Jun
453,expenses,14-Jun
;;;;
   run; 
proc transpose data=company out=company2;
   by date notsorted; 
   var field1;
   id field2;
   run; 
proc transpose data=company2 out=company3 name=Type;
   by company;
   var asset--expenses;
   id date;
   run; 
proc print; 
   run;

Re: Transposing Data-

Kurt_Bremser — Fri, 24 Oct 2014 11:28:53 GMT

Quick, without testing:

data intermed;

set have;

retain company;

if field2 = "company" then company = field1;

else output;

run;

proc sort data=intermed;

by company field2;

run;

data want (keep=company category mar_14 jun_14);

set intermed (rename=(field2=category));

by company category;

if first.category

then do;

mar_14 = 0;

jun_14 = 0;

end;

select (date);

when ('Mar-14') mar_14 = field1;

when ('Jun-14') jun_14 = field1;

otherwise;

end;

if last.category then output;

run;

In the last data step, the possible dates may be covered by a suitable macro.

Re: Transposing Data-

erickbernard — Fri, 24 Oct 2014 12:06:51 GMT

i think this is it. I never really thought I could do this in two steps (to me it seems ingenious now ). really appreciate the reply.

Re: Transposing Data-

Loko — Fri, 24 Oct 2014 13:45:21 GMT

Hello,

Another solution:

proc sql noprint;
select distinct cat('_' ,substr(date,1,2),substr(date,4,3)) into :datevar separated by ' ' from have ;
select distinct cat('max(_' ,substr(date,1,2),substr(date,4,3),') as _',substr(date,1,2),substr(date,4,3)) into :datevarsql separated by ',' from have ;
quit;
%put &datevar &datevarsql;

data int1;
retain compint ;
array trdata{*} $ &datevar;
set have;
if notdigit(strip(field1)) then compint=field1;
else
do;
financial_data=field2;
company=compint;
do i=1 to &sqlobs ;
if (vname(trdata{i})=cat('_' ,substr(date,1,2),substr(date,4,3))) then trdata{i}= field1;
end;
output;
end;

drop compint field1 field2 date i;
run;

proc sql noprint;
create table want as
select company,financial_Data, &datevarsql
from int1
group by company,financial_Data;
quit;