Separating variables

Anvesh Reddy 0
Viewed 44

I have below data in visit variable

Screening DayXX
CycleXX DayXX
CycleXX DayXX
CycleXX DayXX
CycleXX DayXX
CycleXX DayXX
CycleXX DayXX
CycleXX DayXX
CycleXX DayXX
Endofthetreatment DayXX

We have Cycles and days in the visit, now Sponsor asking populate only Cycle in the Visit variable without Screening and End of the study

sas
1 Answers

Use a where statement to only keep lines where the word "Cycle" is in it. You can then use the input, compress, and scan functions to select a specific string, keep only digits, and convert it to a number.

data have;
    infile datalines dlm='|';
    length visit $30.;
    input visit$;
    datalines;
Screening Day01
Cycle01 Day02
Cycle01 Day03
Cycle01 Day04
Cycle02 Day04
Cycle02 Day06
Cycle02 Day07
Cycle03 Day08
Cycle03 Day09
Endofthetreatment Day10
;
run;

data want;
    set have;
    where upcase(visit) contains 'CYCLE';

    /* Keep only digits from the first and second words and
       convert it to a number */
    cycle = input(compress(scan(visit, 1),, 'DK'), 2.);
    day   = input(compress(scan(visit, 2),, 'DK'), 2.);

    drop visit;
run;

Output:

cycle   day
1       2
1       3
1       4
2       4
2       6
2       7
3       8
3       9
Stu Sztukowski0
Related