topic Re: Concatenating too many variables in SAS Programming

Concatenating too many variables

mlogan — Thu, 01 Mar 2018 20:07:15 GMT

Hi All,

I need to concatenate almost 100 variables and I don't want to put a comma (,) after each one of them as it is too much of work. My variables has a sequence and it follows the pattern like var1, var2, var5, var11......

Is there a way to concatenate all of them with a short code like new_Var=CAT(var1-var100)

Here is how my data looks like.

DATA Have;
INPUT VAR1 VAR2$ VAR5 VAR10$;
DATALINES;
107 ENG1 10 NY
105 Bio2 15 ON
101 Che1 10 TX
107 Bio1 11 DL
105 Eng2 15 CA
100 Phy2 11 MR
105 Che1 15 DC
;
RUN

Can someone help me please.

Thanks,

Re: Concatenating too many variables

Reeza — Thu, 01 Mar 2018 20:16:45 GMT

CATX and a variable list. This will include all varialbes that start with the prefix VAR:

Depending on your naming convention you may need other options, google "SAS 9.4 variable lists" to see all the different ways of using abbreviations in your variable lists.

want = catx(", ", of var:);

Note, this works only in a data step since SQL doesn't support variable lists.

Re: Concatenating too many variables

mlogan — Thu, 01 Mar 2018 21:12:22 GMT

Thanks Reza, this is so helpful. Do you think if there is a way to bring to concatenated data as separate line instead of side by side? Like for the first observation it will come like:
New_Var
107
ENG1
10
NY

Re: Concatenating too many variables

Reeza — Thu, 01 Mar 2018 21:14:55 GMT

It depends on the final output. SAS doesn't have a way of specifying line breaks in a variable that I know of...and I would probably leave that to my reporting step.

What is your final output destination? If it's HTML, perhaps a line break symbol may be needed, or if its RTF the appropriate tag would be needed.

Re: Concatenating too many variables

mlogan — Thu, 01 Mar 2018 21:27:18 GMT

Hi Reeza,
My final destination file is Excel. Thanks.

Re: Concatenating too many variables

Reeza — Thu, 01 Mar 2018 21:30:43 GMT

Then that's the ALT+ENTER key you need and you can probably add that in. It won't show in the cell, and I'm not 100% sure Excel would recognize it properly.

If you use ODS EXCEL you could go down the formula route and use CHAR(10) or "0A"x...

https://excel.tips.net/T002788_Simulating_Alt_Enter_in_a_Formula.html

Re: Concatenating too many variables

ChrisNZ — Thu, 01 Mar 2018 21:31:59 GMT

NEWVAR = cat(of VAR1-VAR100);

if you want to use the explicit list that you had.