I have a data set that has ID, Metric, score and month but i am noticing some the ids data for month here or there for certain metrics
what i have:
id Metric month score
1 a 1 .9
1 b 1 .9
1 c 1 .8
1 d 1 .8
1 a 2 .7
1 c 2 .8
1 d 2 .8
1 b 3 .9
1 c 3 .6
1 d 3 .6
out put i want
id Metric month score
1 a 1 .9
1 b 1 .9
1 c 1 .8
1 d 1 .8
1 a 2 .7
1 b 2 0
1 c 2 .8
1 d 2 .8
1 a 3 0
1 b 3 .9
1 c 3 .6
1 d 3 .6
Easy-peasy.
Create a table of all the possible ID/Metric/Month combinations, and then left-join it.
Tom
data Have;
length ID 8 Metric $1 Month 8 Score 8;
input ID Metric Month Score;
cards;
1 a 1 .9
1 b 1 .9
1 c 1 .8
1 d 1 .8
1 a 2 .7
1 c 2 .8
1 d 2 .8
1 b 3 .9
1 c 3 .6
1 d 3 .6
run;
data AllRecs;
length Metric $1;
Score = 0;
do ID = 1 to 10;
do Metric = "a", "b", "c", "d";
do Month = 1 to 12;
output;
end;
end;
end;
run;
proc sql noprint;
create table want as
select a.ID, a.Metric, a.Month,
case
when h.Score is missing then 0
else h.Score
end
from AllRecs a left join Have h on a.ID = h.ID and a.Metric = h.Metric and a.Month = h.Month;
quit;
data have;
input id Metric $ month score;
cards;
1 a 1 .9
1 b 1 .9
1 c 1 .8
1 d 1 .8
1 a 2 .7
1 c 2 .8
1 d 2 .8
1 b 3 .9
1 c 3 .6
1 d 3 .6
;
proc sort data=have out=temp(keep=id month) nodupkey;
by id month;
data want;
if _n_=1 then do;
dcl hash H (dataset:'have',multidata:'y',ordered: "A") ;
h.definekey ("id",'month','metric') ;
h.definedata ('score') ;
h.definedone () ;
end;
set temp;
by id month;
do _t=97 to 100;
metric=byte(_t);
if h.find() ne 0 then score=0;
output;
end;
drop _:;
run;
SQL might be the easiest way to create a shell with the complete structure in it:
proc sql noprint;
create table id_list as select distinct id from have;
create table metric_list as select distinct metric from have;
create table month_list as select distinct month from have;
create table all_values as select * from id_list, metric_list month_list
orderby id metric month;
quit;
Then fill in the blanks:
data want;
merge all_values have (in=have_it);
by id metric month;
if have_it=0 then score = 0;
run;
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.
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.