About brophymj

brophymj · ‎08-18-2017

I have a dataset like the one below where one of the fields is latest (which indicates the latest year). I want to calculate the reported losses (rl) for the 2 and 5 year before the latest year. So, for the first row, the 2 year figures would be rl_2015 + rl_2016 and the 5 year would be rl_2012 + rl_2013 + rl_2014 + rl_2015 + rl_2016. If the latest year was the same for every row this would be very straightforward but it varies so I need to put in some conditional sum based on the latest year. Id there a way to sum based on the value of "Latest" in a row.i.e. %let x = latest; rl_2yr = rl_&latest + rl_(&latest-1). within a datastep, class policyno latest gwp_2011 rl_2011 gwp_2012 rl_2012 gwp_2013 rl_2013 gwp_2014 rl_2014 gwp_2015 rl_2015 gwp_2016 rl_2016 gwp_2017 rl_2017 ARA 10 2016 . . 12,736.27 1663.46 15,397.14 48108.36 19,796.19 5306.15 27,873.91 120494.3 . . . . ARA 144 2017 . . . . 2,279.05 . 2,255.24 . 2,255.24 . 2,542.86 . . . ARA 235 2016 . . 1,825.71 . 1,876.19 . 1,912.38 . 2,047.62 . . . . . ARA 253 2016 . . 3,066.67 . 2,792.70 8700 3,340.00 . 3,488.57 . 4,049.52 . . . ARA 282 2016 . . 685.71 380 685.71 20168.29 1,276.19 . 1,921.90 . 1,633.33 . . . ARA 285 2016 . . 3,059.60 . 2,509.98 . 3,683.33 . 5,321.53 . . . . . ARA 335 2017 . . . . 1,780.95 . 1,780.95 . 1,780.95 . 1,980.00 . . . ARA 388 2017 . . . . 2,100.00 . 2,907.67 2662.61 6,671.50 . 10,685.71 35900 . . ARA 39 2016 . . 2,420.95 . 3,025.71 373 3,528.10 . 3,130.95 . . . . .

brophymj · ‎06-14-2017

I have a dataset with 100000 rows and each row contains an extremely long string with lots of data. I'm want to create a new field with the "total payout value". This is the first number after the string "total_payout_value". The problem is the length of the string between the "total_payout_value" and the number can vary and also the number length can vary. Here is a sample from my dataset: "total_payout_value" : { "asset" : "SBD", "amount" : 37.13 }, "pending_payout_value" : { "asset" : "total_payout_value" : {"amount" : 37.13, "asset" : "SBD"}, "pending_payout_value" : { "asset" : "total_payout_value" : {"amount" : 0, "asset" : "SBD"}, "pending_payout_value" : { "asset" : The actual sting is 1000s of characters long but each string will contain the string "total_payout_value" only once and the first number after this string is what I'm trying to extract. You will also see that sometimes the order of words between "total_payout_value" and the number can change so unfortuntately this length is not fixed. I tried this code but I don't know how to allow for the varying lengths. Really, what I need is is something that find the first number after the string "total_payout_value" data test1(keep = start end payout ); set data; format payout $8.; start = find(dataset,' "total_payout_value" : { "asset" : "SBD", "amount" : ')+54; end = find(dataset,', "asset" : "SBD" }, "author"'); payout = substr(dataset,start,end-start)); run;

brophymj · ‎04-19-2017

I've got a dataset with 100s of price fields. I want to loop through all price fields and identify the first where the price is less than 0.9 and save the number after "price_" as period. In the row below, the period would be 0.88. Price_1 Price_2 Price_3 Price_4 Price_5 Price_6 Price_7 Price_8 Price_9 Price_10 Price_11 0.95 0.96 0.94 0.93 0.92 0.91 0.88 0.91 0.89 0.88 0.93

brophymj · ‎04-13-2017

Hi Art Thanks for your reply. I've been through the code many times and it just doesn't make send why it's not working. I'll post an example. There's a useful macro on the forum where you can create the sas code for your dataset. Do you have the code to create it? Thanks

brophymj · ‎04-13-2017

