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.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.