Merge some columns, please!

[MiguelNA]

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!

[ballardw]

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;

[UrvishShah]

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

[damanaulakh88]

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

[kuridisanjeev]

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