Create a table with column name values available in a different table

Question

SAS Datastep- Create a table dynamically with the column name values available in a different table.

Example: My Source_Table would look like |Field No|Field Name| |1| A| |3| B| |2| C|

/*Dynamic table creation*/
%let  s1=;
/*Column lenght should be 30 characters so I am creating a dummy variable*/
%let Dummy= 'Dummy_Dummy_Dummy_Dummy_Dummy_Dummy_Dummy';

proc sql;
    create table TEMP as 
        select 'Hi' as Work from Temp_table where 1=2
    ;
quit;

proc sort data =   Source_table
    by Field_No;
run;

proc sql;
    select Dummy||" as "||fld into :s1 seperated by "," from
    (select "&Dummy" as Dummy,substr(strip(upcase(field_name)),1,30)) as FLD 
from Source_table)
    ;
quit;

proc sql;
    create table target_table  as 
        select "&Dummy." as value_1,&s1 from TEMP where 1=2;
quit;

Target table should be |A|B|C|

score 0 · Answer 1 · answered Feb 05 '19 at 14:16

It's not totally clear what you're asking; you specifically mention using SAS data step, but then your code example uses PROC SQL - does it matter which is used? Also, note that your input has |Field No|Field Name|1|A|3|B|2|C| but then you say the output should be in the order A-B-C - should the fields be in the order specified by Field_No, or not?

In any case, here is some very simple code that works:

    * Create the input data set;
    data source_table;
      field_no = 1; field_name = 'A'; output;
      field_no = 3; field_name = 'B'; output;
      field_no = 2; field_name = 'C'; output;
    run;

    * Derive the list of field/variable names, in the correct order;
    proc sql noprint;
      select field_name into :var_list separated by ' '
        from source_table
        order by field_no
      ;
    quit;

    * Create the output data set using the variable list created above;
    data target_table;
      length &var_list $ 30;
    run;

If there are additional requirements that mean this simple approach isn't allowed, please update the question to explain why not. This code creates all the specified columns as character variables with length 30, but can easily be expanded to allow the type, length and label for each variable to be specified in source_table as well - this sort of thing is done all the time in my work.

score 0 · Accepted Answer · answered Feb 06 '19 at 06:57

0

Thank you Chris.

I tried something like this, and it worked

proc sql noprint;
select catt(Field_name, ' char(30)') into :Col_name separated by ', '
from Source_table
order by field_no;

create table Target_table
(Value_1 char(30), &Col_name);
quit;

answered Feb 06 '19 at 06:57

Sudhan

287
2
5
11

Create a table with column name values available in a different table

2 Answers2