About adil256

adil256 · ‎06-06-2019

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#M158044) 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;

adil256 · ‎06-05-2019

Hi everyone, I tried to run a do loop for a Proc SQL in a macro but my procedure fails. When I run separately the %Do_Stock macro, there is no warning from SAS. However, the macro-variable Expo_d_&i. and Expo_m_&i. doesn't seem to work. If you have any idea on how to fix the problem I would very happy. Thank you to the community. 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; %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;

adil256 · ‎06-04-2019

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;

adil256 · ‎06-04-2019

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 · ‎06-04-2019

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 · ‎06-03-2019

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;

adil256 · ‎06-03-2019

%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.

adil256 · ‎06-03-2019

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 · ‎07-14-2017

Hello again; I thought about this way to get what I want. Data one; input V U ; informat V U date9. duration; format V U date9.; infile datalines missover; datalines; 15feb2012 1jan2012 15feb2012 14jan2012 15feb2012 15jan2012 15apr2012 15jan2012 ; run; data one; set one; month_diff=intck('month',U,V,'C'); b=intnx('month',U,month_diff,'sameday'); days_diff=intck('day',U,b,'C'); if c>0 then month_duration =month_diff+1; else month_duration=month_diff; run;

adil256 · ‎07-13-2017

may excuse me if I expressed myself poorly. The duration variable stands for the number of months elapsed between two dates. the function intck gives me the number of months really elapsed betwen the dates. for example, If 3 months and 17 days have been elapsed the intck function will give 3 months elapsed. What I'm looking for it's to considerer these 3 months and 17 days as 4 months elapsed. @Astounding thank you but ceil function doesn't work perfectly as well :s; the number of month between 15jan2012 and 15feb2012. I will think about your way of doing it. Thank u very much. @art297 ,@RW9 and @Reeza.

adil256 · ‎07-13-2017

Hi everone, I got an issue when it comes to computed date interval in month. I would like to round up the month when 1 day or more have elapsed during the month (ex: 3months and 1 day should be considered as 4months; 2 months and 5 days = 3 months; 11 months and 0 days should be 11 months). I'm using this function but it only round up when the half of the month elapsed. Data one; input V U; informat V U date9.; format V U date9.; infile datalines missover; datalines; 15feb2012 1jan2012 15feb2012 14jan2012 15feb2012 15jan2012 15apr2012 15jan2012 ; run; data one; set one; duration = max(int((V-U)/(365.25/12)+0.5),1); run; Data want; input V U duration; informat V U date9. duration; format V U date9.; infile datalines missover; datalines; 15feb2012 1jan2012 2 15feb2012 14jan2012 2 15feb2012 15jan2012 1 15apr2012 15jan2012 3 ; run; Any help would be grateful. Thank you.

adil256 · ‎07-11-2017

Thank you very much. it works perfectly. 😉 I found my mistake in the previous code but I thinking I'm going to choose your method. corrected code: options macrogen mprint mlogic; %macro rename(lib,dsn,dsn1); options pageno=1 nodate; proc contents data=&lib..&dsn; title "Before Renaming All Variables"; run; proc sql noprint; select nvar into :num_vars from dictionary.tables where libname="&LIB" and memname="&DSN"; select distinct(name) into :var1- :var%TRIM(%LEFT(&num_vars)) from dictionary.columns where libname="&LIB" and memname="&DSN"; quit; run; proc sql noprint; select nvar into :num_vars1 from dictionary.tables where libname="&LIB" and memname="&DSN1"; select distinct(name) into :vari1- :vari%TRIM(%LEFT(&num_vars)) from dictionary.columns where libname="&LIB" and memname="&DSN1"; quit; run; proc datasets library=&LIB; modify &DSN1; rename %do i=1 %to &num_vars; &&vari&i=&&var&i. %end; ; quit; run; options pageno=1 nodate; proc contents data=&lib..&dsn; title "After Renaming All Variables"; run; %mend rename; %rename(WORK,ONE,TWO);

adil256 · ‎07-11-2017

Hi everyone, I would like to rename all variables of dataset thanks to another dataset with the same structure. I used the macro from Prasad Ravi (: http://www2.sas.com/proceedings/sugi28/118-28.pdf) with some modification but it doesn't work. I took a simple exemple to illustrate what i want to achieve. Data one; U=1; V=2; X=3; Y=4; Z=5; Run; Data two; a=25; b=265; c=346; d=454; e=54; Run; Data want; U=25; V=265; X=346; Y=454; Z=54; Run; options macrogen mprint mlogic; %macro rename(lib,dsn,dsn1); options pageno=1 nodate; proc contents data=&lib..&dsn; title "Before Renaming All Variables"; run; proc sql noprint; select nvar into :num_vars from dictionary.tables where libname="&LIB" and memname="&DSN"; proc sql noprint; select nvar into :num_vars1 from dictionary.tables where libname="&LIB" and memname="&DSN1"; select distinct(name) into :var1- :var%TRIM(%LEFT(&num_vars)) from dictionary.columns where libname="&LIB" and memname="&DSN"; quit; run; select distinct(name) into :varia1- :varia%TRIM(%LEFT(&num_vars1)) from dictionary.columns where libname="&LIB" and memname="&DSN1"; quit; run; proc datasets library=&LIB; modify &dsn1; rename %do i=1 %to &num_vars1; &&varia&i=&&var&i. %end; ; quit; run; options pageno=1 nodate; proc contents data=&lib..&dsn; title "After Renaming All Variables"; run; %mend rename; %rename(WORK,ONE,TWO); Thank you in advance

adil256 · ‎07-11-2017

Sorry for late response. It's exactly what i wanted to do. It does the job perfectly. I thought it was possible to exclude a vairable like this : var1-var10 except var3. @RW9 I need to create the table before to retrieve the statistic from fastclus procedure. Then based on the statistic parameters, I only choose one. Thank you guys for your quick answers. Have a good day.

adil256 · ‎07-07-2017

hi, I created tables in a do loop (e.g. Table_1 to Table_30 ) and I would like to keep only one, let's say Table_13. How can I delete the other tables in a simple way? thank you 🙂

Online Status	Offline
Date Last Visited	‎09-22-2021 02:44 PM

Re: By Statement with a variable that not exists in the input dataset

Re: By Statement with a variable that not exists in the input dataset

Re: By Statement with a variable that not exists in the input dataset

Re: By Statement with a variable that not exists in the input dataset

Re: By Statement with a variable that not exists in the input dataset

By Statement with a variable that not exists in the input dataset

Re: FCMP: Multiple arrays simplification

Re: FCMP: Multiple arrays simplification

Re: FCMP: Multiple arrays simplification

FCMP: Multiple arrays simplification

Re: FCMP: Multiple arrays simplification

Re: Function : FCMP Value Assignment

Re: Function : FCMP Value Assignment

Re: Function : FCMP Value Assignment

Re: Function : FCMP Value Assignment

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

Do Loop with Proc SQL inside a macro

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

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

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

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

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

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

Re: How to round Interval date

Re: How to round Interval date

How to round Interval date

Re: Rename all variables.

Rename all variables.

Re: Delete iterated Table

Delete iterated Table