About RickyS

RickyS · ‎03-15-2019

Thank you for the quick turnaround, this works as intended but given how I need to use the data, I really need to retain the values and assumed status for each identified missing date that is created as I will be doing multiple date range analysis. In data set 03 Company02 MidSizeMarket 01 Active 04/25/2005 03 Company02 MidSizeMarket 08 CYRisk 06/25/2009 03 Company02 MidSizeMarket 01 Active 10/05/2009 03 Company02 MidSizeMarket 08 CYRisk 08/09/2013 Required output -keeps the original value and produces the missing data, just listing a few records that would be created between observations for Company02 03 Company02 MidSizeMarket 01 Active 04/25/2005 - original 03 Company02 MidSizeMarket 01 Active 05/01/2005 - created 03 Company02 MidSizeMarket 01 Active 06/01/2005 - created 03 Company02 MidSizeMarket 01 Active 07/01/2005 - created until we reach month b4 next status date 05/01/2009 - created 03 Company02 MidSizeMarket 08 CYRisk 06/25/2009 - original 03 Company02 MidSizeMarket 08 CYRisk 07/01/2009 - created 03 Company02 MidSizeMarket 08 CYRisk 08/01/2009 - created 03 Company02 MidSizeMarket 08 CYRisk 09/01/2009 - created 03 Company02 MidSizeMarket 01 Active 10/05/2009 - original 03 Company02 MidSizeMarket 01 Active 11/01/2009 - created 03 Company02 MidSizeMarket 01 Active 12/01/2009 - created 03 Company02 MidSizeMarket 01 Active 12/01/2009 - created 03 Company02 MidSizeMarket 01 Active 01/01/2010 - created until we reach month b4 next status date 07/01/2013 - created 03 Company02 MidSizeMarket 08 CYRisk 08/09/2013 - original 03 Company02 MidSizeMarket 08 CYRisk 09/09/2013 - created ---------------------until we reach most recent mm/01/yyyy I'm standardizing the output to day 01 for new date, hope this makes sense

RickyS · ‎03-14-2019

GA everyone, I have an existing customer status data set that is only updated when a status change change occurs. I want to do status counts by month, year and segment. So the challenge is creating a temp table for missing values for the month date periods that are not in the data table in order to enable a count of status by month and year. Important assumption: - Customer_Status is the same when it is not reported I should be able to count status for the Status_Date that are missing for Company02 Dates Missing Assumed Status 05/01/2005 - 05/31/2009 Active 07/01/2009 - 09/31/2009 CYRisk 11/01/2009 - 07/31/2013 Active 09/01/2013 - Today() CYRisk Sample Data data sample_data; input @1 Account_id 2. @3 Account_name $10. @13Segment $14. @28 Customer_Status 2. @30Customer_Status_Name $7. +1 Status_Date mmddyy10.; format Status_Date mmddyy10.; cards; 02 Company01 HighNewtWorth 04 CPLost 12/05/2006 03 Company02 MidSizeMarket 01 Active 04/25/2005 03 Company02 MidSizeMarket 08 CYRisk 06/25/2009 03 Company02 MidSizeMarket 01 Active 10/05/2009 03 Company02 MidSizeMarket 08 CYRisk 08/09/2013 04 Company03 HighNewtWorth 01 Active 01/26/2005 04 Company03 HighNewtWorth 04 CPLost 12/02/2016 05 Company04 HighNewtWorth 01 Active 09/13/2004 06 Company05 HighNewtWorth 01 Active 10/01/2004 06 Company05 HighNewtWorth 02 Inactv 08/18/2011 07 Company06 HighNewtWorth 01 Active 09/06/2006 08 Company07 HighNewtWorth 01 Active 08/01/2005 09 Company08 HighNewtWorth 01 Active 11/01/2005 09 Company08 HighNewtWorth 02 Inactv 11/23/2010 09 Company08 HighNewtWorth 06 Termin 05/07/2011 10 Company09 HighNewtWorth 01 Active 07/31/2007 10 Company09 HighNewtWorth 11 Infreq 10/27/2011 10 Company09 HighNewtWorth 01 Active 11/02/2012 10 Company09 HighNewtWorth 11 Infreq 11/14/2018 11 Company10 HighNewtWorth 01 Active 01/04/2005 11 Company10 HighNewtWorth 04 CPLost 06/05/2015 12 Company11 HighNewtWorth 01 Active 10/26/2004 13 Company12 HighNewtWorth 01 Active 01/14/2005 13 Company12 HighNewtWorth 02 Inactv 07/21/2011 14 Company13 HighNewtWorth 01 Active 01/04/2005 14 Company13 HighNewtWorth 02 Inactv 08/02/2012 15 Company14 HighNewtWorth 01 Active 09/01/2005 15 Company14 HighNewtWorth 02 Inactv 09/19/2008 16 Company15 HighNewtWorth 01 Active 09/28/2006 17 Company15 HighNewtWorth 06 Termin 05/19/2005 18 Company16 HighNewtWorth 01 Active 02/11/2005 19 Company17 HighNewtWorth 01 Active 10/26/2004 20 Company18 HighNewtWorth 01 Active 08/24/2005 ; run; quit; Thanks

