BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
arunrami
Pyrite | Level 9

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;
1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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;

View solution in original post

3 REPLIES 3
PaigeMiller
Diamond | Level 26

@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".

 

 

--
Paige Miller
Tom
Super User Tom
Super User

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
Rhodochrosite | Level 12
'dynamically' as in a prompt?

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • 3325 views
  • 0 likes
  • 4 in conversation