Help using Base SAS procedures

Transposing Data-

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 11
Accepted Solution

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
Respected Advisor
Posts: 3,777

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;

10-24-2014 6-26-21 AM.png

View solution in original post


All Replies
Solution
‎10-24-2014 07:28 AM
Respected Advisor
Posts: 3,777

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;

10-24-2014 6-26-21 AM.png
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 Smiley Happy ). really appreciate the reply.

Super User
Posts: 6,928

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
Super Contributor
Posts: 305

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 252 views
  • 1 like
  • 4 in conversation