last observation to keep

[Mark7]

Hi

I have dataset with ID, datetime, group, colour

ID                       datetime                  group            colour

Z67897F     26Nov2017:09:26:00          Fiat           white

Z67897F     26Nov2018:12:26:00          Fiat           red

Z67897F     01May2016:12:26:00         Ford          black

Z67897F     01May2016:12:48:00         Ford          purple

D45671Z    26Dec2017:09:26:00          Toyota       yellow

D45671Z    26Dec2017:10:26:00          Toyota       black

D45671Z    26Jan2019:09:26:00          Buick         purple

.

.

Z678B7F     09Jun2018:09:26:00          Lexus           white

I can very  easy to click and drag this report in EG. But is a way to get result using data step,

to get for every ID one row with last result for each group and colour

I have many datasets and number of groups differs. Is a way to get as many columns as number of groups?

EG

ID            Fiat last datetime               colour       ford_last_datetime            colour      Toyota last dateti         colour

Z67897F    26Nov2018:12:26:00        red          01May2016:12:48:00       purple           Null                             Null

D45671Z      Null                                 Null                 Null                           Null           26Dec2017:10:26:00     black

....

[PaigeMiller]

Is this for some display table, or for perhaps export to Excel?

Those would be good reasons, but if you are doing this for further analysis, I think this is a poor format for additional analysis.

[Mark7]

It is only for management, display table. Table contains over 20 mln rows

[Reeza]

This will be a very sparse data set...that being said:

1. Use LAST. to keep last record for each ID/GROUP
2. Use PROC TRANPOSE twice to flip to a wide format, once for colour and once for group.
3. Merge the results by ID.
2.

[Mark7]

I am SAS EG drag and drop user ana found a problem to get proper solution  

I prepared dataset

data example;
input ID $ DateTime group $ colour $;
informat DateTime datetime19. ;
format DateTime datetime19. ;
datalines;
Z67897F 11SEP18:06:12:00 Fiat white
Z67897F 26Nov18:12:26:00 Fiat red
Z67897F 01May16:12:26:00 Ford black
Z67897F 01May16:12:48:00 Ford purple
D45671Z 26Dec17:09:26:00 Toyota yellow
D45671Z 26Dec17:10:26:00 Toyota black
D45671Z 26Jan19:09:26:00 Buick purple
Z678B7F 09Jun18:09:26:00 Lexus white
;
run;

any hint how I can get 

ID	DateTime	group	colour
D45671Z	26Jan2019 9:26:00	Buick	purple
D45671Z	26Dec2017 10:26:00	Toyota	black
Z67897F	26Nov2018 12:26:00	Fiat	red
Z67897F	01May2016 12:48:00	Ford	purple
Z678B7F	09Jun2018 9:26:00	Lexus	white

[Mark7 · Accepted solution]

Something like this?

proc sort data=example;
by Id group DateTime;
run;

data get_last;
Set example;

by ID group DateTime;
if last.group ;

run;