Use DATA Step generated array in a proc compile

klasko — Thu, 14 Jun 2018 19:21:41 GMT

Hello! I've got a problem handling data.

Basically, I have to generate a cashflow for some bonds. These bonds are inflation-adjusted, so what I want to do is to get inflation data from an Excel so that I can adjust the principal. I've been able to extract the data from the Excel into a SAS table, according to some relevant criteria. I've been able to assign the data I got from the SAS Table into an array within a DATA Step. The issue comes when I want to use that data. Is there any way to import it into a pricing method generated within a PROC Compile?

I can't use macros. I need to do this from several bonds at time, and as far as I know if I make a do loop of call symputs I will only get the data for the last bond in the list.

This is the code I got so far:

%let date = "04APR2018"d;
%let path = "/data/user/Desarrollo_Martin";
libname desa "/data/user/Desarrollo_Martin";


data aa;
set desa.cer;

if ((month(fecha) = 07 or month(fecha)= 01) and  day(fecha) = 22) then
output;

run;

proc sort data= aa out= bb;
by cer;
run;

proc transpose data=bb out=cc prefix=tasas;

var CER;

run;

 
data dd;

set cc;
array ss[*] tasas1-tasas4;
array a (4);
do i = 1 to 4;
a[i] = ss[i];
end;

run;

Thanks!

Re: Use DATA Step generated array in a proc compile

Reeza — Thu, 14 Jun 2018 20:00:38 GMT

It's not really clear what you're trying to do here to me at least.

Arrays in SAS are just shortcut references. If you can explain your logic with some sample data and expected output we can probably help you find a solution. I suspect you need to use BY group processing as well, since you're doing this for multiple bonds.

Re: Use DATA Step generated array in a proc compile

klasko — Thu, 14 Jun 2018 20:17:38 GMT

My data comes like this:

Suppose the bond pays on 28/05/2018 and on 30/05/2018. Inflation between these dates is: (9.4052-9.3891)/9.3891. I need to compute the inflation for the previous payments, so that I can adjust the bond principal (it starts at 100).

The output should look like this, where Mat_Amount is the principal:

I've been able to do it using a proc sql and macros:

/* Calcula los cash-flows de Titulos */

%let date = "04APR2018"d;
%let path = "/data/user/Desarrollo_Martin";
libname desa "/data/user/Desarrollo_Martin";


data desa.filtrado;
set desa.cer;

if ((month(fecha) = 07 or month(fecha)= 01) and  day(fecha) = 22) then
output;

run;

proc sort data= desa.filtrado out= ordenado;
by cer;
run;

proc sql noprint;
select cer, count(*) into :cer1-:cer20,:cuenta  from ordenado;
*select cer into :emp_list separated by ' ' from desa.cerr;
quit;



data Titulos_Data; 
	length Currency $3 Insttype $20 ; 
	informat Fecha_emision date9. Fecha_vencimiento date9. Fecha_primer_pago date9.; 
	format Fecha_emision date9. Fecha_vencimiento date9. Fecha_primer_pago date9.;
	input insttype $ instid $ Fecha_emision Fecha_vencimiento Fecha_primer_pago Cantidad_pagos_anuales Badlar spread Principal Currency $ holding Feriadosyfinesdesemana; 
	datalines;
Titulos_tasa_fija PR15 22JUL2016 22JUL2021 04JAN2010 2 30 0  100 ARS 1 1

;



run;

data pagosano;
set titulos_data;

call symput('pagosano', Cantidad_pagos_anuales);


call symput('emision', fecha_emision);

call symput('vencimiento', fecha_vencimiento);
%global pagosano; 
%global emision;
%global vencimiento;

run;


proc risk;
	environment new = WORK.Titulos_Flujos;
	environment save;
run;

proc risk; 
	environment open = Titulos_Flujos;
		declare instvars = 
			( 
			Fecha_emision date var label = "Fecha de emision", 
			Fecha_vencimiento date var label = "Fecha de vencimiento",
			Fecha_primer_pago date var label = "Fecha del primer pago",
			Cantidad_pagos_anuales num var label = "Cantidad de pagos de interes anuales",
			Badlar num var label = "Badlar",
			spread num var label = "Tasa fija adicionada a la Badlar",
			Principal num var label = "Principal",
			Feriadosyfinesdesemana num var label= "Un uno traslada la fecha de los pagos en caso de que caigan en feriados/fines de semana, un cero no altera nada"

);

		declare riskfactors = 
			( 
			Precio num var
			);
	environment save;
run;

proc compile
	environment = Titulos_Flujos 
	outlib = Titulos_Flujos 
	package = Titulos_Flujos;

	method Titulos_tasa_fija_CF kind = price;

/* Calcula el numero de pagos usando la fecha de inicio y la de vencimiento */

%macro numerocuotas;

%let j = 1;
%let Stop = %str(N); 
%do %while (&Stop = N);
%let fecha = %sysfunc(INTNX( month, &emision., &j*(12/&pagosano), same ));
%if %sysevalf (&fecha = &vencimiento) %then %let Stop = %str(S);
%if &j =120 %then %let Stop = %str(S);
%let j=%eval(&j+1);
%end;

