Re: How to compute the exposure of an observation between two dates?

adil256 · Posted 06-03-2019 06:29 AM

Hi everyone,

I come today with a problem that I can't resolve because I don't know where to start.

I would like to compute the total exposure of portofolio per specific year and per YearXmonth for the x last years.

For instance, if a contract starts in 1st January 2018 and ends in 15 november 2018, the contract has been exposed 11 months or 11/12years or 318 days for 2018. In january 2018, the contract has been exposed during 1 month, 1/12 years or 31 days, ...

In 2019, the contract has not been exposed at all.

Here is the information I have:

data have;
length ID Status $ 40;
input Id  Status $ Date_of_effect Date_Status ;

informat Date_of_effect Date_Status date9.;
format Date_of_effect Date_Status date9.;
infile datalines missover;
datalines;
1 Ongoing 05jul2015 05jul2015
1 Transformation 23Oct2014 05jul2015 
2 Death 07jan2017 02dec2018 
3 Finished 13feb2005 13feb2015
4 NoEffect 06nov2016 06nov2016
5 Cancellation 08MAR2014 19dec2018
6 Pending 21oct2018 21oct2018
;

run;

ID is the contract number

Status is the current status of the contract

Date_of_effect is the date where the contract is effective and begin to be exposed.

Date_Status is the date where the status has been changed.

For contracts with a Status "Noeffect", "Ongoing" or "Pending", the date_of_effect equals the Status_date. For Ongoing and pending contracts I need to compute the exposure at the today date. So, I think that I need apply different rules for those contracts. For the other Status, the contract has been exposed between the Date_of_effect and Date_Status.

I want to create 3 differents tables where the first table summarizes the total exposure per year expressed in days month and year.

data Want_1;

input Year  Sum_Exposition_in_year Sum_Exposition_in_months Sum_Exposition_in_days;

infile datalines missover;
datalines;
2014
2015
2016
2017
2018
2019
;

run;

The second table is basically the same except that I introduce an additional segmentation:

data Want_2;

input Year month Sum_Exposition_in_year Sum_Exposition_in_months Sum_Exposition_in_days;

infile datalines missover;
datalines;
2014 1
2014 2
2014 3
2014 4
2014 5
2014 6
2014 7
2014 8
2014 9
2014 10
2014 11
2014 12
2015 1
2015 2
2015 3
2015 4
2015 5
2015 6
2015 7
2015 8
2015 9
2015 10
2015 11
2015 12
... ...
;

run;

And finally, I want to compute the exposure per contract for several years expressed in days and month.

data Want_3;
length ID Status $ 40;
input Id  Status $ Date_of_effect Date_Status Expo_days_2015 Expo_days_2016 Expo_days_2017 Expo_days_2018 Expo_days_2019 Expo_month_2015 Expo_month_2016 Expo_month_2017 Expo_month_2018 Expo_month_2019 ;

informat Date_of_effect Date_Status date9.;
format Date_of_effect Date_Status date9.;
infile datalines missover;
datalines;
1 Ongoing 05jul2015 05jul2015 179 365 365 365 153 6 12 12 12 6 
1 Transformation 23Oct2014 05jul2015 
2 Death 07jan2017 02dec2018 
3 Finished 13feb2005 13feb2015
4 NoEffect 06nov2016 06nov2016
5 Cancellation 08MAR2014 19dec2018
6 Pending 21oct2018 21oct2018
;

run;

I apologize if I haven't been clear enough. Don't hesitate to ask me more details.

Thank you in advance.

adil256 · Posted 06-03-2019 10:42 AM

%macro get_test;
data new ( drop=i);
set have;
%do i=2015 %to 2019;

if Status = "ongoing" or Status="Pending" then
Date_Status=today();

Expo_&i = min(mdy(12,31,i),Date_Status)-max(Date_of_effect,mdy(1,1,i));


%end;
run;

%mend;
%get_test;

I developed the following code but doesn't seem to work.

andreas_lds · Posted 06-03-2019 11:02 AM

Can you add the expected values for all variables in the want-datasets, so that it is easier to understand the logic.

I am sure that no macro-code is necessary to get the wanted datasets, i can't develop a solution right now, it's time to go home 😉

adil256 · Posted 06-03-2019 12:29 PM

