DATA Step, Macro, Functions and more

SQL Order By Statement is generating the error

Reply
Occasional Contributor
Posts: 5

SQL Order By Statement is generating the error

[ Edited ]

The following is a piece of my program. I would like to count how many times a particular number has borrowed for the last 30 days.

 

 

%MACRO LOOP(dataset);

%DO I=1 %TO 10; 
proc sql outobs=1;
select count(*) from &dataset
where msisdn = &&msisdn&I and 
abs(datepart(advance_datetime) - 
datepart((select advance_datetime from &dataset where msisdn = &&msisdn&I
order by advance_datetime desc limit 1)) < 31;
quit;
%END;
%MEND LOOP;
%LOOP(credit.bbhistory);

When I run it, it generated the following error message(see the bellow snapshot).

Please, help me and tell me what I am doing wrong.

Order by error.JPG

Super User
Super User
Posts: 7,413

Re: SQL Order By Statement is generating the error

Could be a few things, but first off order by does not work in sub queries.  Also, at no point in that code is: MSISDN declared.  

 

The question is why do it this way in the first place?  There is no explanation for the whole do loop for instance.  However if I wanted to get a count based on a condition then the steps are simple:

1) create a category to indicate if the value is within a window

2) count based on the category

 

For example:

data want;
  set have;
  if date between low and high then cat="Y";
run;

proc sql;
  create table WANT as
  select  count(*) as RESULT
  from    WANT
  group by CAT;
quit;

That would give you a two way result, count of those having Y and those not.  Also, follow the guidance 

Occasional Contributor
Posts: 5

Re: SQL Order By Statement is generating the error

@RW9, thanks for your input. This is for clarification.
As you can see the msisdn is a macro variable, it has been declared outside of the loop function. additionally, the reason I wished to get the counts in that way, is to avoid creating intermediary datasets
Super User
Super User
Posts: 7,413

Re: SQL Order By Statement is generating the error

Please ensure you post all relelvant information in the post.  As for not wanting to creating an intermediate dataset - you are creating an intermediate dataset (for each iteration of the do loop) however you just don't see it.  The method you are using here will not be more efficient than having one dataset and one count procedure, as yours will always be * 10.

Occasional Contributor
Posts: 5

Re: SQL Order By Statement is generating the error

@RW9, Ok. Let me then try your steps
Super User
Posts: 6,963

Re: SQL Order By Statement is generating the error

Run the SQL outside of a macro and macro loop (set the macro variable i manually), and look at the log. The ERROR message will point to the location of the error, and make it easier to debug.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 6,963

Re: SQL Order By Statement is generating the error

After further looking, I count four opening and three closing brackets in your SQL where condition; correct that mismatch.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 5

Re: SQL Order By Statement is generating the error

%MACRO LOOP(dataset);
 
%DO I=1 %TO 10; 
proc sql outobs=1;

select count(*) from &dataset
where msisdn = &&msisdn&I and 
abs(datepart(advance_datetime) - 
datepart((select advance_datetime from &dataset 
where msisdn = &&msisdn&I order by advance_datetime desc limit 1))) < 31;
quit;

%END;
%MEND LOOP; 

%LOOP(credit.bbhistory);

This is the codes after adding the fourth closing. Like you mentioned, it was the problem. However, it did not get completely resolved.

This is what I got ; error message due to limit statement

limit error.JPG

Super User
Posts: 6,963

Re: SQL Order By Statement is generating the error

There is no limit statement in SAS.

Do you have some custom code defined that is sent before every program submit, or is that part of your code?.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 5

Re: SQL Order By Statement is generating the error

this is part of my code (it's a function which can be called anytime )
Super User
Posts: 6,963

Re: SQL Order By Statement is generating the error

Please supply link to the documentation for "limit". Smiley Wink

 

There is no limit statement or function in SAS.

If you want to limit the number of output observations from proc sql, look into the outobs= option for the proc sql statement.

 

limit n works in MySQL, but it is not standard SQL language.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Ask a Question
Discussion stats
  • 10 replies
  • 115 views
  • 0 likes
  • 3 in conversation