PROC SQL: Error Message

Accepted Solution Solved
Reply
Regular Contributor
Posts: 222
Accepted Solution

PROC SQL: Error Message

Hi....I am getting an error message when I try an run this code and I am not too sure how to resolved it. What I am attempting to do is from a created table that has a list of SAS Datasets with their corresponding FirstDay (FDay) and LastDay (LDay), I want to be able to extract the data from the appropriate datasets such that the claim_date is between the FDay and LDay to ensure that I do not duplicate records. I have attached the code as well as the error message that I am getting. Thanks.

%LET FROMDATE = 20100401;
%LET TODATE = 20131231;
%LET PIN = 102079880;

DATA TableRanges (DROP=date Class1 Class2 MAX_of_date);
SET dates_info ab;
RUN;

PROC SQL NOPRINT;
SELECT Class,
  FirstDay,
  LastDay
INTO :tableList separated by ' ',
  :FDay separated by ', ',
  :LDay separated by ','
FROM TableRanges
WHERE LDay >= &fromDate and FDay <= &toDate;
QUIT;

%LET FDay = &FDay;
%LET LDay = &LDay;

DATA Summary;
SET &tableList;
  WHERE ((claim_date between &FDay AND &LDay)
  AND (pin = "&pin"));
RUN;

109         /*
110        %LET tableList = &tableList;   */
111        %LET FDay = &FDay;
112        %LET LDay = &LDay;
113       
114        DATA Summary;
115         SET &tableList;
116          WHERE ((claim_date between &FDay AND &LDay)
NOTE: Line generated by the macro variable "FDAY".
116         20100401, 20110401, 20120401
                    _
                    22
                    76
ERROR 22-322: Syntax error, expecting one of the following: !!, *, **, +, -, /, AND, ||. 

ERROR 76-322: Syntax error, statement will be ignored.

117          AND (pin = "&pin"));
ERROR: Syntax error while parsing WHERE clause.
118        RUN;


Accepted Solutions
Solution
‎02-04-2014 01:38 PM
Super User
Super User
Posts: 6,502

Re: PROC SQL: Error Message

You cannot write a expression like:

claim_date between 20100401, 20110401, 20120401 AND ...


You might want to change your first query.


SELECT catx(' ','claim_date between',FDay,'and',LDay)

INTO :wherelist separated by ' or '

FROM TableRanges

WHERE LDay >= &fromDate and FDay <= &toDate;


Then use it in the where clause:

where (&wherelist)


View solution in original post


All Replies
PROC Star
Posts: 7,363

Re: PROC SQL: Error Message

Without seeing your data it is difficult to see what your code is trying to do.  It appears, from your code, that firstday and lastday are either character or numeric fields that contain the yyyymmdd that represent dates, rather than actually being SAS date fields.

The error you are getting is that the macro variables aren't representing single dates, as used in your where statement but, rather, all dates that had met the condition.  Thus, in your log, it shows the macro variable as containing three space-separated strings that represent dates.

If that is what you were trying to accomplish in the SQL part of the code, it might be more direct to NOT create the macro variables but, rather, create a table that you can then use in a join to select the ultimately desired records.

Contributor
Posts: 64

Re: PROC SQL: Error Message

Hi Everyone,

The macro variables created as character variables. If the variables automatically change for instance comparison the number will 20100401.

But in dataset the date's sas values might be less than that.

*** In data step the macro variables are resolved before the execution of the other statements in data step.

Please correct me if I am wrong.

Respected Advisor
Posts: 3,124

Re: PROC SQL: Error Message

"The macro variables created as character variables."

--Wrong. macro variables contain text strings, which could be number or char. The end results all depend on the context and how you deliver it. In this case, for example, if you use "&FDay", then the string of 20100401 will be a char string; else if you omit the quotes, it doesn't need to be converted, it is a number by itself. If the content string is not in a number format, say, NAME, then with quotes, it becomes a char string: "NAME", without, SAS sees it as a variable name (an expression).

Haikuo

BTW, for OP's issue, I believe has gotten to the bottom of it.

Contributor
Posts: 64

Re: PROC SQL: Error Message

Thank you Hai.kuo. for correcting me..

Regular Contributor
Posts: 222

Re: PROC SQL: Error Message

Hi Everyone.....Thank you to everyone for all their help and their suggestions. I did try Tom's suggestion and with a slight modification by adding a statement to resolve the macro variable &wherelist, the code worked perfect and like I had wanted. Thanks once again.

Solution
‎02-04-2014 01:38 PM
Super User
Super User
Posts: 6,502

Re: PROC SQL: Error Message

You cannot write a expression like:

claim_date between 20100401, 20110401, 20120401 AND ...


You might want to change your first query.


SELECT catx(' ','claim_date between',FDay,'and',LDay)

INTO :wherelist separated by ' or '

FROM TableRanges

WHERE LDay >= &fromDate and FDay <= &toDate;


Then use it in the where clause:

where (&wherelist)


Regular Contributor
Posts: 222

Re: PROC SQL: Error Message

Hi Tom.....Thanks once again for your suggestion and help. One further question. If the claim_date was not numerical and was a character, how would that change the codee you suggested...Thanks.

Super User
Super User
Posts: 6,502

Re: PROC SQL: Error Message

Use the QUOTE() function to put quotes around the values.

SELECT catx(' ','claim_date between',quote(FDay),'and',quote(LDay))

INTO :wherelist separated by ' or '

FROM TableRanges

WHERE LDay >= &fromDate and FDay <= &toDate;

☑ This topic is SOLVED.

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

Discussion stats
  • 8 replies
  • 534 views
  • 6 likes
  • 5 in conversation