Dynamic Concatenation

[Asender]

Hi everybody.

I'm looking for a bit of code that will concatenate a series of variables that have been generated by a proc transpose.

i.e. Col1, Col2, Col3......Colx

The catch is that the number of variables to concatenate will change everytime the job is run.

I'm trying to remove the need to manually adjust the code each time.

Can anyone help?

Many thanks.

 

[klaz2002]

Asender,
You can do it many ways.
1) One way that you can go is use the sashelp.vcolumn dataset. (this method requires two datasteps)
Set the data and use a where statement. At the end of the data step you create a macro var that holds the number of vars that you need.
ex.
data test;
set sashelp.vcolumn end=last;
where trim(libname) = 'your_libname' and
trim(memname) = 'your_ds_name' and
upcase(substr(name,1,3)) = 'COL';
if last then
call symput('maxvar',trim(left(put(_n_,8.))) );
run;
The macro var MAXVAR is set to the number of vars that have col as their first 3 letters. (This is available after the datastep.
In the next datastep you can run a loop as follows:
data test;
length newstring $2000;
array allvars [*] col1-col&maxvar;
do i=1 to dim(allvars);
newstring = trim(newstring)||' '||allvars;
end;
run;

Another method is using the autocreate feature in the array statement. If you know that col1-colx are always all numeric vars or all char vars you can do the following.
ARRAY allvars [] _numeric_;
or
ARRAY allvars [] _character_;

You would use the same type of logic to put the new string together.
data test;
set your_ds;
length newstring $2000;
array allvars [*] _numeric_;
do i=1 to dim(allvars);
if substr(upcase(vname(allvars)),1,3) = 'COL' then
newstring = trim(newstring)||' '||left(put(allvars,8.));
end;
run;

Almost the same for the character type.

I hope that this helped you.
Klaz

 

[Asender]

Thanks Klaz.

I cam soooo close to getting your second solution.

Thanks for your time and help.

Regards,



Asender.

 

[Asender]

Just out of interest here is the solution that I came up with before I got your reply.

data AS.SASHELP_VCOLUMN;
set SASHELP.VCOLUMN;
where MEMNAME = 'FUNDS_TRANSPOSE' and NAME =: 'COL';

proc sort;
by NPOS LIBNAME;

run;

data AS.MAX_FUND_VAR;
retain VAR_LENGTH;
set AS.SASHELP_VCOLUMN end=eof;
by LIBNAME;
if first.LIBNAME then
do;
VAR_LENGTH = LENGTH;
end;
else
do;
VAR_LENGTH + LENGTH;
end;
if eof then call symput('VAR_LENGTH',VAR_LENGTH);
run;

data test;
length TEMP_FUND_KEY £&VAR_LENGTH;
retain TEMP_FUND_KEY;
set AS.SSAS_FUNDS_INVESTED;
by POLNO;

if first.POLNO then
do;
TEMP_FUND_KEY = FUNDCODE;
end;
else
do;
TEMP_FUND_KEY = COMPRESS(TEMP_FUND_KEY||FUNDCODE);
end;

if last.POLNO then
do;
FUND_KEY = TEMP_FUND_KEY;
output;
end;

run;

Thanks again for your help.