Extract number sequence from string

[Matos]

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.

[ballardw]

---

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

[Matos]

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

[mklangley · Accepted solution]

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

[Matos]

Absolutely! Thanks a lot for your help mklangley.