How to programm a function using character, numeric AND date variables

[PierreYvesILY]

hello,

I need a function which would use the following entry parameters:

- Date (format: Datetime20.)

- Qualification ($2.)

- Produkt ($25.)

- Laufzeit (4.)

and return the following Value of the variable Zins (format 3.4), stored in the dataset 'reference matrix' underneath.

Zins= Function (Date, Qualification, Produkt, Laufzeit)

The result ist determined by:

• select the lines in the dataset 'reference matrix'  where : Start_date < Date <= End_date
• select the lines in the dataset where : Laufzeit_Start < Laufzeit <= Laufzeit_End
• and the Qualification and Produkt Variables.

I have an (example) reference matrix for the results:

data Matrix_Ref(label='Konditionstableau');

input Start_date:Datetime20. End_date:Datetime20. Qualification:$2. Produkt:$25. Laufzeit_Start:4. Laufzeit_End:4. Zins:3.4;

datalines;

01JAN2009:08:00:00 31JAN2009:08:00:00 C Auto 12 36 0.02

31JAN2009:08:00:00 01JAN2015:08:00:00 D Immo 36 60 0.05

01JAN2009:08:00:00 01JAN2015:08:00:00 A FreeUse 60 72 0.04

01JAN2010:08:00:00 01JAN2015:08:00:00 E FreeUse 96 120 0.055

01JAN2012:08:00:00 01JAN2015:08:00:00 D FreeUse 12 36 0.06

01JAN2015:08:00:00 01JAN2019:08:00:00 C FreeUse 36 60 0.07

01JAN2017:08:00:00 01JAN2019:08:00:00 B Auto 36 60 0.035

01JAN2017:08:00:00 01JAN2019:08:00:00 B Immo 96 120 0.08

01JAN2019:08:00:00 01APR2019:08:00:00 B Immo 72 84 0.07

;

run;

Questions

1. The Input step doesn't work for the Date, what should I write?

2. How could I write the function? What is recommended here?

Thanks in advance,

Regards,

PY

[Jagadishkatam]

Hi PY,

Where is the date or Laufzeit that you want to use for comparison, they are not in the data. Please provide more details and also show sample expected output.

[PierreYvesILY]

The date is not provided in the dataset above because this dataset is the 'reference' one. So does the Laufzeit, the Qualification, the Produkt. They all are in the production dataset that has to be matched with the reference one to determine the result.

To simplify, I have a Production Dataset with the following columns (and lots of other parameters):

data Production_result(label='Productiondata_with_Zins');

input Date:Datetime20. Qualification:$2. Produkt:$25. Laufzeit:4. Zins:3.4;

datalines;

12JAN2009:18:00:00 C Auto 13

31JAN2009:09:00:00 D Immo 40

01JAN2009:08:45:00 A FreeUse 61 

01JAN2010:16:58:00 E FreeUse 99

01JAN2012:12:00:58 D FreeUse 28 

01JAN2015:09:15:00 C FreeUse 39

01JAN2017:08:20:00 B Auto 50

01JAN2017:08:30:00 B Immo 108

01JAN2019:08:10:00 B Immo 78

;

run;

the awaited result is to add a column 'Zins' to the production dataset, and the results would be :

data Production(label='Productiondata');

input Date:Datetime20. Qualification:$2. Produkt:$25. Laufzeit:4.;

datalines;

12JAN2009:18:00:00 C Auto 13 0.02 

31JAN2009:09:00:00 D Immo 40 0.05

01JAN2009:08:45:00 A FreeUse 61 0.04 

01JAN2010:16:58:00 E FreeUse 99 0.055

01JAN2012:12:00:58 D FreeUse 28 0.06 

01JAN2015:09:15:00 C FreeUse 39 0.07

01JAN2017:08:20:00 B Auto 50 0.035

01JAN2017:08:30:00 B Immo 108 0.08

01JAN2019:08:10:00 B Immo 78 0.07

;

run;

I hope it is a lot clearer like this.

I still don't find the correct input format for the dates.

[Jagadishkatam]

Please try this you will get the proper date format, however i wanted to know how you got the zins= 0.02 for the first record as example. If you could explain it then i can try for the code

