Calling macro variable in proc sql

[Babloo]

In the below example, value of the macro variable etls_jobName resolves to

%let etls_jobName = %nrquote(IF_04_J_PRT_TRA_INSURANCE_RECORD_DELIVERY);

I've no clue why I got this error and am I doing something wrong while calling macro variable in proc SQL?

5001      +create table dis_entity_id as select distinct ENTITY_ID format=$ENTITY. from scan(&etls_jobName.,6,'_');
                                                                                                           _
                                                                                                           76
ERROR 76-322: Syntax error, statement will be ignored.

NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
NOTE: The SAS System stopped processing this step because of Errors.

[PaigeMiller]

You can't use SCAN after FROM. Not allowed in PROC SQL.

Maybe you want %let etls_jobName = %scan(IF_04_J_PRT_TRA_INSURANCE_RECORD_DELIVERY,6,_);

You also don't need %NRQUOTE() here.

[VinitvictorCorr]

USE THIS INSTEAD

%let l=%scan(IF_04_J_PRT_TRA_INSURANCE_RECORD_DELIVERY,6,"_");

proc sql;
create table dis_entity_id as select distinct entity_id from &l.;
quit;

[Babloo]

Right! I forgot to tell about my desired Output in my Initial post. I want extract all the words from the given string from 6th word to till last word separated by '_'.

e.g. desired output which I need from scan function is

INSURANCE_RECORD_DELIVERY

[VinitvictorCorr]

then you dont need a macro variable all.use scan directly

[Babloo]

I'm unable to make it work with only scan function. Just to inform you that I always used to have the job name in macro variable per my inital post and from that I've to extract the table name.

[VinitvictorCorr]

ohhh..if you have to extract the table name then it will need creation of a macro variable. refer to the previous solution in that case. but if you want to work directly on variables, then scan function is the way to go. mostly all functions we use on variables, work same on macro variables, we just have to put a % before them.

[PaigeMiller]

---

@Babloo wrote:

Right! I forgot to tell about my desired Output in my Initial post. I want extract all the words from the given string from 6th word to till last word separated by '_'.

e.g. desired output which I need from scan function is

INSURANCE_RECORD_DELIVERY

---

You need to find the position of the sixth underscore, and then take all characters to the left. There are many examples of code in these forums of finding the positions of the second or third instance of character or word; you should search for them.

[Tom · Accepted solution]

The main issue is that your SAS syntax is invalid.  You cannot use functions to replace code.  In this case you cannot call the SCAN() function at a place where SAS needs the name of a dataset.  You need to either use macro functions that generate the code or use regular SAS code to write the generated code to a file and %INCLUDE the file to run the generated code.

The syntax of the SCAN() function call also does not make much sense as you are trying to scan the value of a variable named IF_04_J_PRT_TRA_INSURANCE_RECORD_DELIVERY instead of the text string "IF_04_J_PRT_TRA_INSURANCE_RECORD_DELIVERY".

So if you change to using the %SCAN() macro function instead then your code will generate valid SAS code.

%let etls_jobName = IF_04_J_PRT_TRA_INSURANCE_RECORD_DELIVERY;
proc sql ;
create table dis_entity_id as
  select distinct ENTITY_ID format=$ENTITY. 
  from %scan(&etls_jobName.,6,'_')
;

Which will read from the dataset named INSURANCE since that is the 6th word in your macro variables's value.

Note the + symbol in your log makes it look like you are already including this code from a file. Perhaps you can just fix the step that generates that file?