topic Re: Select Into Separated By in SAS Programming

Select Into Separated By

buechler66 — Mon, 27 Jun 2016 18:38:01 GMT

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
)

Re: Select Into Separated By

PGStats — Mon, 27 Jun 2016 18:30:32 GMT

separated by "#"

maybe?

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

Re: Select Into Separated By

buechler66 — Mon, 27 Jun 2016 18:34:35 GMT

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.

Re: Select Into Separated By

Astounding — Mon, 27 Jun 2016 18:42:31 GMT

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 #

Re: Select Into Separated By

buechler66 — Tue, 28 Jun 2016 15:27:57 GMT

Yes! Thank you.