Hi @andreas_lds,

I've attached an excel file with the results I want to achieve. I succeed to get the results in excel but when it comes to SAS, the logic is completely different. With my current knowledge of the program, it's quite difficult to reproduce same results.

I'm eager to see how you can get the same results without any macro-code.

Thank you and good evening

data Want_1;

input Year  Sum_Exposition_in_days Sum_Exposition_in_months Sum_Exposition_in_years;

infile datalines missover;
datalines;
2015 773 25 2.08
2016 732 24 2.00
2017 1089 36 3.00
2018 1124 37 3.0833
2019 306 10 0.833
;

run;

data Want_2;

input Year month Sum_Exposition_in_days Sum_Exposition_in_months Sum_Exposition_in_years;

infile datalines missover;
datalines;
2015 1 93
2015 2 68
2015 3 62
2015 4 60
2015 5 62
2015 6 60
2015 7 62
2015 8 62
2015 9 60
2015 10 62
2015 11 60
2015 12 62
... ...
;

run;

data Want_3;
length ID Status $ 40;
input Id  Status $ Date_of_effect Date_Status Exposure_days_2015	Exposure_days_2016	Exposure_days_2017	Exposure_days_2018	Exposure_days_2019	Exposure_month_2015	Exposure_month_2016	Exposure_month_2017	Exposure_month_2018	Exposure_month_2019	Exposure_years_2015	Exposure_years_2016	Exposure_years_2017	Exposure_years_2018	Exposure_years_2019
 ;
informat Date_of_effect Date_Status date9.;
format Date_of_effect Date_Status date9.;
infile datalines missover;
datalines;
1 Ongoing 05jul2015 05jul2015 180 366 365 365 153 6 12 12 12 5 0.5000 1.0000 1.0000 1.0000 0.4167
1 Transformation 23Oct2014 05jul2015 185 0 0 0 0 6 0 0 0 0 0.5000 0.0000 0.0000 0.0000 0.0000
2 Death 07jan2017 02dec2018 0 0 359 335 0 0 0 12 11 0 0.0000 0.0000 1.0000 0.9167 0.0000
3 Finished 13feb2005 13feb2015 43 0 0 0 0 1 0 0 0 0 0.0833 0.0000 0.0000 0.0000 0.0000
4 NoEffect 06nov2016 06nov2016 0 0 0 0 0 0 0 0 0 0 0 0.0000 0.0000 0.0000 0.0000
5 Cancellation 08MAR2014 19dec2018 365 366 365 352 0 12 12 12 12 0 1.0000 1.0000 1.0000 1.0000 0.0000
6 Pending 21oct2018 21oct2018 0 0 0 72 153 0 0 0 2 5 0.0000 0.0000 0.0000 0.1667 0.4167
;

run;

andreas_lds · Posted 06-04-2019 04:21 AM

Sorry, but i won't open any excel files. Unfortunately i still don't understand the logic creating the line

2015 773 25 2.08

in your first want-dataset.

adil256 · Posted 06-04-2019 06:07 AM

No problem, I understand :).

I will try to be more clear.

The number 773 represents numbers of days where the portfolio has been active in 2015.

So in my sample,

the contract with ID 1 and status " Transformation" has been active during 185 days in 2015 ( from 01Jan2015 to 05Jul2015), it is the difference betwen Date_statut and the beginning of the 2015. The contract has been active in total from 23Oct2014 until 05Jul2015.

The contract ID 1 with status " ongoing" has been active during 180 days in 2015 from 05Jul2015 to 31Dec2015. It's the difference in days between the end of 2015 and the effective date. The contract with an "ongoing" Status are still active today, although the Date_status is equal to Date_of_effect.

The Contract ID 2 has never been active in 2015 so the exposure in 2015 is 0.However it has been active active during 359 days (31Dec2017-07Jan2017) and 335 days in 2018 (02Dec2018-01Jan2018).

The Contract ID 3 has been active in 2015 during 43 days from 01Jan2015 to 13Feb2015.

The Contract ID 4 has never been active because the Status is "noeffect" and the date_of_effect is equal to date_Status.

The contract ID 5 has been active in 2015 from 1JAN2015 to 31Dec2015 which represents 365 days.

The Contract 6 has not been active in 2015 because it stats in October 2018.

