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)
What have you tried?
Posting your requirements without any code or attempt makes it seem like you're asking someone to do your work...
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;
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.
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?
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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.