BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Lupacante
Calcite | Level 5

Hi all,

is there an equivalent to the lag function in a PROC SQL step?

Thank you,

Marco

EDIT: re-posting this as I posted it in the wrong section earlier

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

You would need to sort your data, otherwise monotonic seems to work ok.  Its not ideal though, you will notice that it throws some weird results when using where/order by's etc. so I would generally avoid it.  An example below.  What I would look at is what you are trying to achieve with using a lag function, and how the problem could be solved using SQL rather than trying to translate a specific function between two different languages.

data have;

  attrib name format=$20. qtr result format=best.;

  infile datalines;

  input name $ qtr result;

datalines;

Terry 3 12

Terry 4 13

Rob   1 90

Rob   3 91

Terry 1 11

Terry 2 15

Rob   4 50

Rob   3 51

;

run;

proc sort data=have;

  by name qtr;

run;

proc sql;

  create table WORK.WANT as

  select  BASE.*,

          PREV.QTR as PREV_QTR,

          PREV.RESULT as PREV_RESULT

  from    (select *,MONOTONIC() as IND from WORK.HAVE) BASE

  left join (select *,MONOTONIC() as IND from WORK.HAVE) PREV

  on      BASE.NAME=PREV.NAME

  and     BASE.IND=PREV.IND+1;

quit;

View solution in original post

13 REPLIES 13
DBailey
Lapis Lazuli | Level 10

Generally, SQL doesn't consider sets to be ordered, so you have to force order on them by adding some sort of variable to match back to the dataset.  For example, if you have a dataset HAVE like this:

Business_Date date

Val1 num

Val2 num

you could do this:

proc sql;

select

     t1.business_date,

     t1.val1,

     t1.val2,

     t2.val1 as Prior_Vall1,

     t2.val2 as Prior_Val2

from

     have t1

     left outer join have t2

          on t1.business_date-1=t2.business_date;

quit;

LinusH
Tourmaline | Level 20

If you don't have a natural row id variable (such as business date in 's example), you can enforce row numbers, either by using the unsupported and fairly undocumented function monotonic(), or by storing the row numbers explicitly in the source table ("have").

But, at this point, it is quite clear that both programming and performance wise, this is not a good idea. This is easier to code in a data step, and joins always comes with a higher cost than a table scan.

Data never sleeps
jakarman
Barite | Level 11

As said the SQL approach has a complete different set of axioma-s eg data is considered not being ordered/sorted as opposed in other language e.g. the SAS-datastep where the data is assumed to be ordered.

The result is you cannot do some things in Proc SQL that you can do in the SAS-datastep as of this kind of axioma-s.

Answer: no there  is not  something like the lag function in SQL  (ansi Sql).

Sometimes there are aggregate functions that will do your original functional request, that is only a subset of all possibilities you could solve within a data-step or 3GL language.

Before SAS 9  within SAS proc sql was solved in a sequential/single process byt that monotonic could work.

Do not trust any ordering with proc sql anymore as it was changed to be multi-threading, that is several sub-processes can run on selected parts of the data. It will using the axioma of not having a defined ordering.          

---->-- ja karman --<-----
RW9
Diamond | Level 26 RW9
Diamond | Level 26

You would need to sort your data, otherwise monotonic seems to work ok.  Its not ideal though, you will notice that it throws some weird results when using where/order by's etc. so I would generally avoid it.  An example below.  What I would look at is what you are trying to achieve with using a lag function, and how the problem could be solved using SQL rather than trying to translate a specific function between two different languages.

data have;

  attrib name format=$20. qtr result format=best.;

  infile datalines;

  input name $ qtr result;

datalines;

Terry 3 12

Terry 4 13

Rob   1 90

Rob   3 91

Terry 1 11

Terry 2 15

Rob   4 50

Rob   3 51

;

run;

proc sort data=have;

  by name qtr;

run;

proc sql;

  create table WORK.WANT as

  select  BASE.*,

          PREV.QTR as PREV_QTR,

          PREV.RESULT as PREV_RESULT

  from    (select *,MONOTONIC() as IND from WORK.HAVE) BASE

  left join (select *,MONOTONIC() as IND from WORK.HAVE) PREV

  on      BASE.NAME=PREV.NAME

  and     BASE.IND=PREV.IND+1;

quit;

jakarman
Barite | Level 11

@rw9, Not all problems can be solved with SQL.  There is always a search for the right tool for a problem.   

The acceptance of a limitation should be part of a learning process. That is the why behind mathematics.

---->-- ja karman --<-----
Ksharp
Super User

To be honest. I will not recommend to use RW9's code. MONOTONIC() is undocumented function which means you are going to suffer the risk from it .Sometime you are going to wrong result.I will use ODS to get that index row.


ods listing close;
ods output sql_results=have;
proc sql number;
 select name,age from sashelp.class;
quit;
ods listing;
proc sql;
create table want as
 select *,(select age  from have where row=a.row-1) as lag_age
  from have as a;
quit;

Ksharp

Haikuo
Onyx | Level 15

Very nice, Ksharp! Great to have you back.

Haikuo

Ksharp
Super User

HaiKuo,

Hope we have a meet in near future.

Xia Ke Shan

rajeshm
Quartz | Level 8

hi,

good answer. can you help me the similar as below

Empid   year    salary

1         2011    11

1         2012    13

1         2013     20

2         2011     44      

2         2012     22

2         2013     88

Requirement:    Need a 4th column representing percentage of salary change for an empid  by years.

                                             datastep->lag function-> we can do

                                              proc sql-> how to do this?

thanks in advance.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Should start a new topic for this as this post is closed.  You can do the same as above without the need for monotonic as you can use year.

proc sql;

     create table WANT as

     select     A.EMPID,

                   A.YEAR,

                    A.SALARY,

                    (A.SALARY / B.SALARY)  * 100 as SAL_PCENT    

     from        HAVE A

     left join    HAVE B

     on          A.EMPID=B.EMPID

     and        A.YEAR=B.YEAR+1;

quit;

rajeshm
Quartz | Level 8

Hi,

thanks a lot. I understood.

Ksharp
Super User

OK. Assuming the data as simple as it is :

data have;
input Empid   year    salary ;
cards;
1         2011    11
1         2012    13
1         2013     20
2         2011     44      
2         2012     22
2         2013     88
;
run;
proc sql;
create table want as 
 select *,(salary-(select salary from have where Empid=a.Empid and year=a.year-1) )/salary as per format=percentn8.2
  from have as a;
  quit;

Xia Keshan

rajeshm
Quartz | Level 8

hi,

thanks a lot. i understood.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 13 replies
  • 44429 views
  • 13 likes
  • 8 in conversation