Then I sum all the contract's exposure in 2015 which are 180 + 185 +0+ 43 +0+ 365+0 =773.

I think that I need to compute the exposure by observation first and then maybe make a sum by year. The code below represents what I've done in excel to compute the exposure of an observation for each year. When the exposure is negative, I make it equal to 0.

if Status = "ongoing" or Status="Pending" then
Date_Status=today();

Expo_days_2015 = min(01/01/2016,Date_Status)-max(Date_of_effect,01/01/2015);

Expo_days_2016 = min(01/01/2017,Date_Status)-max(Date_of_effect,01/01/2016); 
Expo_days_2017 = min(01/01/2018,Date_Status)-max(Date_of_effect,01/01/2017); 
.
.
.

I would like to compute the same exposure in month and in year.

adil256 · Posted 06-04-2019 06:43 AM

data Want3 ;
set Have;

if Status = "Ongoing" or Status="Pending" then
Date_Status=today();


Expo_days_2015 = min(mdy(01,01,2016),Date_Status)-max(Date_of_effect,mdy(1,1,2015));
Expo_month_2015 = round((min(mdy(01,01,2016),Date_Status)-max(Date_of_effect,mdy(1,1,2015)))/(365.25/12),1);

if Expo_days_2015 <0 then Expo_days_2015=0;
if Expo_month_2015 <0 then Expo_month_2015=0;

Expo_days_2016 = min(mdy(01,01,2017),Date_Status)-max(Date_of_effect,mdy(1,1,2016));
Expo_month_2016 = round((min(mdy(01,01,2017),Date_Status)-max(Date_of_effect,mdy(1,1,2016)))/(365.25/12),1);

if Expo_days_2016 <0 then Expo_days_2016=0;
if Expo_month_2016 <0 then Expo_month_2016=0;

Expo_days_2017 = min(mdy(01,01,2018),Date_Status)-max(Date_of_effect,mdy(1,1,2017));
Expo_month_2017 = round((min(mdy(01,01,2018),Date_Status)-max(Date_of_effect,mdy(1,1,2017)))/(365.25/12),1);

if Expo_days_2017 <0 then Expo_days_2017=0;
if Expo_month_2017 <0 then Expo_month_2017=0;

Expo_days_2018 = min(mdy(01,01,2019),Date_Status)-max(Date_of_effect,mdy(1,1,2018));
Expo_month_2018 = round((min(mdy(01,01,2019),Date_Status)-max(Date_of_effect,mdy(1,1,2018)))/(365.25/12),1);

if Expo_days_2018 <0 then Expo_days_2018=0;
if Expo_month_2018 <0 then Expo_month_2018=0;


Expo_days_2019 = min(mdy(01,01,2020),Date_Status)-max(Date_of_effect,mdy(1,1,2019));
Expo_month_2019 = round((min(mdy(01,01,2019),Date_Status)-max(Date_of_effect,mdy(1,1,2018)))/(365.25/12),1);

if Expo_days_2019 <0 then Expo_days_2019=0;
if Expo_month_2019 <0 then Expo_month_2019=0;

run;

That's the dirty way to get the Table Want3. Then with that table, I would like to get Want1 where I need to sum Expo_days_2015 to have 773 days for year 2015.

adil256 · Posted 06-04-2019 10:40 AM

hello again @andreas_lds ;

I almost succeed what I wanted to produce but as you can see, it's very repetitive and not automatic.

I've tried to use " do loop" to reduce the amount of code but unsuccessfuly.

If you've any idea on how to achieve the same results in a better way, I'd happy to learn.

Thank you 🙂

data Have;
length ID Status $ 40;
input Id  Status $ Date_of_effect Date_Status ;

informat Date_of_effect Date_Status date9.;
format Date_of_effect Date_Status date9.;
infile datalines missover;
datalines;
1 Ongoing 05jul2015 05jul2015
1 Transformation 23Oct2014 05jul2015 
2 Death 07jan2017 02dec2018 
3 Finished 13feb2005 13feb2015
4 NoEffect 06nov2016 06nov2016
5 Cancellation 08MAR2014 19dec2018
6 Pending 21oct2018 21oct2018
;

run;
data Want1 ;
set Have;

if Status = "Ongoing" or Status="Pending" then
Date_Status=today();


