Data step to create tables based on a column value

Sudhan — Tue, 05 Feb 2019 05:52:42 GMT

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.

Re: Data step to create tables based on a column value

FreelanceReinh — Tue, 05 Feb 2019 13:16:12 GMT

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;

topic Data step to create tables based on a column value in SAS Enterprise Guide

Data step to create tables based on a column value

Re: Data step to create tables based on a column value