Filling in gaps in sequential data

[enginemane44]

SAS-L'ers
I have an interesting data step problem which should be easy, but whose solution is escaping me. Essentially, I have a data set with gaps in it :

QNUM Rating
1 3.0
2 2.9
3 10.0
5 7.7
6 8.9
7 9.1
9 10.0
10 7.6
1 9.8
2 7.0
3 6.3
4 4.9
5 5.5
7 6.3
8 8.9
9 9.9
10 7.9
etc.
There are ten questions total. What I'm looking for is code to 'fill in the gaps' with ratings set to missing :
QNUM Rating
1 3.0
2 2.9
3 10.0
4 . <-- filled in missing Q4
5 7.7
6 8.9
7 9.1
8 . <-- filled in missing Q8
9 10.0
10 7.6

I've tried all kinds of counters and flags/DO loops, etc. to no avail. Anybody have any hints ?

Barry Walton

[SPR]

Hello Enginemane44,

It looks like your input dataset should contain an additional variable, say ID, to distinguish two sets of data. Anyway, to solve the problem for the first set of questions you can use the following code:

data a;
input QNUM Rating;
datalines;
1 3.0
2 2.9
3 10.0
5 7.7
6 8.9
7 9.1
9 10.0
10 7.6
;
run;
data t;
do qnum=1 to 10;
output;
end;
run;
data r;
merge a(in=a) t(in=t);
if a and t then output;
if not a and t then output;
by qnum;
run;

Sincerely,

[ArtC]

If the data are not strictly sequential or if the list of possible QNUMs is not well specified, a double transpose using QNUM as the ID variable will also fill in the gaps.

[Ksharp]

Hi.I think Patrick can do it very well.
[pre]
data temp;
input num rate;;
cards;
1 3.0
2 2.9
3 10.0
5 7.7
6 8.9
7 9.1
9 10.0
10 7.6
1 9.8
2 7.0
3 6.3
4 4.9
5 5.5
7 6.3
8 8.9
9 9.9
10 7.9
;
run;
data result(drop=num rate);
set temp;
qnum+1;
do while(qnum lt num) ;
call missing(rating);
output;
qnum+1;
end;
rating=rate;
output;
if qnum eq 10 then qnum=0;
run;
proc print noobs;run;

[/pre]
Ksharp

[enginemane44]

Hi all,
Ksharp was right on - I tried the code and it worked perfectly. Thanks to Ksharp and all the others who responded. I suspected a merge may be an alternate way to do it, but I wasn't sure what to merge the data with.
Barry Walton