BookmarkSubscribeRSS Feed
enginemane44
Calcite | Level 5
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
4 REPLIES 4
SPR
Quartz | Level 8 SPR
Quartz | Level 8
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
Rhodochrosite | Level 12
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
Super User
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
Calcite | Level 5
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

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
  • 4 replies
  • 1582 views
  • 0 likes
  • 4 in conversation