topic Re: PROC SQL issue in SAS Programming

PROC SQL issue

jaymin_shah — Fri, 09 Oct 2015 18:05:35 GMT

I have used If first. and last. options in data step.

But, couldn't find any similar options in proc sql.

Please provide me the solution for this issue.

Re: PROC SQL issue

PaigeMiller — Fri, 09 Oct 2015 18:22:06 GMT

These options do not exist in PROC SQL.

They exist in DATA steps.

Thus, if you need to use these options, you would use a DATA step and not PROC SQL.

Re: PROC SQL issue

PGStats — Fri, 09 Oct 2015 21:01:47 GMT

Conceptually, SQL operates on unordered sets of records. Operations such as first, last, next, previous have no meaning in that context.
The DATA step on the other hand operates on a sequence of records. You will sometimes need to sort your data before running the DATA step.

Use both! You will twice as efficient!

Re: PROC SQL issue

LinusH — Sat, 10 Oct 2015 08:18:19 GMT

"Please provide me the solution for this issue"
Yeah, what exactly is your issue?
Syntax itself us never an "issue".

Re: PROC SQL issue

Peter_C — Sat, 10 Oct 2015 13:59:53 GMT

Linus
I think the issue is establishing the first and last rows within groups. The limitation of sql was not justified.
The result (finding first. And last. Rows) is native to data steps. My issue is the unexplained constraint (using sql).
But it really is no issue at all.

Re: PROC SQL issue

Patrick — Sat, 10 Oct 2015 14:12:54 GMT

I believe @PGStats comment nailed it.

Having said that: Some database SQL extensions provide "first" and "last" as analytics functions http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions056.htm ... but then: They don't have the SAS data step and implementing the same with PLSQL can be painful.