topic Re: Selecting First from Multiple List in SAS Programming

Selecting First from Multiple List

BCNAV — Thu, 15 Nov 2018 01:33:28 GMT

We have flight data that looks like:

SPECIALTY     FLT_BIZ_UID    FIR_NAME     DISTANCE
1             1234567        MONCTON      25
2             1234567        MONCTON      40
3             1234567        MONCTON      60
4             1234567        TORONTO      25
5             1234567        TORONTO      40
6             1234567        TORONTO      80
8             1234567        VANCOUVER    25
7             1234567        VANCOUVER    40
6             1234567        VANCOUVER    80

I would like to be able to select the first entry for each flight (flt_biz_uid) as it enters a different FIR (flight information region).

So the output would be:

FLT_BIZ_UID    FIR_NAME
1234567        MONCTON
1234567        TORONTO
1234567        VANCOUVER

All this is doing is getting a track of a flight as it moves into different FIR regions (there can be many flights...many flt_biz_uid). I had tried using .first, but could not get the syntax to work.

As a part two, is it possible to do the same thing but add a total distance column for each FIR? For example:

FLT_BIZ_UID    FIR_NAME   DISTANCE
1234567        MONCTON    125
1234567        TORONTO    145
1234567        VANCOUVER  145

thanks in advance

Re: Selecting First from Multiple List

Reeza — Thu, 15 Nov 2018 01:48:38 GMT

Have you tried BY groups with the FIRST. logic?

https://onlinecourses.science.psu.edu/stat481/node/9/

Re: Selecting First from Multiple List

novinosrin — Thu, 15 Nov 2018 01:51:25 GMT

part1

by FLT_BIZ_UID FIR_NAME;

if first.FIR_NAME;

part 2:

proc means/summary/sql/datastep by group sum

Re: Selecting First from Multiple List

Reeza — Thu, 15 Nov 2018 01:52:14 GMT

Part 2 would generate part 1 in your sample data, but I expect that won't scale.

Re: Selecting First from Multiple List

Astounding — Thu, 15 Nov 2018 03:29:52 GMT

It's probably just a coincidence that the FIR_NAMEs are in alphabetical order here. They won't have to be if you use:

proc summary data=have;

by flt_biz_uid fir_name notsorted;

var distance;

out=want (drop=_: ) sum=;

run;

Re: Selecting First from Multiple List

BCNAV — Thu, 15 Nov 2018 03:49:35 GMT

The following seems to work....should be ok?

data EGTASK.FDE_FIR_CLEAN; set EGTASK.FDE_FIR_CLEAN_TMP;

by FLT_BIZ_UID FIR_NAME;

if first.FIR_NAME then output;

run;

Re: Selecting First from Multiple List

error_prone — Thu, 15 Nov 2018 06:19:21 GMT

Seems to be ok. Don't use all updase names, they decrease readability.

Re: Selecting First from Multiple List

Astounding — Thu, 15 Nov 2018 15:18:40 GMT

That works or part 1 of your question, as long as the data set is in order that matches your BY statement.