I'm using code the uses the lag function but for some reason the lag1 function is calling values from two periods before. I've attached screenshot where you can see the issue. I'm not sure what's causing this but my whole code relies a lot on lags so hopefull this is something that can be resolved. data check1; retain vol_x vol_x1 vol_x2; set check; vol_x1=lag1(vol_x); vol_x2=lag2(vol_x); if Price_USD gt 0 then vol_24hr_units = Vol_24hr/Price_USD; vol_1hr_units = vol_24hr_units/24 + (vol_24hr_units-lag1(vol_24hr_units))/2; vol_x = (vol_1hr_units + lag1(vol_1hr_units) + lag2(vol_1hr_units) + lag3(vol_1hr_units) + lag4(vol_1hr_units))/5; price_x = (Price_USD + lag1(Price_USD) + lag2(Price_USD) + lag3(Price_USD) + lag4(Price_USD))/5; run;

brophymj · ‎04-13-2017

Thanks Kurt. That works now. If I want to retain the price change data from the last period with a price change, how would I go about doing this. Can i use the lag function with period to get this? So I know the last negative change was 3 periods ago, what was that change?

brophymj · ‎04-13-2017

Thanks but it doesn't produce the dataset that's Im looking for. When I run this code: data have; input signal $1. date :ddmmyy10. change :percent5.; format signal $1. date ddmmyy10. change percent5. ; cards; B 13/04/2017 1% # 12/04/2017 2% # 11/04/2017 1% # 10/04/2017 -1 # 09/04/2017 -1% # 08/04/2017 1% # 07/04/2017 2% # 06/04/2017 3% B 05/04/2017 1% # 04/04/2017 2% # 03/04/2017 -1% # 02/04/2017 -1% # 01/04/2017 2% # 31/03/2017 1% # 30/03/2017 2% # 29/03/2017 3% # 28/03/2017 4% B 27/03/2017 1% # 26/03/2017 -1% # 25/03/2017 -1% # 24/03/2017 -2% # 23/03/2017 1% # 22/03/2017 1% # 21/03/2017 -1% # 20/03/2017 -1% ; run; data want; set have; retain period; if change < 0 then period = 0; else period + 1; run; data want; set want; retain _period; if change < 0 then _period = 0; else _period + 1; if signal = 'B' then period = _period; drop _period; run; I don't get the data set below data have; input signal $1. date :ddmmyy10. change ercent5. period change_last_neg ercent5.; format signal $1. date ddmmyy10. change percent5. period change_last_neg percent5.; cards; B 13/04/2017 1% 3 -1% # 12/04/2017 2% . # 11/04/2017 1% . # 10/04/2017 -1% . # 09/04/2017 -1% . # 08/04/2017 1% . # 07/04/2017 2% . # 06/04/2017 3% . B 05/04/2017 1% 2 -1% # 04/04/2017 2% . # 03/04/2017 -1% . # 02/04/2017 -1% . # 01/04/2017 2% . # 31/03/2017 1% . # 30/03/2017 2% . # 29/03/2017 3% . # 28/03/2017 4% . B 27/03/2017 1% 1 -1% # 26/03/2017 -1% . # 25/03/2017 -1% . # 24/03/2017 -2% . # 23/03/2017 1% . # 22/03/2017 1% . # 21/03/2017 -1% . # 20/03/2017 -1% . ; run; Also do you know how to pull the price change from the last period with a negative price change i.e. data have; input signal $1. date :ddmmyy10. change ercent5. period change_last_neg ercent5.; format signal $1. date ddmmyy10. change percent5. period change_last_neg percent5.; cards; B 13/04/2017 1% 3 -1% # 12/04/2017 2% . # 11/04/2017 1% . # 10/04/2017 -1% . # 09/04/2017 -1% . # 08/04/2017 1% . # 07/04/2017 2% . # 06/04/2017 3% . B 05/04/2017 1% 2 -1% # 04/04/2017 2% . # 03/04/2017 -1% . # 02/04/2017 -1% . # 01/04/2017 2% . # 31/03/2017 1% . # 30/03/2017 2% . # 29/03/2017 3% . # 28/03/2017 4% . B 27/03/2017 1% 1 -1% # 26/03/2017 -1% . # 25/03/2017 -1% . # 24/03/2017 -2% . # 23/03/2017 1% . # 22/03/2017 1% . # 21/03/2017 -1% . # 20/03/2017 -1% . ; run;

brophymj · ‎04-13-2017

