topic Re: How to map text values among columns? in SAS Programming

How to map text values among columns?

binhle50 — Mon, 25 Apr 2022 17:46:28 GMT

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,

Re: How to map text values among columns?

average_joe — Tue, 26 Apr 2022 00:07:43 GMT

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;

Re: How to map text values among columns?

andreas_lds — Tue, 26 Apr 2022 06:23:04 GMT

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.

Re: How to map text values among columns?

binhle50 — Tue, 26 Apr 2022 13:12:37 GMT

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

Re: How to map text values among columns?

binhle50 — Tue, 26 Apr 2022 13:20:02 GMT

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

Re: How to map text values among columns?

andreas_lds — Fri, 29 Apr 2022 05:09:31 GMT

@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.