Programming the statistical procedures from SAS

combining two columns

Accepted Solution Solved
Reply
Frequent Contributor
Frequent Contributor
Posts: 79
Accepted Solution

combining two columns

Dear all,

I have the following dataset

IDREGIONNUMTYPE
NYus;ne1015; 10x16
BOus;ne1015; 10x17
IDnonus1018

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

IDREGIONNUMTYPEREGION_NUMTYPE
NYus;ne1015; 10x16us1015; us10x16;ne1015;ne10x16
BOus;ne1015; 10x17us1015; us10x17;ne1015;ne10x17
IDnonus1018nonus1018

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


Accepted Solutions
Solution
‎04-28-2014 09:03 PM
Respected Advisor
Posts: 3,124

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

View solution in original post


All Replies
Solution
‎04-28-2014 09:03 PM
Respected Advisor
Posts: 3,124

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
Frequent Contributor
Posts: 79

Re: combining two columns

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 168 views
  • 0 likes
  • 2 in conversation