topic Re: create new variables from substring in SAS Procedures

create new variables from substring

GeorgeSAS — Thu, 24 Mar 2016 14:22:41 GMT

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,

please help.

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;

Re: create new variables from substring

Astounding — Thu, 24 Mar 2016 14:28:48 GMT

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, "|");

Re: create new variables from substring

Loko — Thu, 24 Mar 2016 14:28:55 GMT

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;

Re: create new variables from substring

Reeza — Thu, 24 Mar 2016 14:30:14 GMT

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.

Re: create new variables from substring

Haikuo — Thu, 24 Mar 2016 15:41:47 GMT

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;

Re: create new variables from substring

Ksharp — Fri, 25 Mar 2016 01:46:57 GMT

No. The first I saw this mixed code is Tom, not John King(data _null_) .