data Production_result(label='Productiondata_with_Zins');
input Date:Datetime18. Qualification:$2. Produkt:$25. Laufzeit:4. ;
format Date datetime20.;
datalines;
12JAN2009:18:00:00 C Auto 13
31JAN2009:09:00:00 D Immo 40
01JAN2009:08:45:00 A FreeUse 61 
01JAN2010:16:58:00 E FreeUse 99
01JAN2012:12:00:58 D FreeUse 28 
01JAN2015:09:15:00 C FreeUse 39
01JAN2017:08:20:00 B Auto 50
01JAN2017:08:30:00 B Immo 108
01JAN2019:08:10:00 B Immo 78
;
run;

[PierreYvesILY]

thank you for your efforts.

To understand the result Zins = 0.02 for the first example:

1) the first entry in the production datastep is:

 Date = 12JAN2009:18:00:00 

 Qualification = C

 Produkt = Auto

 Laufzeit = 13

2) look at the Konditionstabelau (1. Datastep), and now check if you find a line where, using the values above:

Start_date < Date <= End_date

Qualification = C

Produkt = Auto

Laufzeit_Start < Laufzeit <= Laufzeit_End

The 1st line of the Konditionstabelau (1. Datastep)  is compatible with the values, and in the Zins column is the value 0.02 stored.

This value is the result to complete the Production dataset into Production_result dataset.

I hope this is clear now.

Regards,

PY

[Jagadishkatam · Accepted solution]

Hope this is the expected output check the Want dataset

data Matrix_Ref(label='Konditionstableau');
input Start_date:Datetime20. End_date:Datetime20. Qualification:$2. Produkt:$25. Laufzeit_Start:4. Laufzeit_End:4. Zins:3.4;
format Start_date End_date datetime20.;
datalines;
01JAN2009:08:00:00 31JAN2009:08:00:00 C Auto 12 36 0.02
31JAN2009:08:00:00 01JAN2015:08:00:00 D Immo 36 60 0.05
01JAN2009:08:00:00 01JAN2015:08:00:00 A FreeUse 60 72 0.04
01JAN2010:08:00:00 01JAN2015:08:00:00 E FreeUse 96 120 0.055
01JAN2012:08:00:00 01JAN2015:08:00:00 D FreeUse 12 36 0.06
01JAN2015:08:00:00 01JAN2019:08:00:00 C FreeUse 36 60 0.07
01JAN2017:08:00:00 01JAN2019:08:00:00 B Auto 36 60 0.035
01JAN2017:08:00:00 01JAN2019:08:00:00 B Immo 96 120 0.08
01JAN2019:08:00:00 01APR2019:08:00:00 B Immo 72 84 0.07
;
run;

data Production_result(label='Productiondata_with_Zins');
input Date:Datetime18. Qualification:$2. Produkt:$25. Laufzeit:4. ;
format Date datetime20.;
datalines;
12JAN2009:18:00:00 C Auto 13
31JAN2009:09:00:00 D Immo 40
01JAN2009:08:45:00 A FreeUse 61 
01JAN2010:16:58:00 E FreeUse 99
01JAN2012:12:00:58 D FreeUse 28 
01JAN2015:09:15:00 C FreeUse 39
01JAN2017:08:20:00 B Auto 50
01JAN2017:08:30:00 B Immo 108
01JAN2019:08:10:00 B Immo 78
;
run;

proc sql;
create table want as select /*a.Start_date,a.End_date,*/b.*, /*a.Qualification,a.Produkt,a.Laufzeit_Start,a.Laufzeit_End,*/a.zins from Matrix_Ref as a left join Production_result as b 
on a.Start_date < b.Date <= a.End_date and a.Qualification=b.Qualification and a.Produkt=b.Produkt and a.Laufzeit_Start < b.Laufzeit <= a.Laufzeit_End order by b.date;
quit;

[PierreYvesILY]

Thanks a lot!
I got the wished result, and can now use thi method with my actual real data.

Have a nice WE
Regards,
PY

[Jagadishkatam]

Happy to know that it helped.