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
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.