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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.