Creating datasets and inserting data based on a condition during execution

[rkarr5]

This is my test data and for every order type I want to create different tables with same structure and insert data into it from other tables. For example, for 'Internet' order type I want to create different tables for order code
internet_1a,internet_1b,internet_1c,internet_1d,internet_1e,internet_1f with same structure that contains two columns ordercode and year.

I have the sample sql code that generates this.

data table1;
     input ordertype $ ordercode $ code $ name $;
     datalines;
internet internet_1a google_ad google
internet internet_1a google_ad google
internet internet_1b website target
internet internet_1b website target
internet internet_1c facebook facebook
internet internet_1c facebook facebook
internet internet_1d twitter twitter
internet internet_1d twitter twitter
internet internet_1e instagram instagram
internet internet_1e instagram instagram
internet internet_1f other other
app app_2a north north
app app_2a north north
app app_2b south south
app app_2b south south
app app_2c east east
app app_2c east east
app app_2d west west
app app_2d west west
app app_2e central central
app app_2e central central
app app_2f other other
app app_2f other other
;

data test1;
     input year $;
     datalines;
2012
2012
2013
2014
2015
2015
2016
2017
2018
;

for rec in select * from table1 where ordertype='internet' loop
execute format('create table %I(ordercode varchar(15),year varchar(4))',rec.ordercode);
execute format('insert into %I(ordercode,year) select b.ordercode,e.year from(select * from table1 where ordertype='app') b, (select distinct year from test1) e',rec.ordercode);

Example of output table: Internet_1a is created with data

order_code year

north 2012

north 2013

north 2014

north 2015

north 2016

north 2017

north 2018

south 2012

south 2013

south 2014

south 2015

south 2016

south 2017

..

..

..

[ed_sas_member]

Hi @rkarr5 

You can try this. It generate one dataset name according to the ordercode and containing 2 columns: year (with 1 observation per year from 2012 to 2018) and code.

I am not sure if this is what you want, as your entry data are very weird: lot of duplicate records and there is no rule to link codes "north" or "south" with Internet_1a.

/* List of tables to be created into macrovariables */

proc sql noprint;
	select distinct ordercode into: ordercode_1-:ordercode_999  from table1;
	select count(distinct ordercode) into: nb_ordercode from table1;
run;

/* Retrieve each couple of ordercode-code */
proc sort data=table1 out=table1_nodupkey (keep=ordercode code) nodupkey;
	by ordertype ordercode code name;
run;

/* Output 1 row / year for each couple ordercode-code - Method 1*/
data table2;
	set table1_nodupkey;
	do year=2012 to 2018;
		output;
	end;
run;

%macro ordercode_tables();
	%do i=1 %to &nb_ordercode;
		data &&ordercode_&i (drop =ordercode);
			set table2;
			where ordercode = "&&ordercode_&i";
		run;
	
		*title "&&ordercode_&i";
		*	proc print data=&&ordercode_&i;
		*	run;
		*title;
	%end;
%mend;

%ordercode_tables;