@Tom    A very nice use case for the undocumented "monotonic" function in PROC SQL. ... View more

It looks to me like you want to create pre-1963 historic adjusted highs and low, and then compare each post-1962 observation to those historic extremes.  You can do that in one pass of the crsp data, which is typically already sorted by PERMNO/DATE.   data want ; set crsp (where=(shrcd IN (10,11) and exchcd in (1,2,3,31,32,33)); by permno date; prc_split_adjusted =abs(prc)/cfacpr; retain ohh ohdate /*Pre-1963 historical adjusted high and date of its latest occurence*/ ohprc ohcfacpr /*Nominal PRC and CFACPR on OHDATE */ oll oldate /*Pre-1963 historical adjusted low and date of its latest occurence*/ olprc ohlfacpr /*Nominal PRC and CFACPR on OLDATE */ ; format ohdate oldate date9.; if first.permno then call missing(of oh:, of ol:); if date<'01jan1963'd then do; if prc_split_adjusted=max(prc_split_adjusted,ohh) then do; ohh=prc_split_adjusted; ohdate=date; end; if prc_split_adjusted=min(prc_split_adjusted,oll) then do; oll=prc_split_adjusted; oldate=date; end; end; if date>='01jan1963'd; /* This is a "subsetting IF" */ ** Do your code for comparing post-1962 to OHH and OLL here **; run;     ... View more

@DrAbhijeetSafai wrote: @chrej5am  Thanks!   Method = relative option in proc compare statement helped me. Thanks once again.   Thank you.   - Dr. Abhijeet Safai For the benefit of the rest of us, please mark the response with the needed answer as a solution.  Thank you. ... View more

Apparently Excel stores time values as fractions of a day,  where 06:00:00 displayed is stored as 0.25, 12:00:00 noon stored as 0.5, etc.  Those fractional values are what is brought by PROC IMPORT.   But SAS records time of day as number of seconds after midnight (06:00:00 is stored as 21600, noon is stored as 43200).  So you have to do two things after the proc import:   Multiply each value by 86400 (number of seconds in a day). Reformat the variable using a TIME format.   data work.svoi2014_formatted; set work.svoi2014; array timvals {*} tobito--retiradafuneraria; do i=1 to dim(timvals); timvals{i}=timvals{i}*86400; end; format tobito--retiradafuneraria TIME8.; drop i; run;   ... View more

@pdick2    You want  all cases for ID's that have sepsislabel always at zero, or that change from zero to one. You earlier said your data has no instance of going from one to zero. If the above are both true, then you simply want all ID's that start with zero,  That's a simple task.   If the data are sorted by PATIENT_ID/Hour, then:   data want (drop=_:); retain _keep ' '; set have; by patient_id ; if first.patient_id=1 then _keep=ifc(sepsislabel=0,'Y','N'); if _keep='Y'; run; If the data within patient_id are sorted by HR, and the patient_id's are grouped, but not sorted, then change the BY statement to: by patient_id notsorted;     Now if the data are sorted by HR, you don't have to re-sort by patient_id/hr.  And it doesn't matter what the order is within each HR value.  So, if sorting by patient_id/hr is expensive, then you can use a hash object:   data want (drop=_:); set have; by patient_id; length _keep $1; if _n_=1 then do; declare hash h(); h.definekey('patient_id'); h.definedata('_keep'); h.definedone(); end; if h.find()^=0 then do; /*If this is first instance for this ID ...*/ if sepsislabel=0 then _keep='Y'; /*Check the initial sepsislabel value */ else _keep='N'; h.add(); /*Put it in the hash object */ end; if _keep='Y'; run; These are untested. ... View more

For data sorted by client_id/segm_date, the technique I would use is to look forward to the upcoming segm_date to see if there will be holes to fill with missing values.  The benefit of this approach is that you can first output the current non-missing values, and then set them to missing in advance of filling holes.  Something like (using the sample data provided by @ballardw   data example; input SEGM_DATE datetime18. CLIENT_ID :$9. AVG_WEIGHT; format SEGM_DATE datetime20.; datalines; 01MAR2012:00:00:00 1-ET-1500 8 01APR2012:00:00:00 1-ET-1500 10 01JUN2012:00:00:00 1-ET-1500 13 01JAN2012:00:00:00 2-ET-1500 12 01JUN2012:00:00:00 2-ET-1500 18 run; data want (drop=nxt_:); set example (keep=client_id); by client_id; merge example example (firstobs=2 keep=segm_date rename=(segm_date=nxt_date)); **** Other Needed Code Here ****; output; /* Write out the non-missing values */ /* If there are upcoming holes, fill them with missing values. */ if last.client_id=0 and intck('dtmonth',segm_date,nxt_date)>1 then do; /*If holes follow ... */ call missing(avg_weight); /* Set the appropriate list of variables to missing */ do segm_date=intnx('dtmonth',segm_date,1,'same') by 0 while (segm_date<nxt_date); output; segm_date=intnx('dtmonth',segm_date,1,'same'); end; end; run; The SET and BY statement combination is just to generate the first.client_id and last.client_id dummy vars.  The MERGE statement merges the current obs with the next obs (but only the SEGM_DATE variable, renamed to NXT_DATE).  Comparing SEGM_DATE to NXT_DATE provides a way to detect upcoming holes.     ... View more

This program runs proc tabulate only of variables with minimum values greater than 5.   If there are no such variables, no proc tabulate will be invoked.   data mydata; input score1 score2 ; datalines; 10 110 11 111 2 112 13 113 14 114 15 115 run; data _null_; set mydata end=end_of_data; array s {*} score1 score2 ; array names {2} $32 _temporary_ ("SCORE1","SCORE2"); do v=1 to dim(s); if names{v}^=' ' then do; if 0<=s{v}<=5 then names{v}=' '; end; end; if countw(catx(' ',of names{*}))=0 then do; put '*** Every VAR has at least one value < 5. ***'; put '*** No PROC TABULATE will be done. ***'; stop; end; if end_of_data; call execute('proc tabulate data=mydata;'); call execute(catx(' ','var',of names{*},';')); call execute(catx(' ','table',of names{*},', n nmiss min max',';')); call execute('run;'); run; When a variable has a value <=5 its name is removed from the NAMES array.  That array of names is what gets submitted to the proc tabulate.   The "call execute" statement queues up SAS code to be run immediately after the current DATA step.   ... View more

After seeing @ItsMeAG 's response, it occurred to me that you want only obs up through the last type='136', but not any obs that follow.  If so, here is a possibility:   data want (drop=_:); set have (where=(type='136') in=found136) have (in=secondpass); by account_number; if first.account_number=1 then _n136=0; _n136+found136; if secondpass and _n136>0; *subsetting IF*; if type='136' then _n136=_n136-1; run;         ... View more

@Justin9    There is some ambiguity in your request.  You start out by saying   Can someone tell me what the best way to keep all previous records if one account has type = 136 (type is a character variable)?  but later you say   where type=136, I want to keep all instances where the same account appears So which is it?  All obs for the identified account?  Or just the preceding obs? And if it's the preceding obs, do you want the type=136 obs also?     My solution, and @PaigeMiller's provide all the obs.  But @ItsMeAG's response selects every obs through the last type="136".   BTW, it would simplify code to know: Can your data have more than one type="136" for a single account? Is your data sorted by type, within account?  You sample data is. ... View more

If your data are sorted by account_number:   data want; merge have (where=(type='136') in=wanted) have; by account_number; if wanted=1; run; The WANTED dummy variable will be a 1 for all account numbers in have that include at least 1 instance of "136".   That's due to the way a MERGE statement followed by a BY statement works.  For each account number, whichever merge data set has fewer obs will have its last instance matched against the "excess" obs in the other data set, including the dummy varaible associated with the "shorter" data set.  The "136" obs are the shorted dataset in this case. ... View more

In the absence of sample data in the form of a working DATA step, this code is untested, but straightforward:   data want; merge have (where=(LOT='1L' and regimen='BR-based') in=include) have; by id; if include=1; run;   It assumes: Data are sorted by ID. The indicator of a desired ID is to have an observation with LOT='IL' and regiment='BR-bases'. You want the entire collection of obs for such ID's. ... View more

@Patrick wrote: @mkeintz Appears to be a "duplicate" question with an accepted solution here True.     The code I offered above takes advantage of the fact that the data are sorted, which AFAICT, SQL does not.  There is likely to be a significant performance benefit.   It uses the same technique as the "carry the future back" section in a presentation I gave at PharmaSUG 2019 (History Carried Forward, Future Carried Back: Mixing Time Series of Differing Frequencies), except it mixes a subset of a series with the whole series.   One of the reasons I have come to promote this technique is that it is fairly simple to expand mixing of two series to three or more series, which I think can become rather messy in SQL.  Applying it to finding the "closest" value only explicitly occurred to me when contemplating this OP's post.   Plug:  I'll be presenting content from the PharmaSUG paper at next month's Boston Area SAS User's group.  And I hope to include an example of "finding the closest" as well.     ... View more

In the absence of sample data in the form of a working DATA step, this should work.   data vneed / view=vneed; set have (rename=(alcohol_user=_alc_user)); by dmrn; where _alc_user^=' '; dx_date=ifn(first.dmrn=1,'01jan1900'd,mean(dx_date,lag(dx_date)),'01jan1900'd); run; data want (drop=_:); set have (in=inh) vneed (in=inv keep=dmrn dx_date); by dmrn dx_date; if inv then set vneed (keep=_alc_user); if inh; if alcohol_user=' ' then alcohol_user=_alc_user; run; How does this work?   Data set view VNEED has only non-missing alcohol_user data (renamed to _alc_user).  But its dx_dates are changed.   dx_date is reset to halfway between the current valid obs and the prior valid obs ("valid" means non-missing _alc_user for the same DMRN).   And in the case of the first non-missing _ALC_USER for a given DMRN, the DX_DATE is set back to 01jan1900.   As a result, when VNEED is interleaved with HAVE (the SET HAVE ... VNEED; BY DMRN DX_DATE statements), one can use the IF INV condition to read in (and retain) the non-missing _ALC_USER variable, until the next IF INV is satisfied.  And since the DX_DATEs in vneed are at halfway points between observed DX_DATEs, missing ALCOHOL_USER will always get the closest DX_DATE with non-missing values.   Important note:  the first SET statement in DATA WANT, must NOT read in _ALC_USER.  Therefore _ALC_USER is not automatically replaced, until the IF INV condition is satisfied.   Edited note: This assume data are sorted by DMRN/DX_DATE. ... View more