Expo_days_2015 = min(mdy(01,01,2016),Date_Status)-max(Date_of_effect,mdy(1,1,2015));
Expo_month_2015 = round((min(mdy(01,01,2016),Date_Status)-max(Date_of_effect,mdy(1,1,2015)))/(365.25/12),1);

if Expo_days_2015 <0 then Expo_days_2015=0;
if Expo_month_2015 <0 then Expo_month_2015=0;

Expo_days_2016 = min(mdy(01,01,2017),Date_Status)-max(Date_of_effect,mdy(1,1,2016));
Expo_month_2016 = round((min(mdy(01,01,2017),Date_Status)-max(Date_of_effect,mdy(1,1,2016)))/(365.25/12),1);

if Expo_days_2016 <0 then Expo_days_2016=0;
if Expo_month_2016 <0 then Expo_month_2016=0;

Expo_days_2017 = min(mdy(01,01,2018),Date_Status)-max(Date_of_effect,mdy(1,1,2017));
Expo_month_2017 = round((min(mdy(01,01,2018),Date_Status)-max(Date_of_effect,mdy(1,1,2017)))/(365.25/12),1);

if Expo_days_2017 <0 then Expo_days_2017=0;
if Expo_month_2017 <0 then Expo_month_2017=0;

Expo_days_2018 = min(mdy(01,01,2019),Date_Status)-max(Date_of_effect,mdy(1,1,2018));
Expo_month_2018 = round((min(mdy(01,01,2019),Date_Status)-max(Date_of_effect,mdy(1,1,2018)))/(365.25/12),1);

if Expo_days_2018 <0 then Expo_days_2018=0;
if Expo_month_2018 <0 then Expo_month_2018=0;


Expo_days_2019 = min(mdy(01,01,2020),Date_Status)-max(Date_of_effect,mdy(1,1,2019));
Expo_month_2019 = round((min(mdy(01,01,2020),Date_Status)-max(Date_of_effect,mdy(1,1,2019)))/(365.25/12),1);

if Expo_days_2019 <0 then Expo_days_2019=0;
if Expo_month_2019 <0 then Expo_month_2019=0;

run;

proc sql noprint;
select distinct sum(Expo_days_2015) into: Expo_days_2015
from work.want1;
select distinct sum(Expo_days_2016) into: Expo_days_2016
                                                            
from work.want1;

select distinct sum(Expo_days_2017) into: Expo_days_2017
                                                            
from work.want1;

select distinct sum(Expo_days_2018) into: Expo_days_2018
                                                            
from work.want1;

select distinct sum(Expo_days_2019) into: Expo_days_2019
                                                            
from work.want1;

select distinct sum(Expo_month_2015) into: Expo_month_2015
from work.want1;
select distinct sum(Expo_month_2016) into: Expo_month_2016
                                                            
from work.want1;

select distinct sum(Expo_month_2017) into: Expo_month_2017
                                                            
from work.want1;

select distinct sum(Expo_month_2018) into: Expo_month_2018
                                                            
from work.want1;

select distinct sum(Expo_month_2019) into: Expo_month_2019
                                                            
from work.want1;

quit;
run;

%put Expo_days_2015;
%put Expo_days_2016;
%put Expo_days_2017;
%put Expo_days_2018;
%put Expo_days_2019;
%put Expo_month_2015;
%put Expo_month_2016;
%put Expo_month_2017;
%put Expo_month_2018;
%put Expo_month_2019;



data Want2 ( drop=i);
do i = 2015 to 2019;

year= i;

if i =2015 then do;
Expo_days=&Expo_days_2015;
Expo_month=&Expo_month_2015;
end;
else if i = 2016 then do ;
Expo_days=&Expo_days_2016;
Expo_month=&Expo_month_2016;
end;
else if i = 2017 then do ;
Expo_days=&Expo_days_2017;
Expo_month=&Expo_month_2017;
end;
else if i = 2018 then do ;
Expo_days=&Expo_days_2018;
Expo_month=&Expo_month_2018;
end;
else if i = 2019 then do ;
Expo_days=&Expo_days_2019;
Expo_month=&Expo_month_2019;
end;
output;
end;

run;


data Want3 ;
set have;

if Status = "Ongoing" or Status="Pending" then
Date_Status=today();


