## combining two columns

Solved
Frequent Contributor
Posts: 79

# combining two columns

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

Accepted Solutions
Solution
‎04-28-2014 09:03 PM
Posts: 3,157

## Re: combining two columns

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

All Replies
Solution
‎04-28-2014 09:03 PM
Posts: 3,157

## Re: combining two columns

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

Frequent Contributor
Posts: 79

## Re: combining two columns

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

🔒 This topic is solved and locked.