topic Re: last observation to keep in SAS Enterprise Guide

last observation to keep

Mark7 — Mon, 07 Oct 2019 22:18:32 GMT

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?

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

....

Re: last observation to keep

PaigeMiller — Mon, 07 Oct 2019 22:39:13 GMT

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.

Re: last observation to keep

Mark7 — Mon, 07 Oct 2019 22:44:26 GMT

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

Re: last observation to keep

Reeza — Mon, 07 Oct 2019 22:55:25 GMT

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.

Re: last observation to keep

Mark7 — Tue, 08 Oct 2019 13:58:36 GMT

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

Re: last observation to keep

Mark7 — Tue, 08 Oct 2019 14:21:20 GMT

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;