@Ronein wrote: Why array have 6 arguments? The array does NOT have six elements, it has seven elements, where the lower bound is zero (not one) and the upper bound is six.  When you divide the date value by 7 and keep the remainder, you will end up with 0, 1, ..., 5, or 6.  So each day of the week can trivially be placed in exactly one element of the array.   ... View more

@Ronein wrote: Why array have 30000 arguments? How dud you know to choose this number ? If you are using an array from 0 to 30000, it covers Jan 1, 1960 through 19feb2024.  You can customize the date range per below (say for 01jan2009 through 31dec2020):   %let begdate=01jan2009 ; %let enddate=31dec2020 ; data; * other sas code ; array history {%sysevalf("&begdate"d):%sysevalf("&enddate"d)} _temporary_ ; * other sas code ; run; ... View more

Here's a data step that effectively maintains a moving window of offer amounts for the prior 7 days.   With each obs, first weed out stale (over 7 days prior) data.   Then just take the maximum of that small array.   Then update the arrays with the new obs   data have; format date ddmmyy10.; input custid date :date9. loansAmnt offerAmnt; cards; 111 01JUL2025 . 50000 111 03jul2025 . 22000 111 08jul2025 . 19000 111 09jul2025 5000 . 222 01jul2025 . 40000 222 03jul2025 . 28000 222 04jul2025 13000 . 222 08jul2025 . 27000 223 09jul2025 35000 . run; data want (drop=_:); set have; by custid; array tempdat{0:6} _temporary_; /*Observed dates within prior 7 days*/ array tempval{0:6} _temporary_; /*Observed offers within prior 7 days*/ /* Weed stale data first */ if first.custid=1 then call missing(of tempval{*},of tempdat{*}); else do while (.< min(of tempdat{*}) < date-7); _d=mod(min(of tempdat{*}),7); call missing(tempval{_d},tempdat{_d}); end; /* Get the maximum */ if n(of tempdat{*})>0 then max_prior_7days=max(of tempval{*}); /*Update the arrays with current obs */ _d=mod(date,7); tempdat{_d}=date; tempval{_d}=offeramnt; run;   Ignore the previously sumitted code below, which didn't weed before taking the maximum:   data want (drop=_:);   set have; by custid;   array tempdat{0:6} _temporary_; /*Observed dates within prior 7 days*/   array tempval{0:6} _temporary_; /*Observed offers within prior 7 days*/     /* Get the maximum, when there is a 7-day window available */   if first.custid=0 and dif(date)<=7 then max_prior_7days=max(of tempval{*});   else call missing(of tempval{*},of tempdat{*});     /*Update the arrays with current obs */   _d=mod(date,7); tempdat{_d}=date;   tempval{_d}=offeramnt;     /*Weed out stale data */   do while (min(of tempdat{*})<date-7);     _d=mod(min(of tempdat{*}),7);     call missing(tempval{_d},tempdat{_d});   end;   run; Two points: The temporary arrays are indexed from 0 to 6, effectively representing a day-of-week index.  So the data in the arrays are not ordered strictly chronologically, but order doesn't matter for getting the maximum.  The task is to be sure to eliminate stale values. The DIF(x) function is   x-lag(x). ... View more

  An interviewer insisting on PROC SQL code for the 3rd highest value is not attempting to assess your SAS expertise. ... View more

@Wolverine    Glad you identified the problem.  Mark your own explanation as the solution, so that this topic no longer appears as unsolved. ... View more

If the dataset is not particularly big, then I think your best option is the hash object technique suggested by @Patrick. The code is simple.   That solution requires reading the data in two unsynchronized data streams, which could create a performance hit in the case of large datasets, due to disk activity.   Now if the data were sorted by VAR2, you could do:   data want; merge have (where=(var1='XX') in=wanted) have; by var2; if wanted; run; which synchronizes the data streams and reduces disk activity.     Your data are not sorted by VAR2, although it is grouped by VAR2.  So you can generate data stream synchronization with code such as this:   data want (drop=_:); set have (where=(var1='XX') rename=(var2=_var2)); do until (last.var2=1 and var2=_var2); set have; by var2 notsorted; if var2=_var2 then output; end; run;     The above assumes no more than one var1='XX' for any given VAR2.  If you can have two or more var1='XX' cases, then: data want (drop=_:); set have (where=(var1='XX') rename=(var2=_var2)); by _var2 notsorted; if last._var2 then do until (last.var2=1 and var2=_var2); set have; by var2 notsorted; if var2=_var2 then output; end; run;   And yes, the degree of synchronization achieved would depend on the distribution of the "XX" cases.   ... View more

You can transfer the components of the cell into a hash object with the "ordered" attribute.  Then iterate through them and concatenate each of them:   data have; input col1 col2 :$50. ; cards; 1 1,5,4,3,2,6,3.5 2 21,3,2,1,5,15,17,3 3 1,2,3 4 4,3,2 5 1,2,junk,3 ; data want (drop=i _:); set have; if _n_=1 then do; declare hash h (ordered:'A',multidata:'Y'); h.definekey('_x'); h.definedata('_x'); h.definedone(); declare hiter hi ('h'); end; do i=1 to countw(col2,','); _x=input(scan(col2,i,','),best12.); if _x^=. then h.add(); end; length new_col $50; do while (hi.next()=0); new_col=catx(',',new_col,_x); end; h.clear(); run; ... View more

