CODEWITHSUNDEEP
sas
Anonamous
Anonamous

Reputation: 303

Get last 2 observations of each country

I have different countries and need last 2 observations of each country

India 200
India 300
India 400
US 1000
US 2000
US 3000
US 4000

I should get -

India 300
India 400
US 3000
US 4000

Upvotes: 0

Views: 50

Answers (2)

data _null_
data _null_

Reputation: 9109

This assumes your data are grouped by country. I reckon you would call this a look ahead merge of some sort.

data country;
   input country $ x;
   cards;
India 200
India 300
India 400
NZ 4567
US 1000
US 2000
US 3000
US 4000
;;;;
   run;
data last2;
   merge country country(firstobs=3 keep=country rename=(country=z));
   if country ne z;
   run;
proc print;
   run;

enter image description here

Upvotes: 0

DCR
DCR

Reputation: 15665

there may be a shorter way but this will work:

data have;
country = "INDIA";
pop = 200;
output;
country = "INDIA";
pop = 500;
output;
country = "INDIA";
pop = 300;
output;
country = "US";
pop = 1200;
output;
country = "US";
pop = 1400;
output;
country = "US";
pop = 900;
output;
country = "US";
pop = 1500;
output;
country = "INDIA";
pop = 700;
output;
run;

proc sort data=have;
by country descending pop;
run;

data have;
set have;
by country;
retain cnt;
if first.country then cnt = 1;
else cnt = cnt + 1;
run;

proc sql noprint;
create table want as
select country,pop from have
where cnt < 3;quit;

Upvotes: 1

Related Questions