SAS Support Communities

Why the non-SAS standard variable names? If they not essential why use them as you have to wrap quotes and an "N" around every one of them. So much extra typing. ... View more

Hi  @sivakoya  see if this helps   data have; input LOAN_NBR INT_AMT_PAID PRIN_AMT_PAID EFF_FROM_DT :date9.; format EFF_FROM_DT date9.; cards; 60016100 20.58 -98.50 07Jan2019 60016101 29.72 -80.28 07Jan2019 60016101 15.19 -94.81 28Jan2019 60016102 52.70 -57.35 16Jan2019 60016102 0.00 -110.05 16Jan2019 60016103 121.08 -235.92 04Jan2019 60016122 41.29 -89.06 17Jan2019 60016122 -41.29 89.06 24Jan2019 60016122 57.27 -73.08 29Jan2019 60016212 29.24 -73.85 09Jan2019 60016212 -29.24 73.85 17Jan2019 60016274 142.96 -186.55 11Jan2019 60016274 -142.96 186.55 21Jan2019 60016326 45.99 -96.08 03Jan2019 60016326 -45.99 96.08 08Jan2019 60016326 57.48 -84.59 10Jan2019 60016468 61.45 -113.64 31Dec2018 60016468 -61.45 113.64 31Dec2018 60016501 17.69 -80.70 15Jan2019 60016501 18.76 -79.63 17Jan2019 60016501 -17.69 80.70 17Jan2019 60016701 18.76 -81.24 08Jan2019 60016701 -18.76 81.24 14Jan2019 60016701 26.48 -73.52 22Jan2019 60016701 3.75 -96.25 29Jan2019 60016760 24.25 -102.86 31Dec2018 60016760 5.15 -121.96 07Jan2019 60016760 -5.15 121.96 14Jan2019 60016760 13.24 -113.87 18Jan2019 60016848 32.63 -77.37 21Dec2018 60016848 50.90 -55.10 04Jan2019 60016848 -32.63 77.37 04Jan2019 60016848 18.04 -87.57 18Jan2019 ; data temp; set have; n1=abs(INT_AMT_PAID); n2=abs(PRIN_AMT_PAID); run; proc sort data=temp out=_temp; by loan_nbr n1 n2 ; run; data want; n=1; do _n_=1 by 1 until(last.loan_nbr); set _temp; by loan_nbr; array t(9999); if sum(INT_AMT_PAID,lag(INT_AMT_PAID))=0 and sum(PRIN_AMT_PAID,lag(PRIN_AMT_PAID))=0 then do; t(n)=_n_-1; n+1; t(n)=_n_; end; end; do _n_=1 by 1 until(last.loan_nbr); set _temp; by loan_nbr; if _n_ not in t then output; end; drop t: n:; run; If you want more robust, I can apply hashes , but we do not know how comfortable you are though, so feel free to let us know ... View more

data have; infile datalines truncover ; informat id cell $12. home $12. work $12.;; input ID cell $ home $ work $ ; datalines; 1 (XXX)XXX-XXXX (XXX)XXX-XXXX (YYY)YYY-YYYY 2 (ZZZ)ZZZ-ZZZZ (YYY)YYY-YYYY (ZZZ)ZZZ-ZZZZ 3 (ZZZ)ZZZ-ZZZZ (YYY)YYY-YYYY (YYY)YYY-YYYY 4 (XXX)XXX-XXXX (XXX)XXX-XXXX (XXX)XXX-XXXX 5 (XXX)XXX-XXXX (YYY)YYY-YYYY (ZZZ)ZZZ-ZZZZ 6 (XXX)XXX-XXXX ; run; data want; if _n_=1 then do; length k $ 100; declare hash h(); h.definekey('k'); h.definedone(); end; set have; length dup_phone all_same $ 1 dup_from temp $ 200; array x{*} $ cell home work; do i=1 to dim(x); k=x{i}; h.replace(); end; if h.num_items=1 then do; if cmiss(of x{*})=0 then do;dup_phone='Y';dup_from='All_dup';end; else do;dup_phone='N';dup_from='All_missing';end; all_same='Y'; end; else do; dup_phone='N';dup_from='No_dup'; do i=1 to dim(x)-1; do j=i+1 to dim(x); if not missing(x{i}) and not missing(x{j}) and x{i}=x{j} then do; yes=1; dup_phone='Y';temp=catx('|',temp,cats(vname(x{i}),'_',vname(x{j}))); end; end; end; if yes then dup_from=temp; all_same='N'; end; h.clear(); drop i j k temp yes; run; ... View more

data have; input ID value ; datalines; 1 0 1 0 1 100 1 200 2 0 2 0 2 200 2 0 2 0 3 0 3 100 3 50 3 0 3 200 ; run; data want; set have; by id; retain want . ; if first.id then call missing(want); want=max(want,value); run; ... View more

The benefit of a SET statement  coupled with a BY statement is that you know when you've reached the last record for a by group.  And if that last record precedes 31dec2016, you also know to output more records:   data want; set have; by account_nbr; output; if last.account_nbr and month_end<'31dec2016'd then do until (month_end='31dec2016'd); month_end=intnx('month',month_end,1,'e'); output; end; run; ... View more