DATA Step, Macro, Functions and more

Pin Change

Super Contributor
Posts: 713

Pin Change

Acct                  pin                       last_updt          cd                Output
371520005 WKCQM0349867533 9/23/2011      X00
371520005 WKCQM0349867533 1/26/2014     X01             change
371520005 WKCQM0349867533 1/26/2014      X00              change
371520005 010488909014X99      1/8/2016       X97              change
371520005 010488909014X99     1/10/2016      X20           No change
371520005 010488909014X99     1/10/2016       X01           No change
371520005 WKCQM0349867533   1/10/2016     X00                 change
371520005 010488909014X99      1/10/2016       X97                 change


An account will have their pin updated.

if the first five characters of PIN is alpha then it is a valid pin, if not Invalid pin.

if combination of valid pin and cd is different from top row then there is a change (row1 and row2)

if there is change from valid Pin to invalid pin then there is change(row3 and row4)

if there is change in invalid pin and cd between rows then no change (row 4 and row5 and row5 and row6)

how to get summarized stats for an account:
No of Changes:5
No of distinct identifiers :2 (WKCQM0349867533 and 010488909014X99)

Distinct No of Invalid Pins:1 (010488909014X99)

Distinct No of Valid Pins:1 (WKCQM0349867533)

Super User
Posts: 23,683

Re: Pin Change


What have you tried? 


Posting your requirements without any code or attempt makes it seem like you're asking someone to do your work...


Super Contributor
Posts: 713

Re: Pin Change

Tried this but doesnt seem to work as expected:

data test1;
set test;
if anydigit(substr(pin,1,1))=1 then pin_ver='Digit';
else pin_ver='Alpha';
if pin_ver='Alpha' and cats(pin,cd)=lag(cats(pin,cd) then chng='No Change';
else chng='Change';
if pin_ver='Digit' and pin=lag(pin) then chng='No Change';
else chng='Change';

Super User
Posts: 23,683

Re: Pin Change

Break your problem up and try each one at a time.

Your first requirement is 5 characters are alpha, so why does your substr formula look at only the first digit? 


Also, post what your output should be.


You can use LAG to compare to the previous row.


Look at RETAIN for carrying values down through the ID. 


Super User
Posts: 13,508

Re: Pin Change

[ Edited ]

Lag and Dif are occasionally wonderful functions but understanding the value streams takes some work. Generally you will be much better off creating unconditional temporary variables (you drop them from the data set later) such as:




And use Lpin in your comparisons instead.

If pin = Lpin then do ...


And how does this affect your earlier question on "flip flop" of pin values?

Super Contributor
Posts: 713

Re: Pin Change


  Pin change will be super set of flip flop. Once the pin changes are identified there are some addistional rules to be plugged in to see flip flop criteria.


Ask a Question
Discussion stats
  • 5 replies
  • 3 in conversation