Variable more than one delimiter

[Beecelal]

Hi All,

could you please help me. I have one Variable with two delimiters ( : and , )

Variable

test1:aaaa,test2:bbbb,test3:ccc

test2:ddd,test1:ee25,test3:ffffff

test3:gg,test1:h,test2:jj12

I want to extract the Variables test1 to test3 with the Values.

test1	test2	test3
aaaa	bbbb	ccc
ee25	ddd	ffffff
h	jj12	gg

Thanks in advance.

[PaigeMiller]

The SCAN function allows you to separate "words" using multiple delimiters.

Are the words TEST1 TEST2 TEST3 always present in every row, or could there be other words like TEST4 or GORILLA?

[Beecelal]

no some times in the variable just test1 and test 3 like that.

Variable 

......

test1;ll,test3:p12

There is no words "test2"

but I want to use always the words test1, test2, and test3 the new Variable names

[Tom · Accepted solution]

Looks like you have name/value pairs.  So generate a data structure that reflects that.

data tall ;
  set have ;
  do index=1 by 1 until(index>= countw(variable,','));
    length name $32 value $200 ;
    value = scan(variable,index,'.');
    name=scan(value,1,':');
    value=scan(value,2,':');
    output;
  end;
run;

If you want to generate that flattened file then using PROC TRANSPOSE.  Let's assume you also have a variable named ID that uniquely identifies the rows of the original dataset.

proc transpose data=tall out=want(drop=_name_);
  by id ;
  var value;
  id name;
run;

[FreelanceReinh]

Hi @Beecelal,

You may want to try the "_infile_ trick":

data have;
input variable $50.;
cards;
test1:aaaa,test2:bbbb,test3:ccc
test2:ddd,test1:ee25,test3:ffffff
test3:gg,test1:h,test2:jj12
test1:ll,test3:p12
;

data want(keep=test:);
set have;
infile sasautos(verify.sas);
if _n_=1 then input @;
_infile_=translate(variable,'=',':,');
input @1 (test1-test3)(= :$10.) @@; /* increase length 10 if insufficient */
run;