RickyS · ‎02-04-2019

Novinosrin, Your solution works as well and I appreciate that you took some time over the weekend to look at it also nice that you included the defined period as part of the output.

RickyS · ‎02-04-2019

Thanks for the solution Erik. To answer the white elephant in the room, yes I did want to count any customers with a transaction within the 12 month period, did not translate the answer I provided mkeintz to the data. Took some time to play around with this today, it gave me the output I was looking for. Big thanks to everyone for looking at this.

RickyS · ‎02-01-2019

What should be the starting date of the first 12-month window for a customer id? is it the 1st of the month of the first record? Yes, For the sample data 01/01/2001 actual data set 01/01/1999 What should be the ending date of the last 12-month window? is it the end of the month of the last record for the id? actual data 12/31/2017 is it the end of the month 11 months after that last record? 12 months total so 11 months after the first month If you have an id in which the first and last records do not span 12 months, should that id be ignored? Yes, However, for the actual data the last rolling 12 month is 01/01/2017 to 12/31/2017, when I mapped it out in EXCEL assuming I did not make a mistake, it came out exactly through sheer dumb luck The output I'm looking for is a single table that should have 248 observations Periods Count M1 M248

RickyS · ‎02-01-2019

Thank you for your patience as we try to get on the same page. I created more detailed variable names in hopes that it will help in the explanation. I have 15 years of customer data, that includes a customer_id, customer_name and customer_product_id all of which are distinct for any transaction_date but may repeat across transaction_dates. I'm trying to get a rolling 12 month count of customers. So the first rolling 12 month period M1 would be 01/01/2001 - 12/31/2001, The next rolling 12 month period M2 would be 02/01/2001 - 01/31/2002 I'm looking for the count of customer_id or customer_name in buckets of 12. The first M1 output from the Data below would be count of customer_id or customer_name for the Period 01/01/2001 - 12/31/2001 Period Count M1 14 the last transaction_date happens to be 07/18/2001 for the sample data I provided but the code would need to count through 12/31/2001 for the actual data M2 the count would start in 02/01/2001 and go to 01/31/2002 basically drop Jan 2001 to create a rolling 12 month count. hope this helps data abc; input customer_id 1. Customer_name $ 3 - 11 transaction_date mmddyy10. customer_product_id 6.; format date mmddyy10.; cards; 1 Customer1 01/28/2001 123456 1 Customer1 02/01/2001 123457 1 Customer1 02/18/2001 123458 1 Customer1 02/19/2001 123459 1 Customer1 02/21/2001 123460 1 Customer1 02/26/2001 123461 1 Customer1 03/02/2001 123462 1 Customer1 04/01/2001 123463 1 Customer1 05/15/2001 123464 1 Customer1 06/01/2001 123465 1 Customer1 06/23/2001 123466 1 Customer1 06/26/2001 123467 1 Customer1 07/01/2001 123468 1 Customer1 07/18/2001 123469 2 Customer2 01/01/2002 123470 2 Customer2 01/28/2002 123471 2 Customer2 02/18/2002 123472 2 Customer2 02/19/2002 123473 2 Customer2 02/26/2002 123474 2 Customer2 03/02/2002 123475 2 Customer2 05/11/2002 123476 2 Customer2 05/15/2002 123477 2 Customer2 06/23/2002 123478 2 Customer2 06/30/2002 123479 2 Customer2 07/01/2002 123480 2 Customer2 07/16/2002 123481 2 Customer2 08/01/2002 123482 2 Customer2 12/31/2002 123483 ; run;

