topic proc transpose in SAS Programming

proc transpose

SASAna — Thu, 31 May 2018 19:52:10 GMT

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;

Re: proc transpose

PGStats — Thu, 31 May 2018 20:02:02 GMT

Did you try using proc transpose? What happened?

Re: proc transpose

SuryaKiran — Thu, 31 May 2018 20:26:36 GMT

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;

Re: proc transpose

ballardw — Thu, 31 May 2018 20:55:15 GMT

@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.