topic Re: Filling in gaps in sequential data in SAS Programming

Filling in gaps in sequential data

enginemane44 — Mon, 08 Nov 2010 20:59:15 GMT

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

Re: Filling in gaps in sequential data

SPR — Mon, 08 Nov 2010 22:14:00 GMT

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,

Re: Filling in gaps in sequential data

ArtC — Tue, 09 Nov 2010 02:17:39 GMT

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.

Re: Filling in gaps in sequential data

Ksharp — Tue, 09 Nov 2010 09:18:23 GMT

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

Re: Filling in gaps in sequential data

enginemane44 — Tue, 09 Nov 2010 15:30:14 GMT

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