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 |
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;
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;
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
is the value 3 after the array function for Xmaturity Ymaturity Zmaturity or is it for the number of dates that i had?
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;
Thank you. You really solved a big problem of mine
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
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9.
Lock in the best rate now before the price increases on April 1.
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.