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;
Use a wildcard and the OF keyword:
data want;
set t2004;
length revenue_new $20;
revenue_new = catx(',',of revenue:);
run;
you may try
Revenue_new=CATX(',', of Revenue1--Revenue4);
It is not good because you don't know in advance that there are 4 Revenue columns.
This is exactly the issue.
ok then try this
Data t2204_new;
length Revenue_new $200.;
set t2004;
Revenue_new=CATX(',',of Revenue:);
Run;
Use a wildcard and the OF keyword:
data want;
set t2004;
length revenue_new $20;
revenue_new = catx(',',of revenue:);
run;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: