completing missing weeks

Accepted Solution Solved
Reply
Contributor
Posts: 45
Accepted Solution

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
Respected Advisor
Posts: 3,799

Re: completing missing weeks

Posted in reply to starz4ever2007

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;

View solution in original post


All Replies
Respected Advisor
Posts: 3,156

Re: completing missing weeks

Posted in reply to starz4ever2007

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
Respected Advisor
Posts: 3,799

Re: completing missing weeks

Posted in reply to starz4ever2007

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 374 views
  • 2 likes
  • 3 in conversation