topic How to transpose the values for each fund number, or aligning? in SAS Programming

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

Prash — Wed, 19 Aug 2015 12:07:36 GMT

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.

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

LinusH — Wed, 19 Aug 2015 12:16:21 GMT

Standard PROC TRANSPOSE.

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

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

RW9 — Wed, 19 Aug 2015 12:20:43 GMT

proc transpose data=have out=want;

by fundnumr lp region cmr_lvl mass;

var goldfund silver platinum;

run;

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

Prash — Thu, 20 Aug 2015 04:41:02 GMT

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.?

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

Prash — Thu, 20 Aug 2015 04:43:40 GMT

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.

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

RW9 — Thu, 20 Aug 2015 08:09:20 GMT

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?

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

data_null__ — Thu, 20 Aug 2015 11:47:08 GMT

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!

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

Prash — Sat, 22 Aug 2015 11:13:54 GMT

Hello,

I have edited the original, hope that explains..

Thanks

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

Reeza — Sat, 22 Aug 2015 16:24:09 GMT

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;