topic Re: find first id and date in SAS Enterprise Guide

find first id and date

hk2013 — Sat, 30 Jun 2018 01:09:46 GMT

data one;
input ID   Date total predicted;
cards;
1              6/24/18    200    250
1             6/30/18    200    270
1              6/18/20    200   230 
2               5/24/18     160   200
2             5/27/18     160   210
2             5/22/18     160   170
3          5/20/18     300   450
3           5/14/18     300   400
3            5/4/18     300     425
;

i want to pick the first date for each ID and subtract that date and IDs Predicted from Total

Re: find first id and date

PaigeMiller — Sat, 30 Jun 2018 01:15:29 GMT

Show us the desired result.

Re: find first id and date

hk2013 — Sat, 30 Jun 2018 01:19:07 GMT

result would look like this 
ID             Date       total  Predicted   diff
1              6/18/20    200    230           -30
2             5/22/18     160   170            -10
3            5/4/18     300     425           -125

Re: find first id and date

Reeza — Sat, 30 Jun 2018 01:26:16 GMT

The way your data is sorted you're looking for the last. Is your data sort important and verified or do you need to verify it?

proc sort data=have; 
by id date;
run;

data want;
set have;
by id;
if first.id;
dif = predicted-total;
run;

Documentation references:

http://documentation.sas.com/?docsetId=lrcon&docsetTarget=n138da4gme3zb7n1nifpfhqv7clq.htm&docsetVersion=9.4&locale=en

They have some good pictures that illustrate how BY groups work in the documentation.

@hk2013 wrote:

data one;
input ID   Date total predicted;
cards;
1              6/24/18    200    250
1             6/30/18    200    270
1              6/18/20    200   230 
2               5/24/18     160   200
2             5/27/18     160   210
2             5/22/18     160   170
3          5/20/18     300   450
3           5/14/18     300   400
3            5/4/18     300     425
;

i want to pick the first date for each ID and subtract that date and IDs Predicted from Total

Re: find first id and date

jebjur — Mon, 02 Jul 2018 17:58:13 GMT

This code should give you the results you want:

data two (drop=temp);
set one;
by ID;
retain temp;
if first.id then temp=total;
else if last.id then do;
diff=temp-predicted;
output;
end;
run;