Expo_1_2015 = min(mdy(2,1,2015),Date_Status)-max(Date_of_effect,mdy(1,1,2015));
Expo_2_2015 = min(mdy(3,1,2015),Date_Status)-max(Date_of_effect,mdy(2,1,2015));
Expo_3_2015 = min(mdy(4,1,2015),Date_Status)-max(Date_of_effect,mdy(3,1,2015));
Expo_4_2015 = min(mdy(5,1,2015),Date_Status)-max(Date_of_effect,mdy(4,1,2015));
Expo_5_2015 = min(mdy(6,1,2015),Date_Status)-max(Date_of_effect,mdy(5,1,2015));
Expo_6_2015 = min(mdy(7,1,2015),Date_Status)-max(Date_of_effect,mdy(6,1,2015));
Expo_7_2015 = min(mdy(8,1,2015),Date_Status)-max(Date_of_effect,mdy(7,1,2015));
Expo_8_2015 = min(mdy(9,1,2015),Date_Status)-max(Date_of_effect,mdy(8,1,2015));
Expo_9_2015 = min(mdy(10,1,2015),Date_Status)-max(Date_of_effect,mdy(9,1,2015));
Expo_10_2015 = min(mdy(11,1,2015),Date_Status)-max(Date_of_effect,mdy(10,1,2015));
Expo_11_2015 = min(mdy(12,1,2015),Date_Status)-max(Date_of_effect,mdy(11,1,2015));
Expo_12_2015 = min(mdy(1,1,2016),Date_Status)-max(Date_of_effect,mdy(12,1,2015));

Expo_1_2016 = min(mdy(2,1,2016),Date_Status)-max(Date_of_effect,mdy(1,1,2016));
Expo_2_2016 = min(mdy(3,1,2016),Date_Status)-max(Date_of_effect,mdy(2,1,2016));
Expo_3_2016 = min(mdy(4,1,2016),Date_Status)-max(Date_of_effect,mdy(3,1,2016));
Expo_4_2016 = min(mdy(5,1,2016),Date_Status)-max(Date_of_effect,mdy(4,1,2016));
Expo_5_2016 = min(mdy(6,1,2016),Date_Status)-max(Date_of_effect,mdy(5,1,2016));
Expo_6_2016 = min(mdy(7,1,2016),Date_Status)-max(Date_of_effect,mdy(6,1,2016));
Expo_7_2016 = min(mdy(8,1,2016),Date_Status)-max(Date_of_effect,mdy(7,1,2016));
Expo_8_2016 = min(mdy(9,1,2016),Date_Status)-max(Date_of_effect,mdy(8,1,2016));
Expo_9_2016 = min(mdy(10,1,2016),Date_Status)-max(Date_of_effect,mdy(9,1,2016));
Expo_10_2016 = min(mdy(11,1,2016),Date_Status)-max(Date_of_effect,mdy(10,1,2016));
Expo_11_2016 = min(mdy(12,1,2016),Date_Status)-max(Date_of_effect,mdy(11,1,2016));
Expo_12_2016 = min(mdy(1,1,2017),Date_Status)-max(Date_of_effect,mdy(12,1,2016));



if Expo_1_2016  <0 then Expo_1_2016 =0;
if Expo_2_2016  <0 then Expo_2_2016 =0;
if Expo_3_2016  <0 then Expo_3_2016 =0;
if Expo_4_2016  <0 then Expo_4_2016 =0;
if Expo_5_2016  <0 then Expo_5_2016 =0;
if Expo_6_2016  <0 then Expo_6_2016 =0;
if Expo_7_2016  <0 then Expo_7_2016 =0;
if Expo_8_2016  <0 then Expo_8_2016 =0;
if Expo_9_2016  <0 then Expo_9_2016 =0;
if Expo_10_2016 <0 then Expo_10_2016=0;
if Expo_11_2016 <0 then Expo_11_2016=0;
if Expo_12_2016 <0 then Expo_12_2016=0;

