Update NewIDs based on Macro list IDs?

[ybz12003]

Hello,

I have a situation.   Day 1, I got a dataset 'Day1_macrolist.'   Day 2, I got another dataset 'Day2_macrolist.'   Day by day, the macrolist will be keep updating from tempID_1 to tempID_6.    First, I would like to create a FULL macro list based on daily updates.   Then, when I have a TempID in the dataset, I would like to assign the NewID to the TempID based on the macro list ID I have create.   Is there an way to approach this?  Thank you.

data day1_macrolist;
      infile datalines dsd;
  input NewID : $15. TempID_1 : $15.  TempID_2 : $15. TempID_3 : $15. TempID_4 : $15.;
datalines;
1156IL, 8946, 8853, , , 
1487KM, 8956, 7852, , ,
000597, 1596, 4113, , ,
C1156, 4986, 0008, , ,
208, 8MO23, , , ,
21156, 8966, 8852, 5559, , 
;

data day2_macrolist;
      infile datalines dsd;
  input NewID : $15. TempID_1 : $15.  TempID_2 : $15. TempID_3 : $15. TempID_4 : $15. TempID_5 : $15. TempID_6 : $15.;
datalines;
1156IL, 8946, 8853, 7559, , , , 
10235, 0546, 4113, , , , ,
C1156, 4286, 0008, 0009, 4623, 55555, 33333,
200, X1O23, UX689, , , , ,
208, 8MO23, UV689, , , , ,
579, HL2489, GJ627, , , , ,
21156, 8966, 8852, , , , , 
;


data Want;
      infile datalines dsd;
  input TempID : $15.  NewID : $15.;
datalines;
7852, 1156IL, 
4113, 000597, 
55555, C1156,
UX689, 200,
X1O23, 200,
UV689, 208,
;

[Shmuel]

It absolutely not clear how you derived the WANT table:

First row of want is: TempID=7852 and NewID=1156IL - but

in DAY1 table the tempID 7852 relate to newID 1487KM

Please explain what is the logic and algorithm to derive what you want.

[ybz12003]

I apologized that I didn't input the sample data correctly in the morning.
The TempID_1 - TempID6 should be unique and not duplicate, so does the NewIDs.
Based on the day1, if the tempID is 7852 then the newID is 1487KM.

You could change the TempID to 8853 in row 1 of dataset 'Want.'

[Patrick · Accepted solution]

Below should give you some guidance how to implement.

/* macro definition for maintaining a main macro list
    - for real usage &ouds must be a permanent table and not work
*/
%macro maintain_macroList(
  inds,
  outds=work.macro_list
  );

  %if %sysfunc(exist(&outds, DATA)) ne 1 %then
    %do; 
      /* Create empty table structure */
      data &outds;
        stop;
        length TempID $15 NewID $15;
        call missing(of _all_);
      run;
    %end;

  /* transpose source data to narrow table structure */
  data _tmp_mlist(keep=NewID TempId);
    set &inds;
      array _TempID {*} TempID_:;
    do _i=1 to dim(_TempID) while(not missing(_TempID[_i]));
      TempId=_TempID[_i];
      output;
    end;
  run;
  proc sort data=_tmp_mlist nodupkey;
    by TempID;
  run;

  /* add new tempID's to target table */
  data &outds;
    merge macro_list _tmp_mlist;
    by TempID;
  run;

  /* clean-up: delete intermediary work table */
  proc datasets lib=work nolist nowarn;
    delete _tmp_mlist;
    run;
  quit;
%mend;

/* day 1 macro list maintenance */
data day1_macrolist;
  infile datalines dsd;
  input NewID : $15. TempID_1 : $15.  TempID_2 : $15. TempID_3 : $15. TempID_4 : $15.;
  datalines;
1156IL, 8946, 8853, , , 
1487KM, 8956, 7852, , ,
000597, 1596, 4113, , ,
C1156, 4986, 0008, , ,
208, 8MO23, , , ,
21156, 8966, 8852, 5559, , 
;

%maintain_macroList(day1_macrolist)

/* day 2 macro list maintenance */
data day2_macrolist;
  infile datalines dsd;
  input NewID : $15. TempID_1 : $15.  TempID_2 : $15. TempID_3 : $15. TempID_4 : $15. TempID_5 : $15. TempID_6 : $15.;
  datalines;
1156IL, 8946, 8853, 7559, , , , 
10235, 0546, 4113, , , , ,
C1156, 4286, 0008, 0009, 4623, 55555, 33333,
200, X1O23, UX689, , , , ,
208, 8MO23, UV689, , , , ,
579, HL2489, GJ627, , , , ,
21156, 8966, 8852, , , , , 
;

%maintain_macroList(day2_macrolist)


/* add NewID to source table */
data have;
  infile datalines dsd;
  input TempID : $15.;
  datalines;
7852 
4113
55555
UX689
X1O23
UV689
;

data want;
  if _n_=1 then
    do;
      if 0 then set work.macro_list;
      dcl hash h1(dataset:'work.macro_list');
      h1.defineKey('TempID');
      h1.defineData('NewID');
      h1.defineDone();
    end;
  set have;
  h1.find();
run;

[ybz12003]

Thank you so much!