Extracting data from several columns to a column using separate dataset

[orchid_sugar]

Dataset A has total of 51 numeric and character variables: facility_ID (num), procedure_code0 - procedure_code 24 (char), procedure_date0 - procedure_date24 (num).

Dataset B has only 2 character variables: code (which is same as procedure_code0... not every value in procedure_code0 etc is in the dataset B 'code') and another_code (I need to translate to).

My goal is to extract the procedure_code and procedure_date based on the dataset B, so the final dataset will only have 3 columns/variables: facility ID, procedure_code_extracted, procedure_date_extracted.

My pseudocode came out as below:

DATA extracted;

SET dataset A;

ARRAY procedure_Code (25) procedure_code0 - procedure_code 24;

DO i = 1 To 25;

   IF procedure_Code(i) = database B's code THEN procedure_code_extracted = procedureCode(i)

              also get the location of that matched procedureCode(i) [row, column]

              use that location to find procedure_date from procedure_date0 - procedure_date24 and input under procedure_date_extracted

              if there is several matches in a row, make a new line for the subsequent match and repeat

End;

I am not sure how to make this logic into actual running SAS code.

[PaigeMiller]

Could you please provide small example data sets — let's say 2 facilities and 3 procedure_codes and 3 procedure_dates in data set A, and the corresponding data set B? We would also need to see the desired output from this small example data.

[orchid_sugar]

Please see attached as an example: 

*patient_id is not unique, so patient 1 from facility 123 is not the same as the patient 1 from facility 234.

[PaigeMiller]

---

@orchid_sugar wrote:

Please see attached as an example: 

*patient_id is not unique, so patient 1 from facility 123 is not the same as the patient 1 from facility 234.

---

I think @Tom has the answer ... but if that's not the right answer, please understand that we cannot use data in Excel. In addition, downloading Excel files (or any Microsoft Office files) is a security threat, so many people will not download Excel files. Please type (a portion of) the data into your reply.

[Tom · Accepted solution]

Sounds like you want to transpose A and then join/merge it with B.

data a_wide;
  set a;
  array p procedure_code0-procedure_code24;
  array d procedure_date0-procedure_date24;
  do index=1 to dim(p);
     procedure_code = p[index];
     procedure_date = d[index];
     if not missing(procedure_code) then output;
  end;
  drop procedure_code0-procedure_code24 procedure_date0-procedure_date24;
run;

proc sort;
  by procedure_code;
run;

data want;
  merge a_wide (in=in1) b(in=in2);
  by procedure_code;
  if in1 and in2;
run;

If you wanted to use your proposed DO loop instead then I would suggest converting B into a FORMAT that converts procedure_code into the value of the other variable in B.