Looping through table name in FROM in PROC SQL

[arunrami]

Dear all,

Here is my scenario, I need to change the table name used in PROC SQL dynamically(Highlighted in code) and use the same code for different names of table from database. I know for sure there are ways to do this (May be looping array) but instead of referring SAS documentation , I need any one of your help to provide me an glimpse of an idea about how can achieve this. 

in below sample, I  want to change the dataset name BKD1 and table name bkd(FROM statement) dynamically

Below code..

proc sql;
create table dummy.BKD1 as select
count(case when CD= 'N' then 1  end) as Count_NewRecord,
count(case when CD= 'C' then 1  end) as COUNT_Changed,
count(case when CD= 'D' then 1  end) as Count_Duplicate
from layer.bkd ;
quit;

[PaigeMiller]

---

@arunrami wrote:

Dear all,

Here is my scenario, I need to change the table name used in PROC SQL dynamically(Highlighted in code) and use the same code for different names of table from database. I know for sure there are ways to do this (May be looping array) but instead of referring SAS documentation , I need any one of your help to provide me an glimpse of an idea about how can achieve this. 

in below sample, I  want to change the dataset name BKD1 and table name bkd(FROM statement) dynamically

---

Explain this whole thing further.

Where are you getting the names of the other tables from? Show us.

What code are you referring to? Is it the variable CD?

Give us a clear example of what this means: "use the same code for different names of table from database".

[Tom · Accepted solution]

Sounds like you are ready to begin learning how to use macro variables.  The SAS macro processor will examine your SAS statements and replace macro triggers with the code they generate and then pass that code onto SAS.

So you have this code you want to run.

proc sql;
create table dummy.BKD1 as 
  select
    count(case when CD= 'N' then 1  end) as Count_NewRecord
   ,count(case when CD= 'C' then 1  end) as COUNT_Changed
   ,count(case when CD= 'D' then 1  end) as Count_Duplicate
 from layer.bkd 
;
quit;

So first make a macro variable that has the value BKD.

%let basename=bkd ;

Then replace the BKD in the code with references to the macro variable.

%let basename=bkd;
proc sql;
create table dummy.&basename.1 as 
  select
    count(case when CD= 'N' then 1  end) as Count_NewRecord
   ,count(case when CD= 'C' then 1  end) as COUNT_Changed
   ,count(case when CD= 'D' then 1  end) as Count_Duplicate
 from layer.&basename.
;
quit;

[tomrvincent]

'dynamically' as in a prompt?