topic Re: Variable more than one delimiter in SAS Programming

Variable more than one delimiter

Beecelal — Tue, 25 May 2021 13:59:59 GMT

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.

Re: Variable more than one delimiter

PaigeMiller — Tue, 25 May 2021 14:07:31 GMT

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?

Re: Variable more than one delimiter

Beecelal — Tue, 25 May 2021 14:16:16 GMT

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

Re: Variable more than one delimiter

Tom — Tue, 25 May 2021 14:16:44 GMT

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;

Re: Variable more than one delimiter

FreelanceReinh — Tue, 25 May 2021 14:43:21 GMT

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;