how to balance the number of values of a variable

[fama]

Hi all,

I am working with sas and I have a question. I will try to explain the question properly:

I really appreciate your help and suggestions.

There is a variable in my table that have 5 phases for each observation. Some of the observations such as OBS 1 covers all 5 phases, however some of them do not. For instance obs 2 does not cover phases 2 and 4. I want to alter the table and create the phases( all 5 phrases) for all obs with the value of 0.

Current tabel                                                       wanted table

obs       Var          value                           obs       Var     value

1             1             10                             1             1          10

1             2             20                              1            2           20

1             3             12                              1            3           12

1             4             11                              1            4           11

1             5             10                              1            5           10

2             1             12                              2           1             12

2             3             23                              2           2              0

2             5             23                              2           3              23

                                                                2           4              0

                                                                2           5             23

3             1             21                              3          1                21

3             3              11                             3           2                0

3            4              50                             3           3                11

                                                                3           4              50

                                                                3           5               0

[Ksharp · Accepted solution]

data have;
input obs       Var          value  ;
cards; 
1             1             10       
1             2             20            
1             3             12        
1             4             11        
1             5             10         
2             1             12                       
2             3             23                       
2             5             23              
3             1             21                    
3             3              11             
3            4              50  
;
run;
proc sql;
select a.*,coalesce(b.value,0) as value
 from ( 
  select *
   from (select distinct obs from have),(select distinct var from have))  as a
left join have as b
 on a.obs=b.obs and a.var=b.var ;
quit;

[fama]

Thanks a lot Ksharp,
The code worked well, although I need to figure out how it works 🙂