topic Re: Concatenate columns in ALTER statement in PROC SQL in SAS Programming

Concatenate columns in ALTER statement in PROC SQL

Saurabh_Rana — Thu, 03 Dec 2020 11:55:04 GMT

How can I concatenate multiple columns in PROC SQL statement using ALTER? And I don't want to use SELECT statement since in that case, I will have to mention all the columns and the table on which I am working has more than 100 columns whereas in the ALTER statement you just need to mention the column you are adding.

Re: Concatenate columns in ALTER statement in PROC SQL

Kurt_Bremser — Thu, 03 Dec 2020 12:30:41 GMT

The ALTER TABLE statement can be used to just add a column, but it will initialize it with missing values. You need to also use an UPDATE that sets the values as needed.

I would rather use a DATA step to do this (add a column and use concatenation of values to populate it).

Re: Concatenate columns in ALTER statement in PROC SQL

Saurabh_Rana — Thu, 03 Dec 2020 12:50:22 GMT

But with the data step, I will only get the concatenated column in output table but I want the concatenated column along with the other 100 columns. What is the best way to achieve this output?

Re: Concatenate columns in ALTER statement in PROC SQL

Kurt_Bremser — Thu, 03 Dec 2020 13:07:03 GMT

You will get ALL variables, unless you drop them:

data have;
input x $ y $;
datalines;
a b
;

data want;
set have;
z = cats(x,y);
run;

proc print data=want noobs;
run;

Result:

  x    y    z

  a    b    ab