topic Re: Insert missing ID from list in SAS Programming

Insert missing ID from list

TrueTears — Mon, 25 Nov 2019 20:15:05 GMT

Say I have the following excerpt dataset:

data have;
     input year  ID $ shock;
     cards;
     2000  1001  1
     2001  1001  1
     2002  1001  1
     2003  1001  1
     2004  1001  1
     2000  1003  1
     2001  1003  1
     2002  1003  1
     2003  1003  1
     2004  1003  1
     2000  1007  1
     2001  1007  1
     2002  1007  1
     2003  1007  1
     2004  1007  1
     2000  1010  1
     2001  1010  1
     2002  1010  1
     2003  1010  1
     2004  1010  1	 
;
run;

The year is always from 2000 to 2004. Shock always takes a value of 1 in this dataset. Now I have a list of IDs as follows:

data list;
     input  ID;
     cards;
1001
1003
1004
1005
1007
1009
1010 
;
run;

As you can see, ID 1004, 1005, and 1009 are missing from the original dataset. What I wish to do is obtain the following dataset:

data want;
     input year  ID $ shock;
     cards;
     2000  1001  1
     2001  1001  1
     2002  1001  1
     2003  1001  1
     2004  1001  1
     2000  1003  1
     2001  1003  1
     2002  1003  1
     2003  1003  1
     2004  1003  1
     2000  1004  0
     2001  1004  0
     2002  1004  0
     2003  1004  0
     2004  1004  0
     2000  1005  0
     2001  1005  0
     2002  1005  0
     2003  1005  0
     2004  1005  0
     2000  1007  1
     2001  1007  1
     2002  1007  1
     2003  1007  1
     2004  1007  1
     2000  1009  0
     2001  1009  0
     2002  1009  0
     2003  1009  0
     2004  1009  0
     2000  1010  1
     2001  1010  1
     2002  1010  1
     2003  1010  1
     2004  1010  1	 
;
run;

So that the missing IDs are inserted into the original dataset from year 2000 to 2004 but shock takes a value of 0 for these missing IDs.

Thank you.

Re: Insert missing ID from list

PeterClemmensen — Mon, 25 Nov 2019 20:24:14 GMT

One way

data have;
     input year  ID $ shock;
     cards;
     2000  1001  1
     2001  1001  1
     2002  1001  1
     2003  1001  1
     2004  1001  1
     2000  1003  1
     2001  1003  1
     2002  1003  1
     2003  1003  1
     2004  1003  1
     2000  1007  1
     2001  1007  1
     2002  1007  1
     2003  1007  1
     2004  1007  1
     2000  1010  1
     2001  1010  1
     2002  1010  1
     2003  1010  1
     2004  1010  1
;
run;

data list;
     input  ID $;
     cards;
1001
1003
1004
1005
1007
1009
1010
;
run;

data want;
   if _N_=1 then do;
      dcl hash h(dataset:'have');
      h.definekey('ID');
      h.definedone();
   end;

   set list;

   do year=2000 to 2004;
      shock=ifn(h.check(), 0, 1);
      output;
   end;
run;

Result:

ID   year shock 
1001 2000 1 
1001 2001 1 
1001 2002 1 
1001 2003 1 
1001 2004 1 
1003 2000 1 
1003 2001 1 
1003 2002 1 
1003 2003 1 
1003 2004 1 
1004 2000 0 
1004 2001 0 
1004 2002 0 
1004 2003 0 
1004 2004 0 
1005 2000 0 
1005 2001 0 
1005 2002 0 
1005 2003 0 
1005 2004 0 
1007 2000 1 
1007 2001 1 
1007 2002 1 
1007 2003 1 
1007 2004 1 
1009 2000 0 
1009 2001 0 
1009 2002 0 
1009 2003 0 
1009 2004 0 
1010 2000 1 
1010 2001 1 
1010 2002 1 
1010 2003 1 
1010 2004 1

Re: Insert missing ID from list

novinosrin — Mon, 25 Nov 2019 20:53:51 GMT



data have;
     input year  ID $ shock;
     cards;
     2000  1001  1
     2001  1001  1
     2002  1001  1
     2003  1001  1
     2004  1001  1
     2000  1003  1
     2001  1003  1
     2002  1003  1
     2003  1003  1
     2004  1003  1
     2000  1007  1
     2001  1007  1
     2002  1007  1
     2003  1007  1
     2004  1007  1
     2000  1010  1
     2001  1010  1
     2002  1010  1
     2003  1010  1
     2004  1010  1
;
run;

data list;
     input  ID $;
     cards;
1001
1003
1004
1005
1007
1009
1010
;
run;


data want;
merge have(in=a) list(in=b);
by id;
if a then output;
else if b then do;
shock=0;
 do year=2000 to 2004;;
 output;
end;
end;
run;