if Expo_1_2015  <0 then Expo_1_2015 =0;
if Expo_2_2015  <0 then Expo_2_2015 =0;
if Expo_3_2015  <0 then Expo_3_2015 =0;
if Expo_4_2015  <0 then Expo_4_2015 =0;
if Expo_5_2015  <0 then Expo_5_2015 =0;
if Expo_6_2015  <0 then Expo_6_2015 =0;
if Expo_7_2015  <0 then Expo_7_2015 =0;
if Expo_8_2015  <0 then Expo_8_2015 =0;
if Expo_9_2015  <0 then Expo_9_2015 =0;
if Expo_10_2015 <0 then Expo_10_2015 =0;
if Expo_11_2015 <0 then Expo_11_2015 =0;
if Expo_12_2015 <0 then Expo_12_2015 =0;

/*
.
.
.
.
*/

run;

/*%macro loop_expo;

%do i=1 %to 12;
proc sql noprint;

select distinct sum(Expo_&i._2015) into: Expo_&i._2015
from work.want3;



quit;
run;
%end;
%mend;

%loop_expo;*/
proc sql noprint;

select distinct sum(Expo_1_2015) into: Expo_1_2015
from work.want3;
select distinct sum(Expo_2_2015) into: Expo_2_2015
from work.want3;
select distinct sum(Expo_3_2015) into: Expo_3_2015
from work.want3;
select distinct sum(Expo_4_2015) into: Expo_4_2015
from work.want3;
select distinct sum(Expo_5_2015) into: Expo_5_2015
from work.want3;
select distinct sum(Expo_6_2015) into: Expo_6_2015
from work.want3;
select distinct sum(Expo_7_2015) into: Expo_7_2015
from work.want3;
select distinct sum(Expo_8_2015) into: Expo_8_2015
from work.want3;
select distinct sum(Expo_9_2015) into: Expo_9_2015
from work.want3;
select distinct sum(Expo_10_2015) into: Expo_10_2015
from work.want3;
select distinct sum(Expo_11_2015) into: Expo_11_2015
from work.want3;
select distinct sum(Expo_12_2015) into: Expo_12_2015
from work.want3;

select distinct sum(Expo_1_2016) into: Expo_1_2016
from work.want3;
select distinct sum(Expo_2_2016) into: Expo_2_2016
from work.want3;
select distinct sum(Expo_3_2016) into: Expo_3_2016
from work.want3;
select distinct sum(Expo_4_2016) into: Expo_4_2016
from work.want3;
select distinct sum(Expo_5_2016) into: Expo_5_2016
from work.want3;
select distinct sum(Expo_6_2016) into: Expo_6_2016
from work.want3;
select distinct sum(Expo_7_2016) into: Expo_7_2016
from work.want3;
select distinct sum(Expo_8_2016) into: Expo_8_2016
from work.want3;
select distinct sum(Expo_9_2016) into: Expo_9_2016
from work.want3;
select distinct sum(Expo_10_2016) into: Expo_10_2016
from work.want3;
select distinct sum(Expo_11_2016) into: Expo_11_2016
from work.want3;
select distinct sum(Expo_12_2016) into: Expo_12_2016
from work.want3;

quit;
run;
%put Expo_1_2015;
%put Expo_2_2015;
%put Expo_3_2015;
%put Expo_4_2015;
%put Expo_5_2015;
%put Expo_6_2015;
%put Expo_7_2015;
%put Expo_8_2015;
%put Expo_9_2015;
%put Expo_10_2015;
%put Expo_11_2015;
%put Expo_12_2015;

%put Expo_1_2016;
%put Expo_2_2016;
%put Expo_3_2016;
%put Expo_4_2016;
%put Expo_5_2016;
%put Expo_6_2016;
%put Expo_7_2016;
%put Expo_8_2016;
%put Expo_9_2016;
%put Expo_10_2016;
%put Expo_11_2016;
%put Expo_12_2016;


data Want4 ( drop=i j);
do i = 2015 to 2016;
do j= 1 to 12;

year= i;
month= j;

if i =2015 then do;
if j= 1 then 
Expo_days = &Expo_1_2015;
else if j=2 then
Expo_days = &Expo_2_2015;
else if j=3 then
Expo_days = &Expo_3_2015;
else if j=4 then
Expo_days=&Expo_4_2015;
else if j=5 then
Expo_days=&Expo_5_2015;
else if j=6 then
Expo_days=&Expo_6_2015;
else if j=7 then
Expo_days=&Expo_7_2015;
else if j=8 then
Expo_days=&Expo_8_2015;
else if j=9 then
Expo_days=&Expo_9_2015;
else if j=10 then
Expo_days=&Expo_10_2015;
else if j=11 then
Expo_days=&Expo_11_2015;
else if j=12 then
Expo_days=&Expo_12_2015;
end;