RickyS · ‎01-31-2019

a_id is a customer id cleaned up so that it is distinct by date and b_id date is equivalent to a transaction date, I've cleaned this up so that it is a max trans date for the mmddyyyy b_id is the id of the transaction

RickyS · ‎01-31-2019

Here is an expected Output the M() values would continue until a rolling 12 month count until they could no longer be calculated Period Count_of_Customers M1(1/28/2001 - 12/31/2001) 14 M2(2/1/2001 - 1/31/2002) 15 M3(3/1/2001 - 2/28/2002) 13 M4(4/1/2001 - 3/30/2002) 13

RickyS · ‎01-31-2019

yes I have sas/ets

RickyS · ‎01-31-2019

I need to create a rolling 12 month count of active customers retaining each 12 month count as a new variable. The first rolling 12 months would simply be Year 1. The second rolling 12 month count would be Year 1 months 2 - 12 and Year 2 month 1. Reading through various examples with arrays if then do statements and and proc expand and still not able to get to the finish line most of the examples are calculating new numeric variables vs counting character values. At this point I'm confused on what the best methodology is other than the one that produces the output. Here is some sample data, the actual data set spans 19 years, so numerous calculated new variables would results Output would be M1 to M? data abc; input a_id 1. name $ 3 - 11 date mmddyy10. b_id 6.; format date mmddyy10.; cards; 1 example1 01/28/2001 123456 1 example1 02/01/2001 123457 1 example1 02/18/2001 123458 1 example1 02/19/2001 123459 1 example1 02/21/2001 123460 1 example1 02/26/2001 123461 1 example1 03/02/2001 123462 1 example1 04/01/2001 123463 1 example1 05/15/2001 123464 1 example1 06/01/2001 123465 1 example1 06/23/2001 123466 1 example1 06/26/2001 123467 1 example1 07/01/2001 123468 1 example1 07/18/2001 123469 2 Example2 01/01/2002 123470 2 Example2 01/28/2002 123471 2 Example2 02/18/2002 123472 2 Example2 02/19/2002 123473 2 Example2 02/26/2002 123474 2 Example2 03/02/2002 123475 2 Example2 05/11/2002 123476 2 Example2 05/15/2002 123477 2 Example2 06/23/2002 123478 2 Example2 06/30/2002 123479 2 Example2 07/01/2002 123480 2 Example2 07/16/2002 123481 2 Example2 08/01/2002 123482 2 Example2 12/31/2002 123483 ; run;

RickyS · ‎01-25-2019

