topic Re: changing column into rows keeping 1 row constant and repeated in SAS Data Management

changing column into rows keeping 1 row constant and repeated

Takdir — Wed, 16 Oct 2019 05:04:10 GMT

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 maturity	Y maturity	Z maturity
01-Mar	1	2	4
02-Mar	5	8	9
03-Mar	3	5	7

But this is what I want:

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

Re: changing column into rows keeping 1 row constant and repeated

PeterClemmensen — Wed, 16 Oct 2019 05:20:42 GMT

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;

Re: changing column into rows keeping 1 row constant and repeated

PeterClemmensen — Wed, 16 Oct 2019 05:24:31 GMT

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

Re: changing column into rows keeping 1 row constant and repeated

Takdir — Wed, 16 Oct 2019 06:06:31 GMT

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

Re: changing column into rows keeping 1 row constant and repeated

PeterClemmensen — Wed, 16 Oct 2019 06:14:20 GMT

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;

Re: changing column into rows keeping 1 row constant and repeated

Takdir — Wed, 16 Oct 2019 06:19:10 GMT

Thank you. You really solved a big problem of mine

Re: changing column into rows keeping 1 row constant and repeated

Takdir — Fri, 18 Oct 2019 01:26:33 GMT

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