BookmarkSubscribeRSS Feed
hk2013
Fluorite | Level 6

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

3 REPLIES 3
TomKari
Onyx | Level 15

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;
novinosrin
Tourmaline | Level 20
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;
Astounding
PROC Star

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 697 views
  • 0 likes
  • 4 in conversation