DATA Step, Macro, Functions and more

Select Into Separated By

Accepted Solution Solved
Reply
Regular Contributor
Posts: 212
Accepted Solution

Select Into Separated By

[ Edited ]

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
)

 


Accepted Solutions
Solution
‎06-28-2016 11:28 AM
Super User
Posts: 5,085

Re: Select Into Separated By

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 #

View solution in original post


All Replies
Respected Advisor
Posts: 4,651

Re: Select Into Separated By

separated by "#"

maybe?

 

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

PG
Regular Contributor
Posts: 212

Re: Select Into Separated By

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.

Solution
‎06-28-2016 11:28 AM
Super User
Posts: 5,085

Re: Select Into Separated By

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 #

Regular Contributor
Posts: 212

Re: Select Into Separated By

Yes! Thank you.
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 328 views
  • 1 like
  • 3 in conversation