Creating datasets and inserting data based on a condition during execution

rkarr5 — Mon, 02 Dec 2019 15:53:53 GMT

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

Re: Creating datasets and inserting data based on a condition during execution

ed_sas_member — Tue, 03 Dec 2019 09:35:16 GMT

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;

topic Creating datasets and inserting data based on a condition during execution in SAS Programming

Creating datasets and inserting data based on a condition during execution

Re: Creating datasets and inserting data based on a condition during execution