Date:         Sat, 28 Jul 2007 09:10:28 -0400
Reply-To:     "Richard A. DeVenezia" <rdevenezia@WILDBLUE.NET>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Richard A. DeVenezia" <rdevenezia@WILDBLUE.NET>
Organization: Internet News Service
Subject:      Re: Question about updating ID's using something other than PROC
              SQL
Comments: To: sas-l@uga.edu

donnelly.ryan.m@gmail.com wrote:
> Happy Friday all,
>
> I'm working on a program to import a data set into SAS.  The records
> in the database cover a 12 month period and sometimes the ID's change
> mid way through the year so to the untrained eye the records would
> appear to be from two different entities.  In the past I've utilized
> PROC SQL (update data.sampdata set var ='011301' where var=
> '010053';), to change the old ID to the new ID number.  This method,
> however, creates a large amount of hand coding, something I don't want
> to continue for obvious reasons.
>
> Does anyone have any suggestions for a way to update the data set
> without having to hand code all the equivalent ID's?
>
> Thanks in advance for your help, enjoy the weekend and I appologize if
> I haven't been clear enough, I'm still a bit of a SAS n00b.
>
> Ryan

One way is to use a mapping table with columns oldid and newid.

-------------------------
data old;
  do id =1 to 100;
    output;
  end;
run;

data map;
  do oldid = 1 to 100;
    if ranuni(123) > 0.05 then continue;
    newid = 1e6 + oldid;
    output;
  end;
run;

proc sql;
  update old
  set id = (select newid from map where id=oldid)
  where
  id in (select oldid from map)
  ;
quit;

%let syslast = old;

-------------------------

A more complex solution is needed if the mapping has to go down a path. For
instance, suppose your map data was
old new
1    2
2    5
5    23
And you would want 1 to map to 23, not 2.
Any code dealing with a path needs to have checks or assurances that the map
data does not define cycle, wherein you would otherwise enter an infinite
loop.

One of the best constructs for dealing with paths is an associative array,
better know as a hash.

do while (myMappings.find() eq 0);
  id = newid;
end;


--
Richard A. DeVenezia
http://www.devenezia.com/