topic Re: Pin Change in SAS Programming

Pin Change

SASPhile — Tue, 29 Mar 2016 14:27:44 GMT

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)

Re: Pin Change

Reeza — Tue, 29 Mar 2016 14:59:52 GMT

What have you tried?

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

Re: Pin Change

SASPhile — Tue, 29 Mar 2016 15:13:34 GMT

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';
run;

Re: Pin Change

Reeza — Tue, 29 Mar 2016 15:32:42 GMT

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.

Re: Pin Change

ballardw — Tue, 29 Mar 2016 15:58:34 GMT

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:

Lpin=lag(pin);

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?

Re: Pin Change

SASPhile — Tue, 29 Mar 2016 15:37:24 GMT

Ballardw,

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.