Hi @Ksharp
This is desired output I am looking for, in the previous code this is assigning value 999 at all blank records, but I am looking for 999 on those inbetween blank spaces where there is any value before and after the blank records.
Ex: ID=2, section=c,f,g.
But this code is assigning values 999 at all blank records. Please suggest something else.
ID | Sequence | section | value | desired |
1 | 1 | a | 0 | 0 |
1 | 2 | b | . | . |
1 | 3 | c | 1 | 1 |
1 | 4 | d | 0 | 0 |
1 | 5 | e | 0 | 0 |
2 | 1 | a | 1 | 1 |
2 | 2 | b | 1 | 1 |
2 | 3 | c | . | 999 |
2 | 4 | d | 2 | 2 |
2 | 5 | e | 2 | 2 |
2 | 6 | f | . | 999 |
2 | 7 | g | . | 999 |
2 | 8 | h | 0 | 0 |
3 | 1 | a | . | . |
3 | 2 | b | 1 | 1 |
4 | 1 | a | 0 | 0 |
4 | 2 | b | . | . |
data have;
infile datalines dsd dlm=" " truncover;
input ID $ Sequence section $ value;
datalines;
1 1 a 0
1 2 b
1 3 c 1
1 4 d 0
1 5 e 0
2 1 a 1
2 2 b 1
2 3 c
2 4 d 2
2 5 e 2
2 6 f
2 7 g
2 8 h 0
3 1 a .
3 2 b 1
4 1 a 0
4 2 b
;
run;
data want;
do until(last.value);
set have;
by id value notsorted;
if first.id then first=1;
if last.id then last=1;
end;
do until(last.value);
set have;
by id value notsorted;
desired=value;
if not first and not last and missing(value) then desired=999;
output;
end;
drop first last;
run;
Try this:
data want;
counter = 0;
do until (last.id);
set have;
by id;
counter + 1;
if value ne .
then do;
lastcount = counter;
if firstcount = . then firstcount = counter;
end;
end;
counter = 0;
do until (last.id);
set have;
by id;
counter + 1;
if value = . and counter gt firstcount and counter lt lastcount
then desired = 999;
else desired = value;
output;
end;
drop counter firstcount lastcount;
run;
Try this:
data want;
counter = 0;
do until (last.id);
set have;
by id;
counter + 1;
if value ne .
then do;
lastcount = counter;
if firstcount = . then firstcount = counter;
end;
end;
counter = 0;
do until (last.id);
set have;
by id;
counter + 1;
if value = . and counter gt firstcount and counter lt lastcount
then desired = 999;
else desired = value;
output;
end;
drop counter firstcount lastcount;
run;
May I suggest that instead of assigning a number to these, you assign a missing value that has a specific meaning, such as .A and so now the other missings can be distinguished from the .A
If you assign a number, then any further analysis or reporting (such as computing means or a percent) has to specially handle that number differently than the other numbers; not so if you use .A
I don't see anything wrong. After running my code. I got this :
where is wrong ?
PS If you want to treat zero as a "blank value", you need to include that in the conditions.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
ANOVA, or Analysis Of Variance, is used to compare the averages or means of two or more populations to better understand how they differ. Watch this tutorial for more.
Find more tutorials on the SAS Users YouTube channel.