I'm wondering how, if I have a dataset of data that is in two contiguous sets of dates, i.e.: date 1/2004 2/2004 3/2004 4/2004 5/2004 6/2004 7/2004 8/2004 9/2004 10/2004 11/2004 12/2004 1/2008 2/2008 3/2008 4/2008 5/2008 6/2008 7/2008 8/2008 9/2008 10/2008 11/2008 12/2008 And then would want separate datasets, one starting at 1/2004 up to the first discontiguos date (i.e. in the above simple example, until 1/2008) and then a dataset from 1/2008 onward. I'm having some trouble coding an algorithm for this as it must be very general, the starting date for each set of contiguous dates could be anything, there could be any amount of data from 100 months of data to 12, and each set could be any number of months apart, I just want to find the distinction as soon as their are two consecutive dates that are more than a month apart. I deeply appreciate any suggestions. ... View more

Hello all, I am getting "invalid argument" error to both the MONTH() function and YEAR() function, but I am confused because I don't see why the argument is invalid. The variable is "month_date" and here are two example observations: month_date 31Jul2010 0:00:00 31Jul2010 0:00:00 in SAS column properties the data format is listed as "DATETIME20." -- Why is this not a valid format for these functions which state in their documentation that they accept datetime variables? I must be missing something here.. -Thank you! ... View more

Hey guys, I was wondering if it is possible to adjust the color of the 'markers' for each grouping: essentially my data looks as follows: Date Rate forecast_rate 12/31/2012 1.72 _10YrTr 12/31/2013 1.3 _10YrTr 12/31/2014 1.8 _10YrTr 12/31/2015 2.3 _10YrTr 12/31/2012 0.07 _3MoTr 12/31/2013 0.1 _3MoTr 12/31/2014 0.1 _3MoTr 12/31/2015 0.1 _3MoTr I would like to plot it as follows: proc sgplot data=endyear_givens2;   scatter x=forecast_Rate y=rate / group=date;   run; This gives me almost exactly what I need: the labels "_10YrTr" and "_3MoTr" on the x-axis with the numbers values labeled on the y axis, and the legend contains dates by marker shape. What I would like that is missing though, is for each year (each marker) to have a separate color, I can't figure out how to do this - if I use the markatts(color=) option it changes all 8 markers' colors, I want to see each year group (so both _3MoTr and _10YrTr markers will be one color for 12/31/2012, and then both _3MoTr and _10YrTr markers will be a different color for 12?31/2013, etc.) - Is This possible? (I've attached the current sgplot output to perhaps be more clear) Thank you! ... View more

Okay, So this question isn't quite "how to do something within proc sql" but more about how proc sql implements the min() function -- Essentially, I have this piece of SAS code: proc sql;   create table day_t1_path_t as   select Acct as acct_t1, CurBal as curbal_t1, Date as date_t1, min(date) as path_start_t1, 2 as tenure_t1,   min((select curbal_t from day_t_path_t where acct_t = Acct),curbal) as minbal_t1   from small_init; quit; Now what I am trying to do is port this code over to be able to run in SQL management studio with raw SQL (no sas functions) -- I know that the Min() function works differently in SAS than SQL, for example to be able to implement the second min() call in SQL I need to use the case statement because SQL's min function doesn't allow for variable comparisons, only minimums across columns (i.e: "case when (select curbal_t from day_t_path_t where acct_t = Acct) < curbal then (select curbal_t from day_t_path_t where acct_t = Acct) else CURBAL end) as minbal_t1") However, the problem I'm having is with the first call to min() "min(date) as path_start_t1"  -- I am trying to take the minimum value for "date" in the "from" table, and use that value for observations of this "path_start_t1" variable in my select clause. This way the new table I am creating will have a column "path_start_t1" where it's value in each row is equal to the minimum date in the "from" table. -- This works perfectly in SAS, but in SQL I get an error for all of the variables in the select clause other than the min(date) because they aren't in a group by (or aggregate function) -- this makes sense because in SQL the min(function) is pulling one single value and so must collapse all other variables in the select clause by groupings in the select clause so that it is clear to SQL how to determine the return value for min() in each row of the output table. What I would like to understand is how this works in SAS - is the under lying function implementing some extra SQL queries to make this possible, or is this not at all possible in SQL and done with some other internal SAS functions? I hope my question is clear and applicable to this forum. I'd appreciate any information, thank you very much! ... View more