☑ This topic is solved.
Need further help from the community? Please
sign in and ask a new question.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Posted 07-16-2024 04:55 AM
(388 views)
Data b;
input cc swipe;
cards;
1 200
1 300
1 200
1 900
2 1300
2 1400
2 1500
;
run;
proc sort data=b;
by cc;
run;
data last_second;
set b;
by cc;
last_second_value = lag(swipe);
if last.cc then do;
output;
end;
drop swipe;
run;
How to get last second values using PROC SQL Method
output
cc swipe
1 200
2 1400
1 ACCEPTED SOLUTION
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
SQL does not have a concept of sequence, it works with sets. Stay with the DATA step, e.g.
data last_second;
do until (last.cc);
set b;
by cc;
if not last.cc then last_second_value = swipe;
end;
drop swipe;
run;
Your code can be simplified by using a Subsetting IF:
data last_second;
set b;
by cc;
last_second_value = lag(swipe);
if last.cc and not first.cc;
drop swipe;
run;
The expanded condition takes care of cases where there's only one observation for a cc group.
1 REPLY 1
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
SQL does not have a concept of sequence, it works with sets. Stay with the DATA step, e.g.
data last_second;
do until (last.cc);
set b;
by cc;
if not last.cc then last_second_value = swipe;
end;
drop swipe;
run;
Your code can be simplified by using a Subsetting IF:
data last_second;
set b;
by cc;
last_second_value = lag(swipe);
if last.cc and not first.cc;
drop swipe;
run;
The expanded condition takes care of cases where there's only one observation for a cc group.