BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
satish123
Fluorite | Level 6

hello people,

 

i've a dataset like bellow.

x       y

1      0

1      23

1      56

1      0

1      0

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      .

1      .

 

Thanks in advance.

1 ACCEPTED SOLUTION

Accepted Solutions
collinelliot
Barite | Level 11

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;

View solution in original post

7 REPLIES 7
PaigeMiller
Diamond | Level 26

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.

--
Paige Miller
satish123
Fluorite | Level 6

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

1     0

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     .

1     .

2     0

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

 

hope you understood.

collinelliot
Barite | Level 11

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;
mkeintz
PROC Star

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 0

1 0

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

3 0

3 0

 

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
collinelliot
Barite | Level 11

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...? 

Astounding
PROC Star

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;

satish123
Fluorite | Level 6

thanks every one each reply helped me a lot.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 10837 views
  • 2 likes
  • 5 in conversation