topic Re: Apply logic based on waterfall for unique ids in SAS Programming

Apply logic based on waterfall for unique ids

sasuser101 — Fri, 02 Jun 2017 00:43:36 GMT

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

Re: Apply logic based on waterfall for unique ids

Reeza — Fri, 02 Jun 2017 02:42:06 GMT

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#p0e9b2d12lpyjkn1b0y1tqg6q146.htm

Re: Apply logic based on waterfall for unique ids

mkeintz — Fri, 02 Jun 2017 04:21:28 GMT

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;