topic Re: By Group -- Replace a var's values based on another variables specific value in SAS Procedures

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

Nikos — Sun, 28 Sep 2014 08:48:02 GMT

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

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

Nikos — Sun, 28 Sep 2014 08:54:31 GMT

just a typo

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

Thank you

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

mohamed_zaki — Sun, 28 Sep 2014 10:00:56 GMT

Check this *** Not tested

Data new ;

infile datalines dlm=',';

input STATUS $;

datalines;

;

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;

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

mohamed_zaki — Sun, 28 Sep 2014 10:17:29 GMT

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;