Hey everyone,
I have some problem with SAS. I want to merge some columns to one and create another column that it has the different years, for exmple:
Bank Assets2012 Assets2011 Assets2010 Liabilities2012 Liabilities2011 Liabilities2010
JP 1 3 5 2 4 6
City 10 11 13 12 14 16
Result:
Bank year Assets Liabilities
JP 2012 1 2
JP 2011 3 4
JP 2010 5 6
City 2012 10 12
City 2011 11 14
City 2010 13 16
Thanks everyone!
data want (keep=bank year assets liabilities);
set have; /* the data set you already have*/
year=2010; assets=assets2010;liabilities=Liabilities2010;output;
year=2011; assets=assets2011;liabilities=Liabilities2011;output;
year=2012; assets=assets2012;liabilities=Liabilities2012;output;
run;
Hello,
Try by using ARRAY statement...
data test;
retain bank year assets libilities;
array asset (3) assets1 assets2 assets3;
array lib (3) liabilities1 liabilities2 liabilities3;
do i = 1 to 3;
assets = asset(i);
liabilities = lib(i);
_temp = vname(asset(i));
year = input(substr(_temp,7),4.);
output;
end;
run;
Thanks,
Urvish
Hi MigueANA,
Please find below some more dynamic code :-
==================================
%macro trns();
data want(keep=bank year assets liabilities);
set have;
%do i=0 %to 2; /* You need to change the loop her if you want to add more years*/
year=201&i.;
assets=assets201&i.;
liabilities=Liabilities201&i.;output;
%end;
run;
%mend;
%trns;
proc print data=want;
run;
===================================
Output:-
================================
Obs Bank year assets liabilities
1 JP 2010 5 6
2 JP 2011 3 4
3 JP 2012 1 2
4 City 2010 13 16
5 City 2011 11 14
6 City 2012 10 12
===================================================
/Daman
Hi ..
Here is the alternate approach ..
Data Have;
input Name$ product $ unit volume;
cards;
A1 CC 6 0
A1 PL 2 116650
A1 SL 1 38930
A2 CC 9 0
A2 SL 1 20588
A3 CC 4 0
A4 PL 13 523137
A4 SL 2 90423
A5 CC 2 0
A5 PL 4 176813
A5 SL 1 53988
;
run;
proc print;
run;
proc transpose data=have out=middle ;
by name product ;
run;
Data Ass Lib;
set middle;
if substr(_NAME_,1,1)="A" then output ASs;
else output Lib;
run;
Data Want(Drop=_name_);
Retain Bank Year Assets Liabilities;
merge Ass(Rename=Col1=Assets) Lib(Rename=Col1=Liabilities);
Year=Substr(_name_,2);
run;
Regards..
Sanjeev.k
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.