topic Re: conditional replacing values in a variable in SAS Programming

conditional replacing values in a variable

satish123 — Mon, 27 Mar 2017 13:58:06 GMT

hello people,

i've a dataset like bellow.

x y

1 0

1 23

1 56

1 0

i want to replace the zero with '.' but there is one thing that i'm worried with.

last three observations in y are zeros. replace last two zeros with '.' , last but two and first values should stay zeros. ie output will look like

x y

1 0

1 23

1 56

1 0

1 .

Thanks in advance.

Re: conditional replacing values in a variable

PaigeMiller — Mon, 27 Mar 2017 14:06:16 GMT

Does this question have anything to do with the value of X? Please explain. Or give a bigger example.

What if the last two values are not zero? What happens then? Please explain. Or give an example.

Re: conditional replacing values in a variable

collinelliot — Mon, 27 Mar 2017 14:08:32 GMT

A solution for the specific case you presented is pretty easy, but what other cases might exist? I assume this needs to be done by the different x values, for one. Also, within an x value, do non zero numbers appear in more than one grouping surrounded by zeros? That is, could you have 0, 0, 1, 2, 3, 0, 0, 0, 0, 1, 2, 3, 0...?

Re: conditional replacing values in a variable

satish123 — Mon, 27 Mar 2017 14:43:59 GMT

hello paigemiller and collinelliot,

there is more data with last observations zeros and non zeros.

example:

x y

1 0
1 311
1 65
1 102
1 217
1 174
1 18

1 0

2 0

2 12
2 65
2 77
2 92
2 0
2 208
2 226
2 19
2 0 ...etc

1 has three consecutive zeros and out of those only last two zeros need to replace with '.' . ie values 174 18 0 0 0 will be 174 18 0 . .

keep the value zero if its preceeding value is non zero. replace zero with '.' if its preceeding value is zero.

if zero comes first keep it as zero. zeros in between non zero values should be zeros only.

o/p

x y

1 0
1 311
1 65
1 102
1 217
1 174
1 18

1 0

1 .

2 0

2 12
2 65
2 77
2 92
2 0
2 208
2 226
2 19
2 0

hope you understood.

Re: conditional replacing values in a variable

collinelliot — Mon, 27 Mar 2017 14:52:50 GMT

I think this works for the more extended sample you provided. If there are more nuances to the real data, this might need to modified or scrapped altogether for an alternative approach.

data have;
input x y;
datalines;
1 0
1 311
1 65
1 102
1 217
1 174
1 18
1 0
1 0
1 0
2 0
2 12
2 65
2 77
2 92
2 0
2 208
2 226
2 19
2 0
;


data want;
    set have;
    by x;
    y2 = y;
    if not first.x then do;
        if y = 0 and lag(y) = 0 then y2 = .;
    end;
run;

Re: conditional replacing values in a variable

Astounding — Mon, 27 Mar 2017 15:21:32 GMT

I think the easiest way to do this would be to reverse the order.

data ordered;

set have;

original_order = _n_;

run;

proc sort data=ordered;

by descending original_order;

run;

Then it becomes easier to detect which observations need to change, since you can start at the beginning of each X value.

data want;

set ordered;

by x y notsorted;

if first.x then do;

if y=0 then change='Y';

else change = 'N';

end;

retain change;

if change='Y' then do;

if last.y then change='N';

else y=.;

end;

drop change;

run;

And finally, put it all back in the original order:

proc sort data=want;

by original_order;

run;

Re: conditional replacing values in a variable

satish123 — Mon, 27 Mar 2017 15:34:20 GMT

thanks every one each reply helped me a lot.

Re: conditional replacing values in a variable

mkeintz — Mon, 27 Mar 2017 16:36:58 GMT

I think this has been granted "solution" status prematurely.

It does not do what I believe the OP wants when (1) there are consecutive zero's in the middle of an X series, or (2) when a given X has only two observations, both equal to zero. See the data sample below:

1 0

1 311

1 65

1 102

1 217

1 0

1 174

1 18

1 0

2 0

2 12

2 65

2 77

2 92

2 0

2 208

2 226

2 19

2 0

3 0