Thanks KurtBrenser If my dataset had another column called signal and I want to calculate the number of days since the last price change from the signal how would I alter the code below to accomodate that. Here is the revised dataset: When signal is B, calculate the number of periods since the last price decrease. data have; input signal $1. date :ddmmyy10. change :percent5. period; format signal $1. date ddmmyy10. change percent5. period; cards; B 13/04/2017 1% 3 # 12/04/2017 2% . # 11/04/2017 1% . # 10/04/2017 -1% . # 09/04/2017 -1% . # 08/04/2017 1% . # 07/04/2017 2% . # 06/04/2017 3% . B 05/04/2017 1% 2 # 04/04/2017 2% . # 03/04/2017 -1% . # 02/04/2017 -1% . # 01/04/2017 2% . # 31/03/2017 1% . # 30/03/2017 2% . # 29/03/2017 3% . # 28/03/2017 4% . B 27/03/2017 1% 1 # 26/03/2017 -1% . # 25/03/2017 -1% . # 24/03/2017 -2% . # 23/03/2017 1% . # 22/03/2017 1% . # 21/03/2017 -1% . # 20/03/2017 -1% . ; run; I then want to soemone pull the price information from the last period where there was a price decrease so my final dataset would look like. data have; input signal $1. date :ddmmyy10. change :percent5. period change_last_neg :percent5.; format signal $1. date ddmmyy10. change percent5. period change_last_neg percent5.; cards; B 13/04/2017 1% 3 -1% # 12/04/2017 2% . # 11/04/2017 1% . # 10/04/2017 -1% . # 09/04/2017 -1% . # 08/04/2017 1% . # 07/04/2017 2% . # 06/04/2017 3% . B 05/04/2017 1% 2 -1% # 04/04/2017 2% . # 03/04/2017 -1% . # 02/04/2017 -1% . # 01/04/2017 2% . # 31/03/2017 1% . # 30/03/2017 2% . # 29/03/2017 3% . # 28/03/2017 4% . B 27/03/2017 1% 1 -1% # 26/03/2017 -1% . # 25/03/2017 -1% . # 24/03/2017 -2% . # 23/03/2017 1% . # 22/03/2017 1% . # 21/03/2017 -1% . # 20/03/2017 -1% . ; run;

brophymj · ‎04-13-2017

Hi RW9 I'm not trying to make things complicated. I'm just doing the best I can. I tried the code below data have; input date price_change; datalines; 3 1 2 1 1 1 0 -1 run; proc sort data=have; by date; run; data want; set have; retain flg; if _n_=1 then flg=0; if flg=1 then do; output; flg=2; end; if price_change < 0 then flg=1; run; ... but it's not working. Heres a larger dataset that might make it a bit clearer (i'm just using number for the date for simplicity): data have; input date price_change; datalines; 20 1 19 1 18 1 17 -1 16 1 15 1 14 -1 13 -1 12 1 11 1 10 1 9 1 8 1 7 1 6 1 5 1 4 -1 3 -1 2 1 1 1; run; I want the final dataset to look like: Note: period field is the number of days since the last negative price change. I will also have a field for positive price change data have; input date price_change period; datalines; 20 1 3 19 1 2 18 1 1 17 -1 0 16 1 2 15 1 1 14 -1 0 13 -1 0 12 1 8 11 1 7 10 1 6 9 1 5 8 1 4 7 1 3 6 1 2 5 1 1 4 -1 0 3 -1 0 ; run;

brophymj · ‎04-13-2017

I'm trying to determine how many periods before the current period before the price change was negative. I'm tried this code but it doesn't work: %macro temp; period = 0; %let i = 1; %do %until(lag&i.(price_chg_1) >0); period = period + &i; &i = &i+1; %end; %mend; %temp; Here is the error messahe I'm getting; Required operator not found in expression: lag&i.(vol_chg_1) >0 ERROR: The condition in the %DO %UNTIL loop, lag&i.(vol_chg_1) >0, yielded an invalid or missing value, . The macro will stop executing. ERROR: The macro TEMP will stop executing. ERROR 180-322: Statement is not valid or it is used out of proper I want to produce the Period field below Date Price change Period 13/04/2017 1% 3 12/04/2017 2% 2 11/04/2017 1% 1 10/04/2017 -1% order.

brophymj · ‎04-12-2017

HI RW9 That won't work for my dataset. I want to keep all the fields between the min and max. The min and max will be diffeent for each row so i need to create a different dataset for each row and then deploy the code to keep the fields specified by the min and max fields. I tried this but it didn't work. If i define the min and max, then I have just two fields that are kept... I need all the fields between the max and min (and this will be different for each row in the original dataset) %macro temp; %do j = 1 %to &nrows; data temp&j (keep=diff coin--maxdate_no vname(vals{mindate_no})--vname(vals{maxdate_no})); set test1; if _n_ = &j; array vals{*} price_:; run; %end; %mend; %temp;

