BookmarkSubscribeRSS Feed
SASPhile
Quartz | Level 8


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)

5 REPLIES 5
Reeza
Super User

 

What have you tried? 

 

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

 

SASPhile
Quartz | Level 8

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;

Reeza
Super User

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. 

 

ballardw
Super User

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?

SASPhile
Quartz | Level 8

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.

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 935 views
  • 0 likes
  • 3 in conversation