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

sas-innovate-white.png

Special offer for SAS Communities members

Save $250 on SAS Innovate and get a free advance copy of the new SAS For Dummies book! Use the code "SASforDummies" to register. Don't miss out, May 6-9, in Orlando, Florida.

 

View the full agenda.

Register now!

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
  • 1464 views
  • 2 likes
  • 2 in conversation