Select Into Separated By

[buechler66]

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
)

[PGStats]

separated by "#"

maybe?

Note : I can't find function NVL in SAS. Did you define your own? 

[buechler66]

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.

[Astounding · Accepted solution]

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 #

[buechler66]

Yes! Thank you.