Desktop productivity for business analysts and programmers

find first id and date

Reply
Contributor
Posts: 42

find first id and date

[ Edited ]

 

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 

 

Respected Advisor
Posts: 3,275

Re: find first id and date

Show us the desired result. 

--
Paige Miller
Contributor
Posts: 42

Re: find first id and date

[ Edited ]
Posted in reply to PaigeMiller

 

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
Super User
Posts: 24,004

Re: find first id and date

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&docsetVer...

 

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 

 


 

SAS Employee
Posts: 19

Re: find first id and date

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;

Ask a Question
Discussion stats
  • 4 replies
  • 135 views
  • 0 likes
  • 4 in conversation