topic Re: Extract number sequence from string in New SAS User

Extract number sequence from string

Matos — Tue, 08 Sep 2020 19:42:00 GMT

Dear all,

I have a data set which contains a string variable specifying ranges of values. Some rows display the first and the last value of the range while others display a list of all possible values from the range, separated by a comma, like this:

Data have:

Var1

010-0100

020-050

010,025,048,061

Line by line, I would need to extract all possible values from the range to a different column, such as:

Data want:

Var1
010
020
030
040
050
060
070
080
090
0100
020
030
040
050
010
025
048
061

For ranges delimited by "-" the sequence increments always with a step of +10, e.g.: 010, 020, 030...

Can this transformation be achieved in SAS?

Many thanks.

Re: Extract number sequence from string

ballardw — Tue, 08 Sep 2020 19:48:04 GMT

@Matos wrote:

Dear all,

I have a data set which contains a string variable specifying ranges of values. Some rows display the first and the last value of the range while others display a list of all possible values from the range, separated by a comma, like this:

Data have:

Var1

010-0100

020-050

010,025,048,061

Line by line, I would need to extract all possible values from the range to a different column, such as:

Data want:

Var1

010

020

030

040

050

060

070

080

090

0100

020

030

040

050

010

025

048

061

For ranges delimited by "-" the sequence increments always with a step of +10, e.g.: 010, 020, 030...

Can this transformation be achieved in SAS?

Many thanks.

Yes but you need to answer a few questions to clarify what you actually want.

Are you wanting to create actual numeric values or character values?

Once your variable Var1 is created as character it will stay as such and numeric values would have be another variable.

Re: Extract number sequence from string

Matos — Tue, 08 Sep 2020 20:02:55 GMT

Sure I forgot to specify this point. The variable after transformation shall remain character.

Re: Extract number sequence from string

mklangley — Tue, 08 Sep 2020 21:27:35 GMT

@Matos Is this what you're looking for?

data have;
    input var1 & $100.;
    datalines;
    010-0100  
    020-050  
    010,025,048,061  
    ;
run;

data want (keep=var2);
    set have;
    if find(var1, '-') > 1
        then do;
            range_start = substr(var1, 1, find(var1,'-') - 1);
            range_end = substr(var1, find(var1,'-') + 1);
            increment = input(range_start, best8.) + 10;

            var2 = range_start;
            output;

            do while (increment < range_end);
                var2 = put(increment, z3.);
                output;
                increment + 10;
            end;

            var2 = range_end;
            output;
        end;
    else if find(var1, ',')
        then do;
            do i=1 to countw(var1,',');
               var2 = scan(var1,i,',');
               output;
            end;
        end;
run;

proc print; run;

Re: Extract number sequence from string

Matos — Wed, 09 Sep 2020 07:18:37 GMT

Absolutely! Thanks a lot for your help mklangley.