BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Quartz | Level 8

## last second value for each group

``````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 swipe1 2002 1400`
1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: last second value for each group

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
Super User

## Re: last second value for each group

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.

Discussion stats
• 1 reply
• 298 views
• 0 likes
• 2 in conversation