topic Merge some columns, please! in SAS Procedures

Merge some columns, please!

MiguelNA — Fri, 15 Feb 2013 16:04:54 GMT

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!

Re: Merge some columns, please!

ballardw — Fri, 15 Feb 2013 16:15:57 GMT

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;

Re: Merge some columns, please!

UrvishShah — Tue, 19 Feb 2013 09:27:37 GMT

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

Re: Merge some columns, please!

damanaulakh88 — Tue, 19 Feb 2013 11:08:24 GMT

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

Re: Merge some columns, please!

kuridisanjeev — Tue, 19 Feb 2013 14:20:36 GMT

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