Good Afternoon, I'm trying to follow Rick Wilkins great blog from Oct 20148 on bootstrapping but for a time series exercise. The data on my end is pretty limited, while we have approximately 400 observations we only have 19 years of annual data on those observations right now. The autocorrelations did not show lags but I think that is more a function of annual data and expect that when we are able to migrate to use monthly data we may see some laggards. In the interim bootstrapping seemed like a good idea to ensure we are comfortable with the estimates and confidence intervals for the potential predictors, and I found Rick's recent case resampling blog and I'm trying to work through it with Proc Arima. Right now I'm struggling with some coding syntax as well as open ended questions of is this how and why. Here is some sample data and sample variables DATA COMMERCIAL_ALL_YEARS1; input @1 Account_name $ 7. @9 Date_Year mmddyy10. @18 X1 13. @32 X2 3. @36 X3 4.2 @41 X4 5.4 @47 X5 4.3 @52 X6 4.3 @57 Y 4.3; format Date_Year mmddyy10. X1 best13.; cards; Sample1 1/1/1999 2507741000000 145 0.46 5.330 3.00 1.77 0.30 Sample1 1/1/2000 2708403000000 187 -0.20 6.456 6.82 1.67 0.31 Sample1 1/1/2001 2901728000000 266 2.90 3.687 4.52 1.68 0.30 Sample1 1/1/2002 2923307000000 229 2.51 1.726 5.24 1.92 0.32 Sample1 1/1/2003 2963189000000 176 3.01 1.151 2.55 1.85 0.30 Sample1 1/1/2004 3004471000000 093 1.49 1.563 1.73 1.69 0.29 Sample1 1/1/2005 2998451000000 086 0.01 3.512 2.14 1.44 0.28 Sample1 1/1/2006 3148069000000 066 -0.31 5.153 1.61 1.26 0.27 Sample1 1/1/2007 3348322000000 078 0.70 5.268 3.00 1.17 0.26 Sample1 1/1/2008 3538114000000 138 1.88 2.965 8.47 1.55 1.90 Sample1 1/1/2009 3714737000000 211 3.38 0.556 3.01 2.62 1.60 Sample1 1/1/2010 3951332000000 106 3.01 0.312 2.96 3.70 1.00 Sample1 1/1/2011 4156222000000 086 1.77 0.303 3.74 3.18 0.50 Sample1 1/1/2012 4505611000000 087 1.62 0.283 2.78 2.58 0.30 Sample1 1/1/2013 4798944000000 071 2.91 0.167 1.86 2.08 0.28 Sample1 1/1/2014 5121573000000 054 1.92 0.124 3.20 1.69 0.27 Sample1 1/1/2015 5547467000000 079 1.62 0.227 4.54 1.45 0.26 Sample1 1/1/2016 5835106000000 099 1.60 0.644 2.28 1.35 0.29 Sample1 1/1/2017 6137421000000 071 0.64 1.153 2.31 1.29 1.00 Sample2 1/1/1999 2507741000000 145 0.46 5.330 3.00 1.77 0.30 Sample2 1/1/2000 2708403000000 187 -0.20 6.456 6.82 1.67 0.31 Sample2 1/1/2001 2901728000000 266 2.90 3.687 4.52 1.68 0.30 Sample2 1/1/2002 2923307000000 229 2.51 1.726 5.24 1.92 0.32 Sample2 1/1/2003 2963189000000 176 3.01 1.151 2.55 1.85 0.30 Sample2 1/1/2004 3004471000000 093 1.49 1.563 1.73 1.69 0.29 Sample2 1/1/2005 2998451000000 086 0.01 3.512 2.14 1.44 0.28 Sample2 1/1/2006 3148069000000 066 -0.31 5.153 1.61 1.26 0.27 Sample2 1/1/2007 3348322000000 078 0.70 5.268 3.00 1.17 0.26 Sample2 1/1/2008 3538114000000 138 1.88 2.965 8.47 1.55 1.90 Sample2 1/1/2009 3714737000000 211 3.38 0.556 3.01 2.62 1.60 Sample2 1/1/2010 3951332000000 106 3.01 0.312 2.96 3.70 1.00 Sample2 1/1/2011 4156222000000 086 1.77 0.303 3.74 3.18 0.50 Sample2 1/1/2012 4505611000000 087 1.62 0.283 2.78 2.58 0.30 Sample2 1/1/2013 4798944000000 071 2.91 0.167 1.86 2.08 0.28 Sample2 1/1/2014 5121573000000 054 1.92 0.124 3.20 1.69 0.27 Sample2 1/1/2015 5547467000000 079 1.62 0.227 4.54 1.45 0.26 Sample2 1/1/2016 5835106000000 099 1.60 0.644 2.28 1.35 0.29 Sample2 1/1/2017 6137421000000 071 0.64 1.153 2.31 1.29 1.00 ; RUN; ods graphics on; proc arima data=COMMERCIAL_ALL_YEARS1; title "X Variables"; identify var=Y crosscorr= (X1 X2 X3 X4 X5 X6) nlag= 3; estimate input= (X1 X2 X3 X4 X5 X6) outcorr outcov outest= estimates1; run; quit; title; ods graphics off; /********************************************************************************************* Step 2 Resample 5000 times from the data to form 5000 bootstrap samples currently using the parameter variable names and estimates from proc arima **********************************************************************************************/ title "Bootstrap Distribution of Model Estimates"; title2 "Case Resampling"; %let NumSamples = 5000; %let MU = .59032; %let NUM1 = -3.011; %let NUM2 = -0.004; %let NUM3 = .15342; %let NUM4 = -0.08528; %let NUM5 = 0.18372; %let NUM6 = 0.07935; /* 2. Generate many bootstrap samples by using PROC SURVEYSELECT */ proc surveyselect data=COMMERCIAL_ALL_YEARS1 NOPRINT seed=1 out=BootCases1(rename=(Replicate=SampleID)) method=urs samprate=1 /* OUTHITS */ reps=&NumSamples; run; title; /************************************************************************************************* Step 3 Compute the statistics for each bootstrap sample Should I be using Proc Arima? Or do I use Proc Reg because I'm modeling frequency of the observations that are being sampled ? **************************************************************************************************/ proc arima data= BootCases1; title "Bootstrap Distribution of Model Estimates"; identify var=Y crosscorr= (X1 X2 X3 X4 X5 X6) nlag= 3; estimate input= (X1 X2 X3 X4 X5 X6) outcorr outcov outest= PEBoot; run; quit; title; ods graphics off; OR proc reg data=BootCases1 outest=PEBoot1 noprint; by SampleID; freq NumberHits; model Y = X1 X2 X3 X4 X5 X6; run; quit;

