@SASAna wrote:
Hi SAS Team,
I am trying to do transpose the below created macro variable's ( SOURCE_FIELDS, TARGET_FIELDS) into columns like below, which i am using to create dynamic table.
Transpose really only means much in terms of a data set. A list of macro variables has no position except a relative text position.
I think you may be looking for something like:
proc sql;
create table test_table as
%do i = 1 to %sysfunc(countw(&source_fields.));
%if &i= 1 %then %do;
source_fields(&i) as target_fields(&i)
%end;
%else %do;
, source_fields(&i) as target_fields(&i)
%end;
%end; /* i loop*/
from <table_joins>;
quit;
for your "dynamic table" code. This is just iterating through the macro variable list. I hope your "source_field" values have the correct table references if any of those variable appear in more than one table.
Note that the comma in your separated by might not be needed in this case and a space would do
OR build your "as" clause statements properly in the sql:
PROC SQL;
SELECT catx(' as ',SOURCE_FIELDS , TARGET_FIELDS) as term
INTO :Aslist BY ','
FROM LOOKUP_TABLE
;
QUIT;
and use a single macro variable in the later select &aslist.
There will be some other macro language bits.
OR and probably a better idea given the number of values would be to use the LOOKUP_table to generate Call execute statements.
Which might look something like:
data _null_;
set lookup_table end=last;
if _n_=1 then call execute("proc sql; create table test_table as "||source_fields||" as "|| target_fields);
else call execute(", "source_fields||" as "|| target_fields);
if last then call execute("from <table_joins>;quit;");
run;
of course without seeing what you <table_joins> look like can't be any more specific there.
... View more
