topic Re: concatenate columns in SAS Programming

concatenate columns

Ronein — Thu, 16 Jul 2020 07:30:10 GMT

Hello

I have the following question.

In each month I get a data set that is sent to me (I don't create it).

The structure of the table is not fixed and there are different columns every month.

For example:

In month June 2020 there are 3 columns of revenue called: Revenue1,Revenue2,Revenue3

In month May 2020 there are 2 columns of revenue called: Revenue1,Revenue2

In month April 2020 there are 2 columns of revenue called: Revenue1,Revenue2,Revenue3,Revenue4

In every month after getting the data set I need to perform manipulation on the data set.

Let's say that I want to concatenate all variables that have following name "Revenue"(with comma between values)

What is the way to concatenate the columns with name Revenue when I don't know in advance how many columns with these name have.

I am looking for a dynamic code that concatenate fields when number of fields is not fixed.

Data t2004;
input Id Revenue1 Revenue2 Revenue3 Revenue4;
cards;
1 10 20 30 40
2 15 30 45 60
3 5 10 15 20
;
Run;

Data t2204_new;
set t2004;
Revenue_new=CATX(',',Revenue1,Revenue2,Revenue3,Revenue4);
/*need to find way to do it without knowing number of columns before*/
Run;

Re: concatenate columns

Jagadishkatam — Thu, 16 Jul 2020 07:53:37 GMT

you may try

Revenue_new=CATX(',', of Revenue1--Revenue4);

Re: concatenate columns

Kurt_Bremser — Thu, 16 Jul 2020 07:53:23 GMT

Use a wildcard and the OF keyword:

data want;
set t2004;
length revenue_new $20;
revenue_new = catx(',',of revenue:);
run;

Re: concatenate columns

Ronein — Thu, 16 Jul 2020 07:59:56 GMT

It is not good because you don't know in advance that there are 4 Revenue columns.

This is exactly the issue.

Re: concatenate columns

Jagadishkatam — Thu, 16 Jul 2020 08:02:47 GMT

ok then try this

Data t2204_new;
length Revenue_new $200.;
set t2004;
Revenue_new=CATX(',',of Revenue:);

Run;