BookmarkSubscribeRSS Feed
Prash
Calcite | Level 5

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.

8 REPLIES 8
LinusH
Tourmaline | Level 20

Standard PROC TRANSPOSE.

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

Data never sleeps
Prash
Calcite | Level 5

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
Diamond | Level 26 RW9
Diamond | Level 26

proc transpose data=have out=want;

  by fundnumr lp region cmr_lvl mass;

  var goldfund silver platinum;

run;

Prash
Calcite | Level 5

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Calcite | Level 5

Hello,

I have edited the original, hope that explains..

Thanks

data_null__
Jade | Level 19

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
Super User

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 754 views
  • 0 likes
  • 5 in conversation