What if you want to find the closest match to X145, from a list of variables named X14,X15,X125,X147.X345,X545?   As @PaigeMiller has said, would the use of spelling distance have any utility at all in this case? ... View more

If you change z_char=put(z,best.); to  z_char=put(z,8.2); then z_char will display what you want.  Assuming this statement is the first reference to z_char, it will be an 8-byte character variable.  It will also be right justified, and with two decimal places.   You may not want it to be right justified, in which case, you can use z_char=left(put(z,8.2)); But remember that if you left justify the character value, then sorting by z_char may not generate the same order as sorting by z (i.e. '11.18   ' would precede '6.18    '). --- Lexicographic ordering. ... View more

  If each dataset is sorted by ID, then   data want (drop=_:); set t1 (in=in1) t2 t3; by id; retain _found_in_t1; if first.id then _found_in_t1=in1; if _found_in_t1; run; ... View more

The PROC SUMMARY offers a neat compact single-pass solution.  It will certainly use a lot less disk input/output resources than the PROC SORT solution, and will probably be a lot faster - assuming there is no memory constraint.   BUT ... does your data have the possibility of tied maximum time_flag values for a given ID/VAR1/VAR2?   If not, then ignore the rest of this comment.     But if it does, then the PROC SUMMARY might not likely give the same result as the PROC SORT ... if LAST.VAR2 solution.  It will choose different records (with possibly different VAR3/VAR4 values) among the tied records.   This is because the default behavior of PROC SORT is to preserve the original order (from the unsorted dataset) of tied records.  So that solution would always choose the latest of the tied records.   A quick test of PROC SUMMARY with ties suggests it would always choose the first of such ties.  At least it did so in the test below:   data ties; set sashelp.class (keep=name sex age weight); order='First'; output; order='Last' ; output; run; proc summary nway data=ties; class sex age; output out=summ_want (drop=_:) idgroup (max(weight) out (name weight order)=); run; proc sort data=ties; by sex age weight; run; data sort_want; set ties; by sex age; if last.age; run; Dataset summ_want has order='First' in every output record, but the PROC SORT approach always has ORDER='Last'. ... View more

If I understand your request correctly, then:   data test; infile datalines truncover; input usubjid $3. aestdtc :$10. @17 aendt date7. ae & :$200.; format aendt date9.; datalines; 101 2024-12-18 28JAN25 Appetite lost 101 2024-12-19 26DEC24 Constipation 101 2024-12-30 10FEB25 Thrombopenia 101 2025-01-06 07FEB25 Neutropenia 101 2025-01-27 07FEB25 Neutropenia 101 2025-01-13 23MAR25 Anemia 101 2025-02-10 23MAR25 Anemia 101 2025-01-25 10FEB25 Dizziness 101 2025-01-25 10FEB25 Dyspnea 101 2025-01-25 . Fatigue 101 2025-01-27 31JAN25 Nausea 101 2025-01-27 31JAN25 WBC decreased 101 2025-02-03 15FEB25 Rhinitis 101 2025-03-01 . Epigastralgia 101 2025-03-01 03MAR25 Nausea 101 2025-03-01 03MAR25 Vomiting 101 2025-03-24 . Anemia 101 2025-03-24 . Thrombopenia run; DATA WANT; SET test; by usubjid ae notsorted; astdt=input(aestdtc,yymmdd10.); format astdt date9.; if astdt>lag(aendt) or first.ae=1 then join+1; if first.usubjid=1 then join=1; run; Do you have instances of a given AE qualifying to be assigned the same JOIN value, but occurring in non-consecutive observations?  If so, this code would need to be modified.   Note the BY statement allows detection of whenever a new AE description occurs (first.ae=1).   Also please provide your sample data in a working data step. For example, your informat of date9. for aendt should have been date7. ... View more

This is a good use case for applying conditional SET statements in a data step.   Assuming: SET1 is sorted by ID/d_start/b/c/d SET1 has no instances of overlapping d_start-d_end date ranges SET2 is sorted by ID/eventstart then data set1; input id $ d_start :date9. d_end :date9. physcat $ a b c d e; format d_start d_end date9.; datalines; s001 01JAN2020 31DEC2020 A 1 2 3 1 0 s001 01JAN2020 31DEC2020 A 1 3 3 1 0 s001 01JAN2021 31DEC2021 B 1 3 3 2 1 s002 01JUN2019 31MAY2020 A 2 1 2 1 1 s002 01JUN2020 31MAY2021 C 2 2 2 2 0 s003 01JAN2020 31DEC2022 B 1 3 4 1 0 run; data set2; input id $ eventstart :date9.; format eventstart date9.; datalines; s001 15JUN2020 s001 20JUL2021 s002 15AUG2019 s002 01JUL2020 s003 01JAN2021 s003 01JAN2023 run; data need /view=need /*Keep lowest B C D for each ID/D_START*/; set set1; by id d_start b c d; if first.d; run; data want (drop=_:); set need (keep=id d_start in=in1 rename=(d_start=_ref_date)) set2 ( in=in2 rename=(eventstart=_ref_date)); by id _ref_date; retain _left_sentinel .; if in1 then set need ; retain _right_sentinel ' '; if in2 ; set set2; if first.id or eventstart>d_end then call missing(of _left_sentinel--_right_sentinel); run; This will also accommodate multiple events within a date range. ... View more