DATA Step, Macro, Functions and more

flag records based on certain condition

Reply
Occasional Contributor
Posts: 8

flag records based on certain condition

hello,

  

I am stuck with something please help me out with this..

I have a table as-

 

wr_no    rfa_no   stk_ref     status

1               1          x             fp

1               2          x             fp

1               1          y             fp

1               1          x             nu

2               1

2               1          x             fp

1               1          x             gd

1               2

1               1          x             gp

1               1          x             gc

 

I want to create  a flag as 0 for records having status 'nu' i.e for every  'nu' of  stk_ref i want to flag 0  'fp' but not ('gd','gp','gc') with reference to my stk_ref ,wr_no and rfa_no.so basically here i want to flag 0 1st and 4th record 

because for the same stk_ref i got 'nu' which has unique  wr_no and unique rfa_no.there are  blank stk_ref and status also for which the flag should be zero.

 

And the table contains 5billion rows.

 

 

Super User
Super User
Posts: 7,411

Re: flag records based on certain condition

Can you show what the outpu should look like.  The simplest method is to extract the data with nu, and merge this back to your original data base on your identifier variables, and then you can blank out where not needed, something like (not tested):

data temp;
  set have (where=(status="nu"));
run;

data want;
  merge have temp (rename=(status=mrg_status));
  by wr_no rfa_no stk_ref;
  if status ne "fp" then mrg_status="";
run;
Occasional Contributor
Posts: 8

Re: flag records based on certain condition

It should look like this-

 

OUTPUT:-

 

wr_no    rfa_no   stk_ref     status   flag

1               1          x             fp         0

1               2          x             fp         1

1               1          y             fp         1

1               1          x             nu        0

2               1                                     0

2               1          x             fp         1

1               1          x             gd        1

1               2                                     0

1               1          x             gp         1

1               1          x             gc         1

 

Ask a Question
Discussion stats
  • 2 replies
  • 126 views
  • 0 likes
  • 2 in conversation