BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Takdir
Obsidian | Level 7

basically I have multiple column for a given date. But i wish to have them as rows keeping the date column. Example : this is the table I was given:

Date X  maturityY maturityZ maturity
01-Mar124
02-Mar589
03-Mar357

 

But this is what I want:

DatematurityRate
01-MarX1
01-MarY2
01-MarZ4
02-MarX5
02-MarY8
02-MarZ9
03-MarX3
03-MarY5
03-MarZ7
1 ACCEPTED SOLUTION

Accepted Solutions
PeterClemmensen
Tourmaline | Level 20

It is the number of elements in the array (3 variables: Xmaturity, Ymaturity and Zmaturity). You could put an asterisk in there instead like this

 

data want;
    set have;
    array _{*} Xmaturity Ymaturity Zmaturity;
    do i=1 to dim(_);
        Maturity = char(vname(_[i]), 1);
        Rate     = _[i];
        output;
    end;

    keep Date Maturity Rate;
run;

View solution in original post

6 REPLIES 6
PeterClemmensen
Tourmaline | Level 20
data have;
input Date $ Xmaturity Ymaturity Zmaturity;
datalines;
01-Mar 1 2 4
02-Mar 5 8 9
03-Mar 3 5 7
;

proc transpose data=have out=want(rename=(_name_=Maturity col1=rate));
    by Date;
run;
PeterClemmensen
Tourmaline | Level 20

Or

 

data have;
input Date $ Xmaturity Ymaturity Zmaturity;
datalines;
01-Mar 1 2 4
02-Mar 5 8 9
03-Mar 3 5 7
;

data want;
    set have;
    array _{3} Xmaturity Ymaturity Zmaturity;
    do i=1 to dim(_);
        Maturity = char(vname(_[i]), 1);
        Rate     = _[i];
        output;
    end;

    keep Date Maturity Rate;
run;

Result:

 

Date    Maturity Rate
01-Mar	X	     1
01-Mar	Y	     2
01-Mar	Z	     4
02-Mar	X	     5
02-Mar	Y	     8
02-Mar	Z	     9
03-Mar	X	     3
03-Mar	Y	     5
03-Mar	Z	     7
Takdir
Obsidian | Level 7

is the value 3 after the array function for Xmaturity Ymaturity Zmaturity or is it for the number of dates that i had?

PeterClemmensen
Tourmaline | Level 20

It is the number of elements in the array (3 variables: Xmaturity, Ymaturity and Zmaturity). You could put an asterisk in there instead like this

 

data want;
    set have;
    array _{*} Xmaturity Ymaturity Zmaturity;
    do i=1 to dim(_);
        Maturity = char(vname(_[i]), 1);
        Rate     = _[i];
        output;
    end;

    keep Date Maturity Rate;
run;
Takdir
Obsidian | Level 7

Thank you. You really solved a big problem of mine Smiley Very Happy

Takdir
Obsidian | Level 7

in reference to 

Maturity = char(vname(_[i]), 1);

if my variable name is "mm_100" then how do i extract the number from it ?

for example in this case maturity=100 when get the rows

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 6 replies
  • 898 views
  • 2 likes
  • 2 in conversation