else if i = 2016 then do ;
if j= 1 then 
Expo_days=&Expo_1_2016;
else if j=2 then
Expo_days=&Expo_2_2016;
else if j=3 then
Expo_days=&Expo_3_2016;
else if j=4 then
Expo_days=&Expo_4_2016;
else if j=5 then
Expo_days=&Expo_5_2016;
else if j=6 then
Expo_days=&Expo_6_2016;
else if j=7 then
Expo_days=&Expo_7_2016;
else if j=8 then
Expo_days=&Expo_8_2016;
else if j=9 then
Expo_days=&Expo_9_2016;
else if j=10 then
Expo_days=&Expo_10_2016;
else if j=11 then
Expo_days=&Expo_11_2016;
else if j=12 then
Expo_days=&Expo_12_2016;
end;

output;
end;
end;

run;

andreas_lds · Posted 06-05-2019 07:59 AM

Unfortunately i am to busy to put the necessary time into finding a solution.

The following code creates the first table you want:

data work.Dummy;
   set work.have;

   length 
      startDate endDate done 8
      Year ExpoDays ExpoMonths 8
   ;
   format startDate endDate date9.;

   if Status = "Ongoing" or Status="Pending" then do;
      Date_Status = today();
   end;

   if date_of_effect < '1Jan2015'd then do;
      date_of_effect = '1Jan2015'd;
   end;

   startDate = date_of_effect;
   endDate = ifn(year(startDate) = year(Date_Status), date_status, intnx('year', startDate, 0, 'end'));
   done = 0;

   do until (done);
      done = (endDate = date_status);
      Year = year(startDate);
      ExpoDays = endDate - startDate;
      ExpoMonths = round(ExpoDays / (365.25 / 12), 1) + done;
      output;
      
      startDate = endDate + 1;
      endDate = ifn(year(startDate) = year(date_status), date_status, intnx('year', startDate, 0, 'end'));
   end;

   keep Year ExpoDays ExpoMonths;
run;

proc summary data=work.Dummy nway;
   class Year;
   var ExpoDays ExpoMonths;
   output out=work.Want1(drop=_freq_ _type_) sum=;
run;

adil256 · Posted 06-06-2019 05:55 AM

Hi @andreas_lds ,

It's very different way of yours 😄 but here what I've tried so far with still some bugs. Apparently creating macro variable inside a macro statement doesn't work outside. ( see my post : https://communities.sas.com/t5/SAS-Programming/Do-Loop-with-Proc-SQL-inside-a-macro/m-p/563657#M1580...)

Thank you for helping me, I will try to understand your code and adapt it to my needs.

%let a=2015;
%let b=2019;


data Want_0 (drop=i) ;
set have;

array Expo_d_ (&a.:&b.) Expo_d_&a.-Expo_d_&b.;
array Expo_m_ (&a.:&b.) Expo_m_&a.-Expo_m_&b.;
if Status = "Ongoing" or Status="Pending" then
Date_Status=today();

do i=&a. to &b.;

Expo_d_(i) = max(0,min(mdy(01,01,i+1),Date_Status)-max(Date_of_effect,mdy(1,1,i)));
Expo_m_(i) = max(0,round((min(mdy(01,01,i+1),Date_Status)-max(Date_of_effect,mdy(1,1,i)))/(365.25/12),1));
end;
run;



%macro Do_stock;

proc sql noprint;

%do i=&a. %to &b.;
select sum(Expo_d_&i.) into: Expo_d_&i.
from work.Want_0;

select sum(Expo_m_&i.) into: Expo_m_&i.
from work.Want_0;
%end;                                                       

quit;
run;

%do i=&a. %to &b.;

%put Expo_d_&i.;
%put Expo_m_&i.;

%end;
%mend;
%Do_stock;

data Want_1 ( drop=i);
do i = &a. to &b.;

year= i;

Expo_day=&Expo_d_&i.;
Expo_month=&Expo_m_&i.;
end;

run;

The 2025 SAS Hackathon has begun!

SAS Training: Just a Click Away