Apply logic based on waterfall for unique ids

Accepted Solution Solved
Reply
Contributor
Posts: 28
Accepted Solution

Apply logic based on waterfall for unique ids

Hi

           i have a sample dataset where i need to apply logic to unique_id as per code it has in waterfall manner.

 

For instance if for unique ID 1,  Code A is found which gets Yes in preference then other instance of unique ID 1 will get "no" in Preference column

for unique ID 2 Code B is found which gets Yes in preference then other instance of unique ID 2 will get "no" in Preference column

for unique ID 3 Code E is found which gets Yes in preference  then other instance of unique ID 3 will get "no" in Preference column

 

so waterfall for Code follows the order as A,B,E,D,C.

So A gets first prefernce if found for unique id,

     B gets second and so on

How can i apply this waterfall for unique id. So in nutshell if i find a code for unique id as per Waterfall it should get Yes in preference and rest all other instance of same unique id get No in preference.

 

UNIQUE_ID        CODE          Preference

        1                    A                    Yes

        1                    B                     no

        2                    B                     Yes

        2                    C                      no

        3                    C                      no

        3                    D                      no

        3                    E                      Yes


Accepted Solutions
Solution
‎06-02-2017 12:22 PM
Valued Guide
Posts: 797

Re: Apply logic based on waterfall for unique ids

Read all the 'Yes' values, determining the prime yes (i.e. A=yes supersedes B=yes >> E=yes >> C=yes >> D=yes).  Then read all the cases turning everything to No except the prime yes.

 

In other words if an ID starts out with 3 Yes's and 2 No's, the 3 Yes's are read first [see the "where=(preference='Yes')" parameter] to determine the prime yes.  Then all 5 Yes's and No's are read, assigning No to all but the prime yes:

 

data have;
  input UNIQUE_ID        CODE :$1.          Preference :$3.;
datalines;
1 A Yes
1 B no
2 B Yes
2 C no
3 C no
3 D no
3 E Yes
run;

data want (drop=prime_rank);
  set have (in=inyes where=(preference='Yes'))
      have (in=inkeep);
  by unique_id;
  if first.unique_id then prime_rank=.;
  retain prime_rank;

  if inyes then do;
    prime_rank=min(prime_rank,findc('ABEDC',code));
    delete;  /* Delete this case, it will be read again, and output below */
  end;
  if NOT(code=CHAR('ABEDC',prime_rank) and preference='Yes') then preference='No';
run;

 

 

 

 

  

View solution in original post


All Replies
Super User
Posts: 17,750

Re: Apply logic based on waterfall for unique ids

Create a custom format that maps the order.

 

ABEDC

Then A->1

B->2

E->3

D->4

C->5

 

Sort by the new variable and use BY groups to identify the first. 

http://support.sas.com/documentation/cdl/en/lrcon/69852/HTML/default/viewer.htm#p0e9b2d12lpyjkn1b0y1...

Solution
‎06-02-2017 12:22 PM
Valued Guide
Posts: 797

Re: Apply logic based on waterfall for unique ids

Read all the 'Yes' values, determining the prime yes (i.e. A=yes supersedes B=yes >> E=yes >> C=yes >> D=yes).  Then read all the cases turning everything to No except the prime yes.

 

In other words if an ID starts out with 3 Yes's and 2 No's, the 3 Yes's are read first [see the "where=(preference='Yes')" parameter] to determine the prime yes.  Then all 5 Yes's and No's are read, assigning No to all but the prime yes:

 

data have;
  input UNIQUE_ID        CODE :$1.          Preference :$3.;
datalines;
1 A Yes
1 B no
2 B Yes
2 C no
3 C no
3 D no
3 E Yes
run;

data want (drop=prime_rank);
  set have (in=inyes where=(preference='Yes'))
      have (in=inkeep);
  by unique_id;
  if first.unique_id then prime_rank=.;
  retain prime_rank;

  if inyes then do;
    prime_rank=min(prime_rank,findc('ABEDC',code));
    delete;  /* Delete this case, it will be read again, and output below */
  end;
  if NOT(code=CHAR('ABEDC',prime_rank) and preference='Yes') then preference='No';
run;

 

 

 

 

  

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 101 views
  • 0 likes
  • 3 in conversation