BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Sudhan
Fluorite | Level 6

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.

1 ACCEPTED SOLUTION

Accepted Solutions
FreelanceReinh
Jade | Level 19

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;

View solution in original post

1 REPLY 1
FreelanceReinh
Jade | Level 19

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;

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

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!

Register Now

Creating Custom Steps in SAS Studio

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 1 reply
  • 2537 views
  • 0 likes
  • 2 in conversation