macro to transpose and add a prefix after ordering columns' names

Question

I need to run a macro that does a transpose for many variables (and creates a table for each one), orders the columns names, which are numeric, but also adds as a prefix the variable's name (which is a string).

I have a macro in SAS to perform a transpose with different variables as var in the transpose. The code is:

%macro transponer(var);
proc transpose data=labo2.A_svm_200711_200806
out=labo2.D_tr_&var.0;
var &var;
id mes;
by cid;
run;

/*......more code.....*/

select cats(name, '=', &var, name)
into :prefijolista
separated by ' '
from dictionary.columns 
where libname='LABO2' and memname= cats('D_TR_',upcase(&var))
and name like '_20%';
quit;
%put &prefijolista;
%mend;

Since mes is numeric I wanted to order the variable, that's why I didn't introduce the "prefix &var" in the proc transpose but instead I did it after the retain (that was useful to order the columns).

The problem starts when I try to introduce the prefix (after the ordering).

Since one of the variables' name is for example "monto", I get the following error (because it is the var variable in the transpose and it's not a column name in the transposed table):

The following columns were not found in the contributing tables: monto.

My next step would be:

proc datasets library=labo2;
modify D_tr_&var.0;
rename &prefijolista;
quit;

But I cant do it untill I get the previous one done.

So I don't know how to order the columns after the transpose and also add the prefix.

How can I solve this?

Thanks!

Answer 1

You need to rename the columns using something like PROC DATASETS.

proc datasets lib=work nolist;
modify myDataSet;
rename old_col_name = new_col_name;
run;
quit;

A documentation example is available in the Base SAS guide under the doc for PROC DATASETS. It is available online at: http://support.sas.com/documentation/cdl/en/proc/67327/HTML/default/viewer.htm#n0mfav25learpan1lerk79jsp30n.htm

Answer 2

The problem was that &var inside the cats function inside a macro hast to use

" "

Also you could use

sysfunc(cats(D_TR, &a)

So finally the code will remain like:

%let a = %upcase(&var);
%put &a;
%let b=%sysfunc(cats(D_TR_,&a));
%put &b;

proc sql;
select cats(name, '=', "&var" , name)
into :prefijolista
separated by ' '
from dictionary.columns 
where libname='LABO2' and memname= "&b"
and name like '_20%';
quit;
%put &prefijolista;
%put "&b";


PROC datasets library=LABO2;
modify &b;
rename &prefijolista;
quit;
%put "ult" &b;

Not very straightforward, but worked. :)