topic SQL SAS Top % records in SAS Procedures

SQL SAS Top % records

Citrine10 — Fri, 30 Jul 2021 10:43:09 GMT

Hi there

If I have a table, how can I get the top 50% of records into one table and thereafter the bottom 50% of records in another table?

I generally use PROC SQL;

Please assist.

Thank you

Re: SQL SAS Top % records

sbxkoenk — Fri, 30 Jul 2021 10:53:14 GMT

data _NULL_;
 if 0 then set sashelp.snacks nobs=count;
 call symputx('numobsDIV2'      ,put(int(count/2),8.  ));
 call symputx('numobsDIV2_plus1',put(int(count/2)+1,8.));
 STOP;
run;
%PUT &=numobsDIV2;
%PUT &=numobsDIV2_plus1;

PROC SQL noprint;
 create table work.First_50_pct as
 select *
 from sashelp.snacks(firstobs=1 obs=&numobsDIV2.);
 create table work.Last_50_pct as
 select *
 from sashelp.snacks(firstobs=&numobsDIV2_plus1. obs=MAX);
QUIT;
/* end of program */

Re: SQL SAS Top % records

Ksharp — Fri, 30 Jul 2021 12:19:08 GMT

If you have SAS9.4 .

proc sql;
create table top_50 as
select * from sashelp.class
 having weight>=median(weight);

create table bottom_50 as
select * from sashelp.class
 having weight<median(weight);

quit;

Re: SQL SAS Top % records

ballardw — Fri, 30 Jul 2021 16:55:15 GMT

How is "top 50%" defined?

What about ties. Consider values such as

ID value

1 1

2 2

3 2

4 2

5 2

6 3

Where is "50%"?