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.
expected transpose -
source_field1
source_field2
source_field3
......so on till 500th attribute
target_field1
target_field2,
target_field3
... so on 500th attribute
using in Dynamic table -
proc sql;
create table test_table as
source_field(i) as target_field(i)
from <table_joins>;
quit;
there are around 500 attributes with comma separated by comma in source and target fields.
PROC SQL;
SELECT SOURCE_FIELDS , TARGET_FIELDS
INTO :SOURCE_FIELDS , INTO :TARGET_FIELDS SEPARATED BY ','
FROM LOOKUP_TABLE
;
QUIT;
Did you try using proc transpose? What happened?
Not sure what your trying to do. If you want the macro values separated by comma into a table then try something like this.
%let SOURCE_FIELDS=source_field1,source_field2,source_field3;
data want;
do i=1 to 3;
SOURCE_FIELDS=scan("&SOURCE_FIELDS",i,',');
output;
end;
run;
@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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.