Solved
Contributor
Posts: 45

completing missing weeks

dataset:

subj            week         q1         q2        q3

1                  1               1           3          5

1                  2               2          2          1

1                  3               4           3          .

1                  4               2           0          5

2                  1               2           1          5

2                  3               2           3          5

2                  4               7          3          4

3                  2               2           3          5

3                  4               2           4          5

3                  5               2           .         5

I would like each subject to have a value in the "week" variable from 1 to 5 (as you can see subj 1 is missing 5, subj 2 is missing 2 and 5 and subj 3 is missing 1 and 3. And for those weeks that are missing, i would like q1 q2 q3 to have .

so data should look like:

subj            week         q1         q2        q3

1                  1               1           3          5

1                  2               2          2          1

1                  3               4           3          .

1                  4               2           0          5

1                  5               .          .          .

2                  1               2           1          5

2                  2               .           .          .

2                  3               2           3          5

2                  4               7          3          4

Accepted Solutions
Solution
‎08-11-2016 11:51 AM
Posts: 3,852

Re: completing missing weeks

The features of proc summary make this pretty easy.

``````data have;
infile cards expandtabs;
input subj week q1 q2 q3;
cards4;
1 1 1 3 5
1 2 2 2 1
1 3 4 3 .
1 4 2 0 5
2 1 2 1 5
2 3 2 3 5
2 4 7 3 4
3 2 2 3 5
3 4 2 4 5
3 5 2 . 5
;;;;
run;
proc print;
run;
proc summary data=have nway;
class week;
output out=classdata(drop=_:);
run;
proc print;
run;
proc summary data=have nway classdata=classdata;
by subj;
class week;
output out=filled(drop=_type_) idgroup(out(q:)=);
run;
proc print;
run;``````

All Replies
Posts: 3,167

Re: completing missing weeks

Not the most efficient,

``````data have;
input subj            week         q1         q2        q3;
cards;
1                  1               1           3          5
1                  2               2          2          1
1                  3               4           3          .
1                  4               2           0          5
2                  1               2           1          5
2                  3               2           3          5
2                  4               7          3          4
3                  2               2           3          5
3                  4               2           4          5
3                  5               2           .         5
;

proc sql;
create table want as
select  b.subj as subj,b.week as week, a.q1,q2,q3
from have a
full join
(select * from (select distinct subj from have)  , (select distinct week from have)  ) b
on a.week = b.week and a.subj=b.subj
order by b.subj, b.week
;
quit;``````
Solution
‎08-11-2016 11:51 AM
Posts: 3,852

Re: completing missing weeks

The features of proc summary make this pretty easy.

``````data have;
infile cards expandtabs;
input subj week q1 q2 q3;
cards4;
1 1 1 3 5
1 2 2 2 1
1 3 4 3 .
1 4 2 0 5
2 1 2 1 5
2 3 2 3 5
2 4 7 3 4
3 2 2 3 5
3 4 2 4 5
3 5 2 . 5
;;;;
run;
proc print;
run;
proc summary data=have nway;
class week;
output out=classdata(drop=_:);
run;
proc print;
run;
proc summary data=have nway classdata=classdata;
by subj;
class week;
output out=filled(drop=_type_) idgroup(out(q:)=);
run;
proc print;
run;``````
☑ This topic is solved.