RickyS · ‎01-09-2019

Thank you everyone as I looked at the solutions I think they would all work but this one seemed the most simplistic, and easiest to validate. Did not think o using a having statement this way. cheers

RickyS · ‎01-09-2019

Id Year Name Score 1 1999 A 85 1 2000 A 85 1 2001 A 87 1 2002 A 85 1 2003 A 85 1 2004 A 85 1 2005 A 85 1 2006 A 85 1 2007 A 85 1 2008 A 90 1 2009 A 85 1 2010 A 85 1 2011 A 82 1 2012 A 85 1 2013 A 85 1 2014 A 85 1 2015 A 85 1 2016 A 85 1 2017 A 85 2 1999 B 85 2 2000 B 85 2 2002 B 85 2 2003 B 85 2 2004 B 85 2 2005 B 85 2 2006 B 85 2 2007 B 85 2 2008 B 90 2 2009 B 85 2 2010 B 85 2 2011 B 82 2 2012 B 85 2 2013 B 85 2 2014 B 85 2 2015 B 85 2 2016 B 85 2 2017 B 85 So we do not Id 2 because it had no score in 2001, if there is no score and no observation for a given year want to drop the observation

RickyS · ‎01-09-2019

Good Morning did some google searching and searching through SAS Communities and did not see a solution for what I would like to do, I'm sure its out there just can't find it. Continuing my search but thought I'd post. I have a data set with observations from 1999 - 2017, trying to figure out a simple way to select observations that appear in each and every year. Cannot think of a way to translate a sub-setting statement ......id must appear in every year OR Id cannot be missing for any year. Observations are unique by id and year but may not repeat for each year. Sorry should ay that I know I could subset the file into 1 data set for every year and then rejoin based on year 1999 using id, but there must be a better way. Thanks

RickyS · ‎12-11-2018

I built out a process to complete some distribution analysis using the designer window, it grew over time and got pretty complex. I never thought to export the code. Unfortunately, I found out the file was moved into a new folder today and now the process will not run since it cannot find the original data file. Is there a way to re-point the filtering and analysis to the new folder without the code? Since it is the original source data the link filters does not seem to be an option.

Online Status	Offline
Date Last Visited	‎03-18-2019 01:47 PM

Re: Creating values for missing data to enable summary statistics

Creating values for missing data to enable summary statistics

Re: Rolling 12 month Count across Years of Customers

Re: Rolling 12 month Count across Years of Customers

Re: Rolling 12 month Count across Years of Customers

Re: Rolling 12 month Count across Years of Customers

Re: Rolling 12 month Count across Years of Customers

Re: Rolling 12 month Count across Years of Customers

Re: Rolling 12 month Count across Years of Customers

Rolling 12 month Count across Years of Customers

Re: Replacing If then with Macro

Re: Array to replace if then statement 9.4

Re: How to identify unique reords in two data sets

Re: Proc Tabulate SAS Guide 6.1

Re: Creating values for missing data to enable summary statistics

Creating values for missing data to enable summary statistics

Re: Rolling 12 month Count across Years of Customers

Re: Rolling 12 month Count across Years of Customers

Re: Rolling 12 month Count across Years of Customers

Re: Rolling 12 month Count across Years of Customers

Re: Rolling 12 month Count across Years of Customers

Re: Rolling 12 month Count across Years of Customers

Re: Rolling 12 month Count across Years of Customers

Rolling 12 month Count across Years of Customers

PROC ARIMA Bootstrap

Re: Selecting observations that appear in all years

Re: Selecting observations that appear in all years

Selecting observations that appear in all years

project cannot run file moved to new library