topic Re: Proc SQL/SAS: How to select Lowest record number from output? in SAS Programming

Proc SQL/SAS: How to select Lowest record number from output?

jerrylshen — Fri, 09 Aug 2019 14:11:53 GMT

Given an input table, how to only select the lower RecordNumber of the same subject?

Sample Input Data:

╔═════════╦═══════╦═════════╗
║ Subject ║ Time1 ║ Record ║
╠═════════╬═══════╬═════════╣
║ 1 ║ 11:13 ║ 1 ║

║ 1 ║ 11:13 ║ 2 ║

║ 2 ║ 11:17 ║ 5 ║

║ 2 ║ 11:17 ║ 6 ║
╚═════════╩═══════╩═════════╝

Output Data:

╔═════════╦═══════╦═════════╗
║ Subject ║ Time1 ║ Record ║
╠═════════╬═══════╬═════════╣
║ 1 ║ 11:13 ║ 1 ║
║ 2 ║ 11:17 ║ 5 ║
╚═════════╩═══════╩═════════╝

For Subject 1, Record 1 < Record 2. For Subject 2, Record 5 < Record 6

I tried doing something like this to first get the lowest RecordNumber and then trying to filter out the input data, but it still outputs essentially everything, unless I did something wrong here.

CREATE TABLE recordtemp AS
SELECT DISTINCT Subject, min(RecordPosition) as RecordPosition,
FROM output1
;

m open to either proc sql/SAS advices, but I am more experienced in SQL but I can't think of a way for this.

Thanks!

Re: Proc SQL/SAS: How to select Lowest record number from output?

PaigeMiller — Fri, 09 Aug 2019 14:14:41 GMT

/**** UNTESTED CODE *****/

proc sql;
    create table recordtemp as select * from output1
        group by subject having record=min(record);
quit;

Re: Proc SQL/SAS: How to select Lowest record number from output?

jerrylshen — Fri, 09 Aug 2019 14:18:11 GMT

Accepting this answer, but I actually found this post https://communities.sas.com/t5/SAS-Programming/Proc-SQL-query-Select-Minimum-Record/td-p/296572 like right before you replied lol but thanks for the quick reply!

Re: Proc SQL/SAS: How to select Lowest record number from output?

millerm — Fri, 09 Aug 2019 14:23:10 GMT

If you're using proc sql Paige's is the best way to go, but I normally find with larger datasets a proc summary works a bit better, and is irrespective of other variables, if that's of use.

proc summary data=your_data;
by descending subject time1;
var record;
output out=your_output_dataset min=;
run;

That should work!