How to map text values among columns?

[binhle50]

Hi all

I would highly appreciate if anyone could help me to map each procedure with its date as below:

I have 3 variables ID; Date; and Procedure as shown below (where the date variable contains multiple dates separated by "||" and the procedure variable also contains multiple procedures separated by "||" The order of the date will map with the order of the procedure)

ID                            Date                                                                                            Procedure

1000   2010-10-13 || 2010-03-10 || 2004-04-01 || 2002-01-01 || 1990-01-01      NA || Orthopedic || NA || NA ||  Orthopedic

I want (I just want to map out each orthopedic to each date and ignore everything else):

Id             date                Procedure

1000     2010-03-10       Orthopedic

1000     1990-01-01       Orthopedic

Hope someone can help!

Best,

Le

[average_joe · Accepted solution]

Here you go. The DO loop doesn't depend on a fixed number of pairs of dates and procedures, so each patient can have a varying number if that's the case.

data have;
infile cards;
input ID  _Dates :$100. _Procedures :$100.;
cards;
1000  2010-10-13||2010-03-10||2004-04-01||2002-01-01||1990-01-01  NA||Orthopedic||NA||NA||Orthopedic
;
run;

data want;
set have;
do _i = 1 by 1;
    _dt = scan(_dates, _i, '|');
    if missing(_dt) then leave; * reached the end of the list. exit loop;
    date = input(_dt, yymmdd10.);
    format date yymmdd10.;
    procdure = scan(_procedures, _i, '|');
    if procdure = 'Orthopedic' then output;
end; 
drop _:;
run;

[binhle50]

Thanks so much! It seems working great. Yes the pairs are not fixed, some records have no pairs but some may have 10 pairs and the date sometimes missing months or days or both months and days.
Anyway, I think this is a great solution.

Again, thanks so much for your help!

Best,

Le

[andreas_lds]

Naming a variable "Proc" can cause confusion, so i changed it to "method".

Thanks @average_joe for providing data in a useful form.

data want(keep= ID Method Date);
   set have;
   
   length Method $ 30 Date 8;
   format Date yymmddd10.;
   
   d = countw(Dates, '|');
   p = countw(Procedures, '|');
   
   if d ^= p then do;
      put 'ERROR: Number of values in Dates/Procedures is not the same.';
      stop;
   end;
   
   do i = 1 to p;
      Method = scan(Procedures, i, '|');
      if Method = 'Orthopedic' then do;
         Date = input(scan(Dates, i, '|'), yymmdd10.);
         output;
       end;
   end;
run;

I would inquire, why the data was created in such an ugly and useless structure in the first place.

[binhle50]

Thanks so much for your great solution. I will try and let you know. The data is actually not in that structure. each method will be asked separately and has its own field. I tried to combine all fields together (around 20 fields) into a single fields using || as a separator.

Best,

Le

[andreas_lds]

---

@binhle50 wrote:
Thanks so much for your great solution. I will try and let you know. The data is actually not in that structure. each method will be asked separately and has its own field. I tried to combine all fields together (around 20 fields) into a single fields using || as a separator.

Best,

Le

---

Sounds like it could have been easier to use the original structure.