Lapis Lazuli | Level 10

## create new variables from substring

Hello all,

I have a SAS data set has a variable names 'string' its value looks like:  "a1|a2|a3|a4", "a1|a2",  "a1",   "a1|a2|a3"      (some obs has less '|' and some has more "|") ;

I want to create new variables  x1=a1,x2=a2,x3=a3........ but it does not work,

Thanks!

Here is my code:

data have;

string= "a1|a2|a3|a4"; output;

string= "a1|a2"; output;

string= "a1|a2|a3"; output;

run;

data want;

length x1 x2 x3 x4 x5 \$200 ;

set have;

do i=1 to countw(string,"|")

;

/*call symput('x'||left(_i_), scan(string,i,"|"));*/

x%trim(%left(i))=scan(string,i,"|");

end;

run;

5 REPLIES 5
Opal | Level 21

## Re: create new variables from substring

You're picturing that macro language can work on DATA step variables, but that's not really the case.  For example, try:

%let x = %left(i);

%put *&x*;

You'll see that %LEFT is left-hand justifying the character "i", and has nothing to do with the value of a DATA step variable.

Try setting up an array:

array x {5} \$200;

Then inside the DO loop you can use:

x{i} = scan(string, i, "|");

Barite | Level 11

## Re: create new variables from substring

Hello,

One solution:

data want;

length x1 x2 x3 x4 x5 \$200 ;

set have;

array x{*} x1 x2 x3 x4 x5;

do i=1 to dim(x) while (missing(scan(string,i,"|"))=0);

x{i}=scan(string,i,"|");

end;

run;

Super User

## Re: create new variables from substring

You're in a data step, don't use macro functions.

Create an array to hold your new variables.

Use the scan function to populate the variables.

Onyx | Level 15

## Re: create new variables from substring

You already have some good answers, below example has been presented many time by @data_null__:

``````data want;
infile cards dsd truncover  dlm='|';

if _n_=1 then
input @@;
set have;
_infile_=string;
length x1-x5 \$200;
input @1 x1-x5 @@;
cards;
neccesary evil
;
run;
``````

Super User

## Re: create new variables from substring

No. The first I saw this mixed code is Tom, not John King(data _null_) .
Discussion stats
• 5 replies
• 1954 views
• 2 likes
• 6 in conversation