%global numerocuotas;
%let numerocuotas = &j;
%put &numerocuotas;



%mend numerocuotas;

%numerocuotas;

%put _all_;

Npagos = &numerocuotas -1;


_cashflow_.Num = npagos;


r = .05;
		
/* seteo de fechas */
if Feriadosyfinesdesemana = 0 then do;
			do i = 1 to NPagos;
				_cashflow_.MATDate[i] = INTNX( "month", Fecha_emision, i*(12/Cantidad_pagos_anuales), "same" );
			end;
			end;
		else do;
			do i = 1 to NPagos;
				_cashflow_.MATDate[i] = INTNX( "month", Fecha_emision, i*(12/Cantidad_pagos_anuales), "same" );
				dia = weekday(_cashflow_.MATDate[i]);   /* Dia de la semana del pago original*/
				if dia = 7 then _cashflow_.MATDate[i] = _cashflow_.MATDate[i]+ 2;
				else if dia = 1 then _cashflow_.MATDate[i] = _cashflow_.MATDate[i] + 1;
			end;
			end;
	



		/*dias entre fechas*/	

		array dias[120];


		do i = 1 to NPagos; 
			if i = 1 then do;
					dias[i] =  _cashflow_.MATDate[i] - fecha_emision;
					end;
		else do;
					dias[i] = _cashflow_.MATDate[i] - _cashflow_.MATDate[i-1];
					end;
		end;

/*variaciones CER*/
array capital[120];

array inflacion(120);



%macro pasado;
%DO I = 1 %TO &cuenta.;
inflacion&I. = ((&&cer&I.-&&cer1)/&&cer1)+1;
capital&I. = 100*inflacion&I.;
%END;
%MEND pasado;

%pasado;

%macro futuro;
%let L = &cuenta.;
%do I = &cuenta. %to &numerocuotas;
inflacion&I. = inflacion&L. * (1.01)**&I.;
capital&I. = capital&L. * inflacion&I.;
%end;
%mend futuro;

%futuro;
/*amortizacion y capital AL FINAL del periodo*/
		/* pagos */

		
%macro pagos;
%do i=1 %to &numerocuotas;
_cashflow_.MatAmt[&I.]=capital&I.;
%end;
%mend pagos;

%pagos;	

%put _all_;




	
_value_ = Precio*Principal;
endmethod;
run;






run;

/* Define Instrument Types -*/
proc risk; 
	environment open = Titulos_Flujos; 
		instrument Root1 label = "Common Variables" variables = ( currency , holding ) defaults =( holding 1 ); 
		instrument Titulos_tasa_fija label = "Titulos Tasa Fija" 
			basetype = Root1 
			variables = ( Fecha_emision, Fecha_vencimiento, Fecha_primer_pago, Cantidad_pagos_anuales, Badlar, spread, Principal, Feriadosyfinesdesemana ) 
			methods = ( price Titulos_tasa_fija_CF); 
	environment save;
run;

/*- Create Instrument Data Set -*/


/*- Create Market Data Set -*/
data marketdata; 
	input Precio; 
	datalines;
1.1
;
run;


/*- Register Instrument Data -*/ 
proc risk; 
	environment open = Titulos_Flujos; 
		instdata Titulos_Data 
			file = "Titulos_Data" 
			format = simple 
			label = "Titulos Data" 
			variables = ( insttype instid Fecha_emision Fecha_vencimiento Fecha_primer_pago Cantidad_pagos_anuales Badlar spread Principal Currency holding Feriadosyfinesdesemana ); 
	/*- Portfolio Input Lists -*/ 
		sources Titulos_List Titulos_Data; 
	environment save; 
run;

proc risk; 
	environment open = Titulos_Flujos ; 
		/*- Create Portfolio Files -*/ 
		read sources = Titulos_list out = Titulos_File; 
		/*- Register Market Data Sources After Creating Market Data -*/
		marketdata Marketdata file = Marketdata type = current;
	environment save; 
run;

proc risk; 
	environment open = Titulos_Flujos ;

		cashflow Titulos_Flujos;

		project Titulos_Flujos 
			data = Marketdata
			portfolio = Titulos_File
			analysis = Titulos_Flujos
			numeraire = ARS
			rundate = &date 
			outlib = work
			options = (outall); 

			/*- Run the Project -*/
		runproject Titulos_Flujos;

	environment save;

run;

However, it works only if I want to compute the amounts for one bond. When I add another one, the computation of both bonds is done using the macro-generated values of only one of the bonds, so it's all messed up.

Re: Use DATA Step generated array in a proc compile

Reeza — Thu, 14 Jun 2018 20:27:38 GMT

Please post data as text so we can copy/paste it.

Re: Use DATA Step generated array in a proc compile

klasko — Thu, 14 Jun 2018 20:50:54 GMT

Woops, sorry.

There goes the sample data (cerentero) and output (output).

topic Use DATA Step generated array in a proc compile in SAS Risk Management

Use DATA Step generated array in a proc compile

Re: Use DATA Step generated array in a proc compile

Re: Use DATA Step generated array in a proc compile

Re: Use DATA Step generated array in a proc compile

Re: Use DATA Step generated array in a proc compile