How to transpose the values for each fund number, or aligning?

[Prash]

I have a dataset where I should perform transpose, the data I have look like this( have ).

Data have;

input

Fundnumr LP Region$ Cmr_lvl     Mass$     Goldfund     Silver     Platinum Goldpremval    SilverPremval    Platinumpremval; cards;

9090024    7    ME    1    CCNl     12000      1500      500000      145      233      1343

9090025    7    PL    2    CCNA     150000      3410      750000      146      234      1344

9090026    7    SK    1    DME     300000      4389      300000      147      235      1345

9090027    7    So    2    DPN     150000      2882      750000      148      236      1346

9090028    8    NE    3    DPN     150000      2398      750000      149      237      1347

9090046    8    NW    3    CCNA     150000      2882      750000      150      238      1348

9090048    7    No    3    CCNl     150000      2882      750000      151      239      1349

9090049    7    NE    4    CCNA     150000      3410      750000      152      240      1350

9090050    3    So    -    DME     300000      3993      300000      153      241      1351

9090051    9    So    1    DPN     150000      2882      750000      154      242      1352

9090052    7    NE    1    CCNl     150000      3410      750000      155      243      1353

9090053    7    NW    1    CCNA     300000      6710      300000      156      244      1354

9090055    7    NW    2    DME     150000      2398      750000      157      245      1355

9090056    7    ME    2    DPN     150000      2170      750000      158      246      1356

9090057    8    PL    2    NBF     300000      4270      300000      159      247      1357

9090058    8    SK    3    DPN     300000      2793      300000      160      248      1358

9090059    7    So    3    DME     150000      1834      750000      161      249      1359

run;

Now here I want to transpose the gold fund, Silver and Platinum into single variable called Fund, that is I want to create two new columns Value and Fund , where value as the amounts for the respective fund.

and followed by that the premval for gold platinum and Silver also needs to be transposed into a new column named premval for the appropriate funds.

Something like this in SAS, ( want), here all the vairables from Lp to Mass remains same for each fund number( fund number 9090024 repeating thrice for each fund once having the fundvalue transposed as fund and premval of each funds transposed into premval).

Data want;

input

Fundnumr LP       Region$    Cmr_lvl  Mass$     Value       Fund$      Premval;   cards;

9090024      7     ME            1     CCNl      12000      Goldfund   145

9090024      7     ME            1     CCNl      1500       Silver     233

9090024      7     ME            1     CCNl      500000     Platinum   1343

9090025      7     PL            2     CCNA      150000     Goldfund   146

9090025      7     PL            2     CCNA      3410       Silver     234

9090025      7     PL            2     CCNA      750000     Platinum   1344

9090026      7     SK            1     DME       300000     Goldfund   147

9090026      7     SK            1     DME       4389       Silver     235

9090026      7     SK            1     DME       30000      Platinum   1345

9090027      7     So            2     DP        150000     Goldfund   148

9090027      7     SK            1     DME       2882       Silver     236

9090027      7     So            2     DP        750000     Platinum   1346

run;

How to proceed, I tried transposing and with some If statements but was not successful.

[LinusH]

Standard PROC TRANSPOSE.

Or a simple data step with assignment and explicit output statement will do the job.

[Prash]

Hello Linus,

Thanks for your answer, I got the out put, however there is need of adding another column, that is I have 3 columns goldprem,silverprem,Platinumprem, now these columns also should get transposed to the respective fund.?

[RW9]

proc transpose data=have out=want;

  by fundnumr lp region cmr_lvl mass;

  var goldfund silver platinum;

run;

[Prash]

Hello Rw9,

I got the output, But now I need another extra column to be added from already 3 columns present, I have goldprem, silverprem, platinumprem, now these columns also need to get transposed to the respective funds?

I tried adding the goldprem, silverprem, platinumprem to VAR but did not get the required result.

[RW9]

Hi,

Please post some test data in the form of a datastep, and required output.  I am not following what you say, and this doesn't appear in the data?

[Prash]

Hello,

I have edited the original, hope that explains..

Thanks

[data_null__]

The FUNDs or variables (Goldfund  Silver Platinum) are stored by PROC TRANSPOSE in the variable _NAME_.  If you want to change the name of _NAME_ to FUND you can use the NAME= option on the PROC TRANSPOSE statement.  Similarly if the VARs have labels the labels are stored in a variable whose default name is _LABEL_ but can be changed with the LABEL= option on the PROC statement.

Your mileage will increase when you RTM!

[Reeza]

An array is better or you would need to use two transposes and merge the results.

data want;

set have;

array metal(3) goldfund silver  platinum;

array val(3) goldpremvalue silverpremvalue platinumpremvalue;

do i=1 to 3;

value=metal(i);

premvalue=val(i);

fund=vname(val(i));

output;

end;

drop gold: silver: platinum:;

run;