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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.