Hi,
I would like to create a table dynamically based on the column available in a different table.
Example:
My source_table would look like
Field_No | field_Name
1 | A
3 | C
2 | B
My target_table should look like
A | B | C
Currently my solution is not so efficient.
/*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,Compress(tranwrd(tranwrd(tranwrd(tranwrd(tranwrd(Compress(Compress(Compress(substr(strip(upcase(field_name)),1,30)),"("),")"),'*',''),' ',''),'.',''),'-',''),'>',''),,'s') 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;
Appreciate your support.
Hello @Sudhan and welcome to the SAS Support Communities!
How about something along these lines:
data source_table;
input field_no field_name $;
cards;
1 A
3 C
2 B
;
proc sql noprint;
select catt(field_name, ' char(30)') into :fn separated by ', '
from source_table
order by field_no;
create table target_table
(value_1 char(30), &fn);
quit;
Hello @Sudhan and welcome to the SAS Support Communities!
How about something along these lines:
data source_table;
input field_no field_name $;
cards;
1 A
3 C
2 B
;
proc sql noprint;
select catt(field_name, ' char(30)') into :fn separated by ', '
from source_table
order by field_no;
create table target_table
(value_1 char(30), &fn);
quit;
Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!
Check out this tutorial series to learn how to build your own steps in SAS Studio.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.