## Transposing Data-

Solved
Occasional Contributor
Posts: 11

# Transposing Data-

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?

Accepted Solutions
Solution
‎10-24-2014 07:28 AM
Posts: 3,852

## Re: Transposing Data-

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;

All Replies
Solution
‎10-24-2014 07:28 AM
Posts: 3,852

## Re: Transposing Data-

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;

Occasional Contributor
Posts: 11

## Re: Transposing Data-

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.

Super User
Posts: 10,280

## Re: Transposing Data-

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super Contributor
Posts: 319

## Re: Transposing Data-

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;

🔒 This topic is solved and locked.