topic Re: How to programm a function using character, numeric AND date variables in New SAS User

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

PierreYvesILY — Fri, 06 Sep 2019 10:13:18 GMT

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,

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

Jagadishkatam — Fri, 06 Sep 2019 10:41:13 GMT

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.

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

PierreYvesILY — Fri, 06 Sep 2019 11:06:33 GMT

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.

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

Jagadishkatam — Fri, 06 Sep 2019 11:26:22 GMT

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;

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

PierreYvesILY — Fri, 06 Sep 2019 11:36:59 GMT

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,

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

Jagadishkatam — Fri, 06 Sep 2019 12:30:01 GMT

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;

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

PierreYvesILY — Fri, 06 Sep 2019 12:47:25 GMT

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

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

Jagadishkatam — Fri, 06 Sep 2019 12:48:35 GMT

Happy to know that it helped.