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 .. .. .. ... View more

Hi Every one,    I am trying to convert my postgre sql code to SAS where I declared a record. What is the best way that I can declare a record in sas. How to implement the below functionality in sas either using Data step or proc sql. Any help is highly appreciated.     Below is my postgre sql code. It will declare a record and then iterate through each record at a time in the for loop. It uses the execute format to dynamically populate the values using all and year.  DO $do$ DECLARE rec RECORD; BEGIN /* MAKE test1 TABLE */ /* MAKE TABLE */ CREATE TABLE test1 (Name varchar(15),year varchar(4),PRIMARY KEY (Name,year)); /* POPULATE test1 WITH APPROPRIATE Name */ INSERT INTO test1 (Name,year) SELECT b.Name, c.year FROM (SELECT * FROM test2) b, (SELECT DISTINCT year FROM test3) c; /* POPULATE test1 WITH 'ALL' ROWS */ FOR rec IN SELECT DISTINCT year FROM test3 LOOP EXECUTE format('INSERT INTO test1 (Name,year) VALUES ($1,$2)') USING 'ALL', rec.year; END LOOP; END $do$;   ... View more

Hi Everyone, I attended an interview last week and got this question. I am not able to understand the question. Can someone please help in solving this.   He said, “Here is data set 1 and here is data set 2.  Give me the statement to merge these datasets and sum it by region for the months of January and February.”  I did not understand that the second dataset would have two separate variables (January and February on the same line). I was assuming that they would be on separate lines (as in most relational data bases). "   Thanks for the time.   Regards, Ravi ... View more