brophymj · ‎04-12-2017

HI Reeza Yes, in the example above I'm trying to keep price_2-price_9. The max and min will be different for each row I want to create a new dataset for each row, keep the rows between the price_min and price_max defined by the min and max field

brophymj · ‎04-12-2017

The dataset below is an example of a dataset I'm using only the real dataset has 100s of fields and about 700 rows with different max/min values. How do i drop the field names using the max/min values i.e. data help(keep = Price_&min--Price_max); set want; run; Min Max Price_1 Price_2 Price_3 Price_4 Price_5 Price_6 Price_7 Price_8 Price_9 2 9 1.01 1.02 1.838399 2.609743 2.932246 3.15643 3.421783 4.083877 4.246248 My full code is below but it's not working. I think a solution to the simple example above would resolve the issue. Thanks data _NULL_; if 0 then set test1 nobs=n; call symputx('nrows',n); stop; run; proc sort data = test1 ; by descending diff; run; %put &nrows; %put &m_diff; data test2; input coin $50.; datalines; run; %macro temp; %do j = 1 %to &nrows; data temp&j(keep = diff coin--maxdate_no Price_USD_&min--Price_USD_&max); set test1; if _n_ = &j; call symputx('min',mindate_no); call symputx('max',maxdate_no); run; %end; %mend;

brophymj · ‎04-10-2017

Thanks astounding. I think that could work but is there a way of referencing the field name of variables in the array you want to exclude I.e. If number corresponding to field name is less than start than skip ... that kind of thing

brophymj · ‎04-10-2017

In the table below I want to define an array basd on the start and end value (first two columns). The array for the first row is start 1 and end 5 so the array will go from 101% to 116% (dimension 5); the array for the second row will go from 102% to 108% (dimension 2) and so on. can you create an array using the start and end variable names i.e. array temp{*} price_start --price_end start end price_1 price_2 price_3 price_4 price_5 price_6 price_7 price_8 price_9 price_10 price_11 12 13 14 15 1 5 101% 102% 107% 108% 116% 120% 128% 130% 139% 140% 146% 152% 157% 164% 167% 2 3 101% 102% 108% 112% 114% 116% 122% 129% 133% 141% 146% 149% 158% 165% 172% 5 10 101% 102% 110% 114% 115% 122% 129% 132% 136% 144% 153% 161% 166% 168% 175% 12 14 101% 102% 108% 117% 127% 135% 144% 150% 155% 155% 157% 160% 162% 166% 170% 3 7 101% 102% 107% 114% 118% 126% 130% 132% 136% 138% 138% 147% 152% 158% 162% 5 8 101% 102% 111% 111% 120% 127% 129% 131% 137% 140% 149% 155% 162% 164% 173% 6 8 101% 102% 110% 119% 126% 132% 138% 140% 147% 150% 151% 152% 159% 160% 164%

Online Status	Offline
Date Last Visited	‎01-22-2020 12:34 PM

Re: Generating numbers randomly from a table

Re: Generating numbers randomly from a table

Re: Generating numbers randomly from a table

Generating numbers randomly from a table

Installing SAS on HP laptop

Re: Simulating claims data using Poisson and Lognormal distributions

Re: Simulating claims data using Poisson and Lognormal distributions

Re: Simulating claims data using Poisson and Lognormal distributions

Re: Simulating claims data using Poisson and Lognormal distributions

Simulating claims data using Poisson and Lognormal distributions

Re: Simulating claims data using Poisson and Lognormal distributions

Re: Simulating claims data using Poisson and Lognormal distributions

Re: Simulating claims data using Poisson and Lognormal distributions

Re: converting matrix to two columns

Re: converting matrix to two columns

Nothing happening when running code

Name dataset as today's date

GLIM (Generalised Linear Iterative Model) in SAS

Re: Macros with conditions

Conditionally summing over fields in a dataset

Pulling number from complex string and creating new numeric field cont...

Do loop with conditions

Re: Lag1 pulling value from 2 periods before, not 1

Lag1 pulling value from 2 periods before, not 1

Re: Do until lag&i(var) gt 0

Re: Do until lag&i(var) gt 0

Re: Do until lag&i(var) gt 0

Re: Do until lag&i(var) gt 0

Do until lag&i(var) gt 0

Re: Keep field names dynamically

Re: Keep field names dynamically

Keep field names dynamically

Re: defining arrays using values from other fields

defining arrays using values from other fields