how to balance the number of values of a variable

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 12
Accepted Solution

how to balance the number of values of a variable

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

 

 


Accepted Solutions
Solution
‎10-05-2017 05:16 AM
Super User
Posts: 10,787

Re: how to balance the number of values of a variable

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;

View solution in original post


All Replies
Solution
‎10-05-2017 05:16 AM
Super User
Posts: 10,787

Re: how to balance the number of values of a variable

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;
Occasional Contributor
Posts: 12

Re: how to balance the number of values of a variable

Thanks a lot Ksharp,
The code worked well, although I need to figure out how it works Smiley Happy
☑ This topic is solved.

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

Discussion stats
  • 2 replies
  • 163 views
  • 0 likes
  • 2 in conversation