topic Re: flag records based on certain condition in SAS Programming

flag records based on certain condition

nid197 — Tue, 01 Aug 2017 08:32:41 GMT

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.

Re: flag records based on certain condition

RW9 — Tue, 01 Aug 2017 08:50:32 GMT

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;

Re: flag records based on certain condition

nid197 — Tue, 01 Aug 2017 08:59:52 GMT

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