topic Re: add zero for missing value in SAS Enterprise Guide

add zero for missing value

hk2013 — Fri, 11 May 2018 18:44:14 GMT

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

Re: add zero for missing value

TomKari — Fri, 11 May 2018 19:08:21 GMT

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;

Re: add zero for missing value

novinosrin — Fri, 11 May 2018 19:49:52 GMT

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;

Re: add zero for missing value

Astounding — Fri, 11 May 2018 20:00:15 GMT

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;