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

I have a dataset as such:

 

I have a dataset which looks like this:

ID      2017    2018    2019    2020

2017    30      24      20      18
2018    30      24      20      18
2019    30      24      20      18
2020    30      24      20      18

I am looking to create an array based on a few inputs:

%let FixedorFloating = '1 or 0';
%let Repricingfrequency = n Years;
%let LastRepricingDate = 'Date'n;

 The array criteria is such that if ID= Year +2i then flag = 1

 

e.g.

 

ID = 2017 then flag =1 for Years 2017 and 2019, 0 otherwise

ID = 2018 then flag = 1 for Years 2018 and 2020, 0 otherwise

ID = 2019 then flag = 1 for Year 2019 , 0 otherwise

ID = 2020 then flag = 1 for year 2020, 0 otherwise

 

 

My code is currently, I'm having issues with year i+2 (highlighted in red) but year(i) works fine.

 

data ReferenceRateContract;
	set refratecontract;

*arrays for years and flags;
	array _year(2017:2022) year2017-year2022;
	array _flag(2017:2022) flag2017-flag2022;

*loop over array;

	if &FixedorFloating=1

		then do i=&dateoflastrepricing to hbound(_year);
    	
    	/*check if year matches year in variable name*/
  
  		if put(ID, 4.) = compress(vname(_year(i)),, 'kd') 
  			then _flag(i)=1;
  
  		else _flag(i)=0;
	
	end;

	else if &fixedorfloating=0

		then do i=&dateoflastrepricing to hbound(_year);
  
  		if put(ID, 4.) = compress(vname(_year(i)),, 'kd') 
  			then _flag(i)=1;
  
  		else if put(ID, 4.) = compress(vname(_year(i+2)),, 'kd') 
  		then _flag(i)=1;
  
  		else _flag(i)=0;
	end;

	drop i;

run;

data referenceratecontract;
set referenceratecontract;
keep flag2017--flag2020;
run;

 

Looking for a way to flag based on Id= Year + 2i, TIA

 

else if put(ID, 4.) = compress(vname(_year(i+2)),, 'kd') then _flag(i)=1;

1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

Your loop...

do i=&dateoflastrepricing to hbound(_year);

....iterates until the very last element of the array....

array _year(2017:2022) year2017-year2022;

....and though already in the second last iteration...

 

_year(i+2)

...will point to an array element which hasn't been defined. That's why you get the error.

 

View solution in original post

6 REPLIES 6
RW9
Diamond | Level 26 RW9
Diamond | Level 26

As with any question like this, the first question I always ask is why the data looks like this in the first place.  Bad data modelling results in the rest of the process being sub-optimal.  So first quesiton, why is ID a year value?  Next, why are years transposed?  Normalised data is far simpler, and makes this (as with the hundreds of other questions on here using transposed data) so simple:

ID       YEAR  VALUE

2017   2017    30

2017   2018    24

...

 

 

89974114
Quartz | Level 8

ID identifies the vintage of the bond , the columns specify basis points for each year

 

        2017        2018        2019        2020
17    bp1          bp2          bp3          bp4
18    bp1          bp2 ...
...
Kurt_Bremser
Super User

Turn your dumb data into intelligent data first by transposing, then perform a simple sequential data step:

data have;
input ID val_2017 val_2018 val_2019 val_2020;
cards;
2017    30      24      20      18
2018    30      24      20      18
2019    30      24      20      18
2020    30      24      20      18
;
run;

proc transpose
  data=have
  out=int (rename=(col1=value))
;
by id;
var val_:;
run;

data have_good;
set int;
year = input(substr(_name_,5),best.);
drop _name_;
run;

data want;
set have_good;
retain firstyear;
by id;
if first.id
then do;
  firstyear = year;
  flag = 1;
end;
else if year = firstyear + 2
then flag = 1;
else flag = 0;
drop firstyear;
run;

You may need to tweak the condition where you set firstyear and start analysing.

Patrick
Opal | Level 21

Your loop...

do i=&dateoflastrepricing to hbound(_year);

....iterates until the very last element of the array....

array _year(2017:2022) year2017-year2022;

....and though already in the second last iteration...

 

_year(i+2)

...will point to an array element which hasn't been defined. That's why you get the error.

 

89974114
Quartz | Level 8

Thanks, I'll have to re-write the dimensions and loop

89974114
Quartz | Level 8

I changed the dimensions with a macro variable and the problem was that I was putting I+2 instead of I-2, I-2 is what is required for my desired impact.

 

thanks.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 1039 views
  • 0 likes
  • 4 in conversation