combining two columns

[hdg]

Dear all,

I have the following dataset

ID	REGION	NUMTYPE
NY	us;ne	1015; 10x16
BO	us;ne	1015; 10x17
ID	nonus	1018

I would like to add another column "REGION_NUMTYPE" which has all the combinations of region and num type per row separated by the ";"

ID	REGION	NUMTYPE	REGION_NUMTYPE
NY	us;ne	1015; 10x16	us1015; us10x16;ne1015;ne10x16
BO	us;ne	1015; 10x17	us1015; us10x17;ne1015;ne10x17
ID	nonus	1018	nonus1018

Is there a simple way to do this ? Many Thanks for your help

[Haikuo · Accepted solution]

You can also trying using Hash to be more dynamic.

data have;

input ID :$      REGION :$20.      NUMTYPE :$&20.;

cards4;;;;

NY    us;ne 1015; 10x16

BO    us;ne 1015; 10x17

ID    nonus 1018

;;;;

data want;

  set have;

  length region_numtype $ 100;

    array re(10) /*arbituary dimension*/ $10 _temporary_;

      array num(10) $10 _temporary_;

do i=1 by 1 while (not missing (scan(region,i,';')));

     re(i)=scan(region,i,';');

         do j=1 by 1 while (not missing (scan(numtype,j,';')));

num(j)=scan(numtype,j,';');

             region_numtype=catx(';',region_numtype,cats(re(i),num(j)));

            end;

  end;

  drop i j;

  run;

Haikuo

[hdg]

Thanks a lot Haikuo. super efficient really cut down my processing time.