## how to balance the number of values of a variable

Solved
Occasional Contributor
Posts: 12

# 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;``````

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
☑ This topic is solved.

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