I have a certain data which requires me to compare a particular row with all the prior rows in a group and make an indicator accordingly. For e.g
ID | Issue_date | Paid_to_date | Product |
1 | 26-Dec-16 | 26-Dec-16 | ARS |
1 | 26-Dec-16 | 26-Dec-16 | ARS |
1 | 26-Dec-16 | 26-Dec-16 | ASS |
1 | 26-Dec-16 | 26-Dec-16 | ASS |
1 | 26-Dec-16 | 26-Dec-16 | ASS |
2 | 28-Dec-16 | 28-Dec-16 | ARS |
2 | 28-Dec-16 | 28-Dec-16 | ARS |
2 | 28-Dec-16 | 28-Dec-16 | ARS |
This is the dataset. Now i want to compare the second number row of first id with the first row of first id. And i want to compare the third row of first id with the second and first row of first id, fourth row of first id with the first,second, third row of first id. So basically i want to compare a particular row with all the prior rows for a given id. And i want to repeat the steps for all the other ids in the dataset. I tried but i could only compare two subsequent rows and not the ones prior to them.
Can you please help.
You can use the lag function to enable the comarison of any number of previous records. What is the maximum number of records you might want to compare, what do you want to compare, and what are you ultimatelly trying to achieve through the comparisons?
Art, CEO, AnalystFinder.com
The output you want ?
data have;
infile cards expandtabs truncover;
input ID (Issue_date Paid_to_date Product) (:$20.);
cards;
1 26-Dec-16 26-Dec-16 ARS
1 26-Dec-16 26-Dec-16 ARS
1 26-Dec-16 26-Dec-16 ASS
1 26-Dec-16 26-Dec-16 ASS
1 26-Dec-16 26-Dec-16 ASS
2 28-Dec-16 28-Dec-16 ARS
2 28-Dec-16 28-Dec-16 ARS
2 28-Dec-16 28-Dec-16 ARS
;
run;
data temp;
set have;
by id;
if first.id then n=0;
n+1;
run;
proc sql;
create table want as
select a.*,b.id as _id,b.issue_date as _issue_date,
b.paid_to_date as _paid,b.product as _product,
b.n as _n
from temp as a,temp as b
where a.id=b.id and a.n gt b.n
order by a.id,a.n,b.n;
quit;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.