By Group -- Replace a var's values based on another variables specific value

[Nikos]

Dear all,

In a by group I have  missing values in variable STATUS  for  various Trade_Quarters.

I would like to replace the STATUS missing values based on the following:

If STATUS = 'C' (=cancelled) then if any  SUBSEQUENT TRADE_QUARTERS take STATUS ='C'

If STATUS = 'C' (=cancelled) then if any PRIOR  TRADE_QUARTERS take STATUS = 'V'  (=Valid)

If there is no STATUS = 'C' then ALL values of STATUS become = 'V'

Pls. see data HAVE and WANT in the attached file.

Thanking you in advance for any hint.

Kind regards

Nikos

[Nikos]

just a typo

in the last by group: where 'O' should be replaced with 'V'

Thank you

[mohamed_zaki]

Check this *** Not tested

Data new ;

infile datalines dlm=',';

input STATUS $;

datalines;

C,

C,

,

V,

V,

,

,

V,

,

C,

,

,

;

run;

data newC;

retain newV ' ';

set new;

if _n_=1 and STATUS = ' ' then Do ;

*STATUS = 'V';

newV='V';

End;

Else if STATUS in ('C','V') then newV=STATUS;

*Else if STATUS=' ' then STATUS=newV;

run;

[mohamed_zaki]

the previous one to see the change but the final code should be like the following"

data newC (drop=newV );

retain newV ' ';

set new;

if _n_=1 and STATUS = ' ' then Do ;

STATUS = 'V';

newV=STATUS;

End;

Else if STATUS in ('C','V') then newV=STATUS;

Else if STATUS=' ' then STATUS=newV;

run;