topic Re: SQL, Select the best line in SAS Programming

SQL, Select the best line

WilliamB — Fri, 28 Feb 2020 14:06:55 GMT

Hello,

I would like to select the largest week per month and divide QCNUM / QCDEN into percentages.

In SQL preferably.

Thanks for your help

Table

COMOIS	COSEM	COESVD	QCNUM	QCDEN
202001	202004	3754	12948	24086
202001	202002	3754	12951	24088
202001	202005	3754	12950	24088
202001	202003	3754	12950	24076
202001	202001	3754	12886	24079
202002	202007	3754	12923	24147
202002	202006	3754	12956	24152
202002	202008	3754	12862	24149


Want
COMOIS	COSEM	COESVD	QCNUM	QCDEN	taux
202001	202005	3754	12950	24088	54%
202002	202008	3754	12862	24149	53%

Re: SQL, Select the best line

PaigeMiller — Fri, 28 Feb 2020 14:09:29 GMT

I think you need to define the problem more completely for us.

What is the criterion to determine the "largest week"? Also, there appear to be two different columns that could be month, which are you referring to?

Re: SQL, Select the best line

WilliamB — Fri, 28 Feb 2020 14:12:18 GMT

The largest number, from the COSEM column

Re: SQL, Select the best line

PaigeMiller — Fri, 28 Feb 2020 14:21:52 GMT

UNTESTED CODE

proc sql;
    create table want as select *,qcnum/qcden as taux format=percent8.0
    from have group by comois having cosem=max(cosem);
quit;