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.
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?
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.
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
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.
@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.
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?
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: