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?
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>
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>
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.
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.
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.