Hi SAS Users,   I am trying to rollup the records by member_id  util there is a change in 2 fields. I have given 2 scenario examples in the raw and what i tried in my code to get the expected.   Raw data -  ( A change in 2 fields ( PLAN & TYPE) , should generate the new line with its begin and end date)   member_id     date                    plan        type 123                01JAN2017         A               1 123                01FEB2017         A               1 123                01MAR2017        A               2 234                01JAN2017         A              1 234                01FEB2017         B              3 234                01DEC2017        B              3 234                01JAN2018         B              2     Expected  rollup -    member_id     start_date                 end_date             plan        type 123                01JAN2017              30FEB2017            A              1 123                01MAR2017              31MAR2017           A            2 234                01JAN2017              31JAN2017            A              1 234                01FEB2017              31DEC2017            B           3 234                01JAN2018              31JAN2018            B           2     proc sort data=test; by member_id date ; run;   data test; length PLAN_TYPE $10.; set test; format start_date end_date  date9. ; plan_type = trim(plan) || trim(type); by member_id; retain plan_type  plan_type _tmp; if first.member_idthen     do;            plan_type_tmp= plan_type ;            begin_date = date;            end_date = intnx('month',fisc_dt,1)-1;           end_date1 =end_Date+1;     end; else if date= end_date1 then do;                    if trim(plan_type_tmp) ne trim(plan_type ) then do; start_date = date;                                                                                                   plan_type_tmp= trim(plan_type );                                                                                             end;                    else                       end_date = date; end; if last.member_id  then output; run; ... View more

Hi SAS Users,   I need help in building the below logic.   Ex:1) If I have start_date  as 01-01-2018  and end_date as 12-31-2021   for member '123' how do I transpose them into 8 columns like below. Ex:2) if I have start date as 01-01-208 and end_date as  12-31-2019 for member '234' then rest of the dates should be blank.   first_start_date         first_end_date  second_start_date         second_end_date  third_start_date         third_end_date  fourth_start_date         fourth_end_date 01-01-2018              12-31-2018        01-01-2019                    12-31-2019             01-01-2020              12-31-2020           01-01-2021                  12-31-2021 01-01-2018              12-31-2018        01-01-2019                    12-31-2019     Thanks, Ana         Thanks, Ana ... View more

Hi SAS Users,   I need a help.   I am updating a table with PROC SQL syntax and where condition is not working properly.   year_month value is coming as 01FEB2018 ( a date field) and concatenation needs character conversion , i converted it to put(year_month1,date9.) and looks like  ( ABC01FEB2018 which is id || put(year_month1,date9.)  ) on the right side, i have min(year_month) which is in SAS integer format, i need to convert this into  01FEB2018 format to get the merge correctly.   Or IS there any better way to merge this charcter-date field with min(date) ?   proc sql; create table dataset2_2 as select * from dataset2_1 ; update dataset2_1 a set new_ind =99 where new_ind =1 and (id ||  put(year_month1,date9.)) not in ( select id || min(year_month) from dataset2_2 where new_ind =1)         /* not working */ ; Quit;       Thanks, Ana             ... View more

Hi SAS Users, Need a help solving the dates correctly.    I am looking for the members where the datetime1 is 2 weeks greater than max_datetime2.   b.datetime1 -  Ex: 06FEB2021:11:37:16.453000   ( it is in date time format) a.a.max_datetime2 - it is max of some datetime field . ( It is in numeric format)  - Ex: 1925365763.1   My Query -    proc sql; create table two_weeks as  select a.memberid, a.callid,count(*) from table1 a inner join table2 b on a.memberid = b.memberid and b.datetime1 >= (a.max_datetime2 + 2 weeks ) group by a.memberid, a.callid ; Quit;   Thanks, Ana ... View more

Hi SAS Users,   I am working on substracting max(end_date) - min(begin_date) on one code.  end_Date  and Begin_date fields are datetime fields. when i add max and min to those fields they are converting to numbers.   I am looking to get the total number of days from that substraction.   Total_days = max(end_Date) - min (begin_date)     Thanks, Ana     ... View more