Naming columns based on calculations within row

Accepted Solution Solved
Reply
Contributor exj
Contributor
Posts: 20
Accepted Solution

Naming columns based on calculations within row

Hello,

Is there anybody who can help me with this kind of problem? I have only one row as a result (company). There is for example 10 columns. Each column has it's frequencies. Columns are named as Col1 - Col10. Col1 includes payments under 1 000€,... and Col10 under 100 000€. If the frequency is under 3 I want it to be included to the previous column and the column should be named with columns included. And it should start from the last column towards the first.


I don't know how to solve this problem with macros and arrays/loops. Any help will be appreciated!

Example:

Col1Col2Col3
522

Result should be

Col1Col2-Col3
54

Accepted Solutions
Solution
‎02-11-2013 11:52 AM
PROC Star
Posts: 7,363

Re: Naming columns based on calculations within row

Not sure why you would want to do this but, given that you said you only had one row, here is one way it could be accomplished which would create a file called 'want' that would look like:


col1   col2_col3       col4     col5      col6_col7_col8            col9_col10

  5          4                   4       5                 3                              7



data have;

  input col1-col10;

  cards;

5 2 2 4 5 1 1 1 6 1

;

filename sascode temp;

data _null_;

  array old(*) col1-col10;

  array combine(10) $50. ('col1' 'col2' 'col3' 'col4' 'col5'

                         'col6' 'col7' 'col8' 'col9' 'col10');

  file sascode;

  set have;

  do _n_=10 to 2 by -1;

    if old(_n_) lt 3 then do;

      old(_n_-1)=sum(old(_n_-1),old(_n_));

      call missing(old(_n_));

      combine(_n_-1)=catt(combine(_n_-1),'_',combine(_n_));

      call missing(combine(_n_));

    end;

  end;

  put 'data want;';

  do _n_=1 to 10;

    if not missing(combine(_n_)) then do;

      put combine(_n_) '=' old(_n_) ';';

    end;

  end;

  put 'run;';

run;

%include sascode;

View solution in original post


All Replies
Super User
Posts: 17,810

Re: Naming columns based on calculations within row

Can you post an example for more than one row? I don't understand how you'd implement such a method for multiple rows.

Solution
‎02-11-2013 11:52 AM
PROC Star
Posts: 7,363

Re: Naming columns based on calculations within row

Not sure why you would want to do this but, given that you said you only had one row, here is one way it could be accomplished which would create a file called 'want' that would look like:


col1   col2_col3       col4     col5      col6_col7_col8            col9_col10

  5          4                   4       5                 3                              7



data have;

  input col1-col10;

  cards;

5 2 2 4 5 1 1 1 6 1

;

filename sascode temp;

data _null_;

  array old(*) col1-col10;

  array combine(10) $50. ('col1' 'col2' 'col3' 'col4' 'col5'

                         'col6' 'col7' 'col8' 'col9' 'col10');

  file sascode;

  set have;

  do _n_=10 to 2 by -1;

    if old(_n_) lt 3 then do;

      old(_n_-1)=sum(old(_n_-1),old(_n_));

      call missing(old(_n_));

      combine(_n_-1)=catt(combine(_n_-1),'_',combine(_n_));

      call missing(combine(_n_));

    end;

  end;

  put 'data want;';

  do _n_=1 to 10;

    if not missing(combine(_n_)) then do;

      put combine(_n_) '=' old(_n_) ';';

    end;

  end;

  put 'run;';

run;

%include sascode;

Contributor exj
Contributor
Posts: 20

Re: Naming columns based on calculations within row

This is just the answer what I was waiting for. It works just how I wanted it to work and now I have to get more information about it's functioning. Thank you very much for your swift reply!

I am going to test it with my real data now but I think there will be no problems with it.

Contributor exj
Contributor
Posts: 20

Re: Naming columns based on calculations within row

Hello again,

Is it possible to rename columns like in your example col6_col8 instead of col6_col7_col8? How should it be done? I have actually 30 variables now and the variable name will be over 32 characters long almost in most cases.

Regards, exj

PROC Star
Posts: 7,363

Re: Naming columns based on calculations within row

How about:

data have;

  input col1-col10;

  cards;

5 2 2 4 5 1 1 1 6 1

;

filename sascode temp;

data _null_;

  array old(*) col1-col10;

  array combine(10) $50. ('col1' 'col2' 'col3' 'col4' 'col5'

                         'col6' 'col7' 'col8' 'col9' 'col10');

  file sascode;

  set have;

  do _n_=10 to 2 by -1;

    if old(_n_) lt 3 then do;

      old(_n_-1)=sum(old(_n_-1),old(_n_));

      call missing(old(_n_));

      combine(_n_-1)=catt(combine(_n_-1),'*',combine(_n_));

      call missing(combine(_n_));

    end;

  end;

  put 'data want;';

  do _n_=1 to 10;

    if not missing(combine(_n_)) then do;

      if count(combine(_n_),"*") gt 0 then

       combine(_n_)=catx("_",scan(combine(_n_),1,"*"),scan(combine(_n_),-1,"*"));

      put combine(_n_) '=' old(_n_) ';';

    end;

  end;

  put 'run;';

run;

%include sascode;

☑ This topic is SOLVED.

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

Discussion stats
  • 5 replies
  • 291 views
  • 1 like
  • 3 in conversation