If you use the NOTSORTED option (to accomodate "CODE" value that might either ascend or descend, then    data have; input ID SEQ Code $ date :ddmmyy10.; format date ddmmyy10.; datalines; 001 1 ST 05/04/2014 001 2 ST 05/01/2014 001 3 ST 04/01/2014 001 4 GP 02/05/2014 001 5 ST 02/01/2014 002 1 ST 05/01/2014 002 2 ST 03/02/2014 002 3 GP 02/01/2014 002 4 GP 02/01/2014 run; data want (drop=_:) ; set have; by id code notsorted; if first.id=1 then _code_group=0; _code_group + (last.code=1); if last.code and _code_group=1; run; ... View more

Your description of your objective is very sparse.  But you can do "inter" obs comparsons by reading through a single id group, in a DO UNTIL LAST.ID  do-group.  Something like:   data have; input ID $ Product $ Acc $ Type $ Value1 Value2 Value3; datalines; 1 C 3 Red 3 3 1 1 A 1 Blue 2 1 0 2 D 2 Orange 0 2 . 2 B 4 Yellow 4 4 2 3 B 3 Blue 5 3 . 3 C 5 Orange 1 1 . ; data want (keep=ID ERR); do until (last.id); set have; by id; if catx(',',product,acc)='B,4' and value1>0 then _condition_a1=1; else if catx(',',product,acc)='D,2' and value1=0 then _condition_a2=1; length type_list $400; type_list=catx(',',type_list,type); end; length ERR $10; if n(of _condition_a:)=2 then do; err='E3'; output; end; if findw(type_list,'Blue')>0 and findw(type_list,'Orange')>0 then do; err='Type 2 error'; output; end; run; ... View more

Thank you for providing working code to create dataset HAVE.   You "want to know how many clients in each clinic stayed at a different clinic and the cost of the stay at the different clinic."   Your desired result for clinic 3 shows a COUNT of 1 and a COST of 9,588.   Now the only client for clinic 3 that has stays at other clinics client L, which explains why you have a COUNT of 1.  But, with the following data for L Client Span_Begin Span_End Clinic Stay Cost L 07/01/2022 07/15/2022 1 3 9,588 L 07/26/2022 08/17/2022 12 1 2,060 L 09/23/2022 02/19/2023 12 1 5,149   why does your sample desired result have COST of 9,588?  Shouldn't it be 7,209 (2,060 + 5,149 for two visits to clinic 12)? ... View more

Questions:   Are you saying that you have a file containing the policy numbers to remove and the names of the pre-existing datasets from which those policies are to be removed?     OR If a policy is to be removed, is it to be removed from ALL datasets in which it may be found? Are the pre-existing datasets sorted by policy ID?  Answering this will help in determining whether to use hash methods for removing policies. ... View more

@jorge_garzon_co    "COD" is a very ambiguous topic title.  Do you have a question about the program you presented? ... View more

You don't need to use PUTN.   Since your variable CLOSINGCYCLE is character, (length 6), you, can change the WHERE statement to just use the   =:   (equals then colon) comparison operator against the constant generated by the expression "&_CYCLE".   The =: comparison takes the shorter character value (in _closingcycle) and compares it to an equal length substring of the longer character value (i.e. the first six characters of &_CYCLE).     where closingcycle =:  "&_CYCLE"; which produces the code where closingcycle =: "20180831";   ... View more

Your data appear to be already grouped by companyid and degreetype.  If so, then:   data have; infile cards expandtabs truncover; input DirectorID CompanyID AwardYear DegreeType :$9.; cards; 1928993 62323 1998 Undergrad 1798822 62360 1998 Undergrad 2384193 467713 2003 Other 1769649 467713 2003 Other 1777732 1519459 2002 Undergrad 1627886 1519459 2003 Undergrad 1323941 1519459 2004 Undergrad 1839100 60212 2008 Grad 1706497 60212 2008 Undergrad ; data want; array awdcount{1990:2010} _temporary_; call missing(of awdcount{*}); do until (last.degreetype); set have; by companyid degreetype notsorted; awdcount{awardyear}+1; end; do until (last.degreetype); set have; by companyid degreetype notsorted; nmatch=sum(awdcount{awardyear-1},awdcount{awardyear},awdcount{awardyear+1})-1; output; end; run; ... View more

I would make a copy of the original string, then starting at character 201, search backwards for a blank.  Copy the identified length from the copy to the subtext variable (length $200), shift the copy leftwards by the identified number of characters, and repeat, ... until the copy is blank:   data want (drop=_: i); set have; length subtext $200; _copy=original; do part=1 by 1 while (_copy^=' '); do i=201 by -1 while (char(_copy,i)^=' '); end; subtext=substr(_copy,1,i-1); _copy=substr(_copy,i+1); output; end; run; Note this program assumes there is always blank somewhere from character 2 through character 201, through each modification of variable _COPY.     ... View more

Do they all have the form     "Cdtr":{"Nm":"XXX XXXX XXXXXX"} where "XXX XXXX XXXXXX" is the desired result, and can have variable length?   If so, you can always skip the first 14 characters and drop the last 2 characters, which can be done with the SUBSTR and LENGTH functions:   data sample; string='"Cdtr":{"Nm":"DEUTSCHE BANK"}'; new_string=substr(string,15,length(string)-16); put (_all_) (= /); run; Or if you want something more general, perhaps you can characterize the desired substring as the 5th word, where each double quote is treated as a word separator.  Then, as long as the desired expression is double-quoted, and it is preceded by two expressions that are also double quoted (i.e. preceded by 4 double quotes), you can do this:  data sample; string='"Cdtr":{"Nm":"DEUTSCHE BANK"}'; new_string=scan(string,5,'"'); put (_all_) (= /); run; ... View more

@ModeratelyWise wrote: Thanks. Forgive my ignorance, but how do create that variable in the combined dataset?   You could just use from_abc=4*ina + 2*inb + 1*inc; But it might be better just to keep three dummy variables as below.  That would be easier to remember.   data all_groupings; merge a_sorted (in=ina) b_sorted (in=inb) c_sorted (in=inc); by _all_; from_a=ina; from_b=inb; from_c=inc; run;     ... View more

@Wickedestjr wrote: Hello,   I have a dataset like this with hundreds of additional columns and thousands of rows:   First_name | Last_name | Version | Total_payment Scott | Smith | 1 | $100 John | Smith | 3 | $10 Michael | Scott | 2 | $50 Michael | Scott | 3 | $75 Michael | Scott | 4 | $100 Michael | Jordan | 1 | $20 Michael | Jordan | 2 | $50   I'm looking for a (hopefully) slick way of updating the table so it only includes rows for the latest version of each unique First/Last name combination, like this:   First_name | Last_name | Version | Total_payment Scott | Smith | 1 | $100 John | Smith | 3 | $10 Michael | Scott | 4 | $100 Michael | Jordan | 2 | $50   The dataset is also not necessarily sorted in any way like I have it presented here. Is there any simple way of achieving this?   If the dataset is "not necessarily sorted", I presume that a given firstname/lastname combination is not always presented as consecutive observations, yes?   If so, then what do you mean by "latest"?  Do you mean the obs with the highest VERSION value?   Or do you simply mean the last obs encountered for a given firstname/lastname?   Here's code that keeps the observation with the highest VERSION value:   data _null_; if _n_=1 then do; if 0 then set have; declare hash h (dataset:'have (obs=0)',ordered:'a'); h.definekey('first_name','last_name'); h.definedata(all:'Y'); h.definedone(); end; set have (rename=(version=_vers)) end=end_of_have; if h.find()^=0 then call missing(version); if _vers>version then do; version=_vers; h.replace(); end; if end_of_have then h.output(dataset:'want'); run; If you just want the last obs enountered, then:   data _null_; set have; declare hash h (dataset:'have',duplicate:'r',ordered:'a'); h.definekey('first_name','last_name'); h.definedata(all:'Y'); h.definedone(); h.output(dataset:'want'); stop; run;   ... View more