I have a dataset of records each containing an Oracle SQL string in a variable named Rule. For example here are four of the Rule records.
A.ACTUAL_DLVRY_DATE IS NULL AND B.ACTUAL_DLVRY_DATE IS NOT NULL
NVL(A.ACTUAL_DLVRY_DATE,SYSDATE) <> NVL(B.ACTUAL_DLVRY_DATE,SYSDATE)
A.LAST_SCAN_DATETIME > B.LAST_SCAN_DATETIME
NVL(A.INTER_SCF_EXCL_IND ,'T') <> NVL(B.INTER_SCF_EXCL_IND ,'T')
In my program I create a list of these Rules and place them into a macro variable name Rule_List with each Rule separated by the # character.
SYMBOLGEN: Macro variable RULE_LIST resolves to A.ACTUAL_DLVRY_DATE IS NULL AND B.ACTUAL_DLVRY_DATE IS NOT
NULL#NVL(A.ACTUAL_DLVRY_DATE,SYSDATE) <> NVL(B.ACTUAL_DLVRY_DATE,SYSDATE)#A.LAST_SCAN_DATETIME >
B.LAST_SCAN_DATETIME#NVL(A.INTER_SCF_EXCL_IND ,'T') <> NVL(B.INTER_SCF_EXCL_IND ,'T')
However the output of my program gets goofy for the 4th Rule and I can't figure out how to correct it. It's as though it's being split up into 5 different fragmented Rule values. Please, any help would really be appreciated.
Program:
data QueryRules;
infile datalines;
input rule $70.;
datalines;
A.ACTUAL_DLVRY_DATE IS NULL AND B.ACTUAL_DLVRY_DATE IS NOT NULL
NVL(A.ACTUAL_DLVRY_DATE,SYSDATE) <> NVL(B.ACTUAL_DLVRY_DATE,SYSDATE)
A.LAST_SCAN_DATETIME > B.LAST_SCAN_DATETIME
NVL(A.INTER_SCF_EXCL_IND ,'T') <> NVL(B.INTER_SCF_EXCL_IND ,'T')
;
%macro loopy;
%local i rule rule_list;
proc sql;
select trim(rule) into : rule_list separated by "#" from QueryRules;
quit;
%do i=1 %to %sysfunc(countw(&rule_list,'#'));
%let rule = %scan(&rule_list, &i,'#');
%put &rule;
%end;
%mend;
%loopy;
Output:
A.ACTUAL_DLVRY_DATE IS NULL AND B.ACTUAL_DLVRY_DATE IS NOT NULL
NVL(A.ACTUAL_DLVRY_DATE,SYSDATE) <> NVL(B.ACTUAL_DLVRY_DATE,SYSDATE)
A.LAST_SCAN_DATETIME > B.LAST_SCAN_DATETIME
NVL(A.INTER_SCF_EXCL_IND ,
T
) <> NVL(B.INTER_SCF_EXCL_IND ,
T
)
The problem is using '#' instead of #. Macro language doesn't require quotes ... it would treat # as text in any case. Particularly in the case of %SCAN, using '#' as the set of delimiters indicates that either # or a single quote is a delimiter. That would throw off the parsing of the %SCAN function. It would probably throw off the count of COUNTW as well. Just change '#' to #
separated by "#"
maybe?
Note : I can't find function NVL in SAS. Did you define your own?
I've tried 'separated by' using many different characters. Doesn't seem to make a difference.
NVL isn't a SAS function, it's an Oracle function. It's just part of the data being stored in the Rule variable.
The problem is using '#' instead of #. Macro language doesn't require quotes ... it would treat # as text in any case. Particularly in the case of %SCAN, using '#' as the set of delimiters indicates that either # or a single quote is a delimiter. That would throw off the parsing of the %SCAN function. It would probably throw off the count of COUNTW as well. Just change '#' to #
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.