Hi,   Thank you so much for your help!   The desired outcomes are the person-time was accrued within its own period. Observation 1 developed outcome in T5, which is in pre-stage, then the expected PT_pre=sum(of T1-T9)=3. Observation 3 developed outcome in both pre- and post-stages, so the expected PT_pre=sum(of T1-T9)=5 and PT_post=sum(of T10-T20)=2. It looks like only the calculated results for observation 3 are not correct. Could you please advice how should I modify the code to cooperate my request?    Again, I really appreciate it!   ID Outcome Outcome_timepoint PT_pre PT_Post T1 T2 T3 T4 T5 T6 T7 T8 T9 T10 T11 T12 T13 T14 T15 T16 T17 T18 T19 T20 XXXXX1 1 T5 3 0 0 1 1 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 XXXXX2 0 NA 5 5 0 1 1 0 1 1 1 0 0 0 0 1 1 0 1 1 1 0 0 0 XXXXX3 1 T13 5 2 0 1 1 0 1 1 1 0 0 0 0 1 1 0 0 0 0 0 0 0 XXXXX4 0 NA 5 5 0 1 1 0 1 1 1 0 0 0 0 1 1 0 1 1 1 0 0 0 ... View more

Thank you so much for the code. Just out of curiosity, what do those statements mean within the PRX function? ... View more

A data step to create the list SQL to create the macro variable Use in code data list_codes; length code $ 3; do i=1 to 19, 30 to 99; code=catt('F', put(i, z2.)); output; end; run; proc sql noprint; select quote(code, "'") into :code_list separated by ", " from list_codes; quit; %put &code_list; data want; set have; array D(30) D01-D30; do i=1 to 30; if D(i) in (&code_list) then Flag=1; end; drop i; run; Another option I sometimes use when generating manual lists is to use Excel.  I've attached an example of how that works and then I copy/paste it in the code.  Issue with this, if you ever need to change it you usually need to rebuild it from scratch (which is quick) but annoying. Rarely an issue when you're hard coding lists like these though.      ... View more

I don't think the array helps with the codes you are trying to find.  But it will help with searching multiple variables to see if any of them contain one of the target codes.   So you could preload the codes into a hash object and the for each observation loop over the array of variables and check if that value is in the hash object.  You can stop once you find at least one array element that matches. data want ; if _n_=1 then do; set have2(keep=v); declare hash h(dataset:'have2(keep=v)'); rc=h.definekey('v'); rc=h.definedone(); end; set have1 ; array vars v1-v4 ; do index=1 to dim(vars) until(result); result=not h.find(key:vars[index]); end; drop rc index v; run; Results: Obs v1 v2 v3 v4 result 1 a1 b2 c234 d987 1 2 e45 h76 k93 a1 1 3 d9087 b4 m7908 v9090 1 4 s6987 k2983 o87 f338 0 5 b2 f338 g6 x3 1 6 Y3 h76 009 m1a 1 7 m1a a1 2983 f02 1 8 2983 k8 y4 w348 1 9 k93 e5 w77 h5 1 10 h76 b2 1 11 k93 1 ... View more

Hi Ballardw,   Really appreciate your code. It worked very well to meet my analysis requirement.   My next step is to create a another table with different date layout.   I am having the dataset again: data Have; input id $ datestr :$10. Yes_No; format date mmddyy10.; date = input(datestr!!"-01",yymmdd10.); drop datestr; datalines; 1 2015-04-01 1 1 2015-04-12 0 1 2015-05-01 0 1 2016-02-01 1 1 2016-07-01 1 2 2015-01-01 0 2 2015-06-24 1 2 2015-07-02 0 2 2015-11-25 1 2 2015-12-24 0 2 2015-12-31 0 2 2016-01-20 0 ; run; And, what I want is to create a table that each row represents a month and have all the observations happened in that month, regardless Yes_No=1 or 0, but still have the variable Yes_No in the final dataset. The value of Yes_No for each row is the value for the first observation of that month (i.e. Yes_No=1 for 4/1/2015, Yes_No=0 for 12/24/2015). If there are two or more observations in one month then have them in the same row, just like the table below: ID Yes_No month dispense_date dispense_date_of_following_1 dispense_date_of_following_2 1 1 2015-04 4/1/2015 4/12/2015 1 0 2015-05 5/1/2015 1 1 2016-02 2/1/2016 1 1 2016-07 7/1/2016 2 0 2015-01 1/1/2015 2 1 2015-06 6/24/2015 2 0 2015-07 7/2/2015 2 1 2015-11 11/25/2015 2 0 2015-12 12/24/2015 12/31/2015 2 0 2016-01 1/20/2016   Thank you very much again for the help!!! Best regards, C ... View more

data want;  set have;    by ID;       length comb combination $5;       retain comb type;       combination = cats(a1, a2, a3, a4, a5);          ***(or try combination = cats(of a1-a5);       if first.id then do;           type=1;           output;           comb=combination;       end;       else do;           if combination ne comb then type+1;           output;           comb=combination;       end; run;               ... View more

@CynthiaWei wrote: Hi,   Thank you so much for the code! I was not able to work on this project in the past weeks so sorry for my late reply.   I think your understanding is quite close to my task. I think there is one criterion needs to add here: if a given combination of id*first_date*edu*class has one record do nothing, i.e. leave the last_date value as is.  But if that combination has more than one record and one record has a date difference of 0, then assign last_date=first_date for the rest. So, if a given combination of id*first_date*edu*class has more than one record but each record has a date difference >0 and none of the records has a date difference of 0 within this ID, then leave them. Examples like follows:(there is no record like "5   8-19-2017  8-19-2017    1         1" for this person) ID  First_date   Last_date    EDU   Class 5   8-19-2017  11-11-2017    1         1 5   8-19-2017  12-22-2017    1         1 Could you please advice me what the code look like when incorporating the above situation. I apologize for not specifying this before.   So instead of assigning last_date=first_date for all combinations that have multiple records, as I had programmed before, just do it for all combinations that have multiple records, of which at least one already has last_date=first_date:   In that case then during the first pass, the program should still count records by combination.  But in addition, if any record has first_date=last_date, then add (say) 1,000 to the count.  I choose 1,000 because I assume no combination is likely to have near 1,000 records.   This means: A single record would have count=1 or 1,001 Multiple records would have counts 2,3,4,...    or 1,002+ Therefore, during the second pass, only count>1,001 requires assigning last_date=first_date     data want; set have (in=firstpass) have (in=secondpass); by id; array _n_fdates {%sysevalf("01jan2015"d):%sysevalf("31dec2017"d),3,3} _temporary_; if first.id then call missing(of _n_fdates{*}); if firstpass then do; _n_fdates{first_date,edu,class}+1; if first_date=last_date then _n_fdates{first_date,edu,class}+1000; end; if secondpass; if _n_fdates{first_date,edu,class}>1001 then last_date=first_date; run;       And if the variables that are going to be involved in a given combination are varA, varB, varC, varD, varE, varF and varG, I just need to put all of them in the "{}" in the if then statement, right? In short yes.  Make provision for each variable in the array statement, and then use those variable names as the array indexes in the subsequent statements. ... View more

Hi @CynthiaWei  Good morning. Your problem has some similarities to one of our production code at our bank that flags "debit" and "credit" transactions, however majority of the folks here are SQL users and their comfort lies with Proc SQL. Your sample date is char format, however I'm assuming the real one is indeed a proper numeric SAS date. If not, it's trivial as it merely requires minor conversion to numeric sas date in the conditional execution.  In Proc SQL, you can use summary functions at best and perhaps push the query into the database which the folks here are good at-   data have; input group date :mmddyy10. age gender ; format date mmddyy10.; cards; 1 1/1/2017 3 0 1 1/2/2017 1 1 1 1/3/2017 10 1 1 1/9/2017 5 0 1 1/11/2017 11 1 2 1/1/2017 12 0 2 1/3/2017 10 1 2 1/7/2017 9 1 2 1/11/2017 16 0 2 1/20/2017 15 1 ; run; proc sql; create table want as select *,age>=10 and date>=min(case when gender and age>=10 then date else . end) as Enroll_Yes from have group by group order by group, date; quit; Note: You could use IFN instead of CASE WHEN albeit you pay a performance penalty. On the other hand, CASE WHEN is convenient and portable as SQL engine takes care of it.  ... View more

Hi @CynthiaWei  While it's a privilege to learn from the real "Proc Star- @PaigeMiller " who would make SAS look innocent with his mastery of Procs, with yet another demonstration of a Proc that does with utmost elegance, here is another alternative of Datastep that utilizes the UPDATE construct to fill the missing values. data have; input ID A B; cards; 1 10 . 1 . 16 1 . 16 1 10 . 2 5 . 2 5 . 2 5 . 2 . 7 ; data want; do _n_=1 by 1 until(last.id); update have(obs=0) have; by id; end; do _n_=1 to _n_; output; end; run; The above technique might be a bit of learning curve when you intend to do further processing i.e. should the need is to go beyond after fill  in  a situation that would involve look up/matrix programming(hashes/multi dim arrays) among others for further processing.   Alternatively, the MAX operator/function is indeed the traditional way as it works for both SQL/Datastep. For one, the syntax being convenient and many users are quite familiar with SQL as is often known as "readymeals" solution, proc sql; create table want as select a.id,A,B from have(keep=id) a left join (select id, max(a) as A, max(b) as B from have group by id) b on a.id=b.id; quit; If you are familiar with automatic remerge functionality of Proc SQL, you might be tempted to attempt that auto fill, albeit the SQL processor wouldn't see the inclusion of a non summary list variable to force or trigger that. Therefore, you would need an explicit  self JOIN.   Fun stuff! Have a great day and Kind regards 🙂    ... View more

I have modified my code so that it handles properly the case where A is prescribed twice on a day, or B is prescribed twice on a day (or both! 😊 )   data long; set have; do date=start_taking_date to last_taking_date by 1; output; end; run; proc freq data=long; tables id*drug*date/noprint out=frequencies; run; data long1; merge frequencies(where=(drug='A') rename=(count=countA)) frequencies(where=(drug='B') rename=(count=countB)); by id date; retain overlap_first_date; both_drugs=(countA>=1 and countB>=1); prev_both_drugs=lag(both_drugs); prev_id=lag(id); if first.id then prev_both_drugs=0; if prev_both_drugs=0 and both_drugs=1 and (first.id or id=prev_id) then overlap_first_date=date; if prev_both_drugs=1 and both_drugs=0 and id=prev_id then do; overlap_end_date=date-1; delta=overlap_end_date-overlap_first_date; output; end; format overlap: yymmdd10.; drop drug percent prev: countA countB date; run;    ... View more

Create a separate data set of the 16 months you want each person to have.  Cross join that with a distinct selection of person id to get complete month list per person, follow up with left joining to original data.   Example: data have; input ID Flag count month; attrib month format=yymmd. informat=ANYDTDTE.; datalines; 1 1 9 2017-03 1 1 9 2017-04 1 1 9 2017-05 1 1 9 2017-06 1 1 9 2017-07 1 1 9 2017-08 1 1 9 2017-09 1 1 9 2017-10 1 1 9 2017-11 1 1 9 2017-12 2 1 6 2017-06 2 1 6 2017-07 2 1 6 2017-08 2 1 6 2017-09 2 1 6 2017-10 2 1 6 2017-11 3 0 0 . 3 0 0 . 3 0 0 . 3 0 0 . 4 0 0 . ; * list of wanted months; data months; do month = '01jan2017'd to '01APR2018'd by 1; output; month = intnx('month', month, 0, 'E'); end; format month yymmd.; run; proc sql; create table want as select ids.id , have.flag , have.count , months.month as month from (select distinct id from have) as ids /* individual ids */ cross join months /* wanted months */ left join have /* original data */ on have.id = ids.id & have.month = months.month order by ids.id, months.month ; ... View more