calculate median by year and country

[Satori]

Hi, I want to calculate the median by YEAR and COUNTRY, for two variables and then use this median in the next step to create a new variable. I'm using sql to do this, but something is off. Below is my code:

proc sql noprint; select YEAR, CNTRY, XREV, median(XREV) as medr from dataset; group by YEAR, CNTRY order by YEAR, CNTRY;
proc sql noprint; select YEAR, CNTRY, XASSTS, median(XASSTS) as meda from dataset; group by YEAR, CNTRY order by YEAR, CNTRY;
data want; set have; outr=ln(XREV/medr); outa=ln(XASSTS/meda);

This is part of the log file:

NOTE: The query requires remerging summary statistics back with the original data.
1133     !                                                                                    group by YEAR, CNTRY order by YEAR, CNTRY;
                                                                                              _____
                                                                                              180
ERROR 180-322: Statement is not valid or it is used out of proper order.

Can anyone give me some help here?

[Kurt_Bremser · Accepted solution]

Syntax ERROR happens because you ended the SELECT with a semicolon before the GROUP BY.

The remerge will still happen because you have a variable in the SELECT which is not part of the GROUP BY. Remove the variables from the SELECT:

proc sql;
create table want as
  select
    YEAR, 
    CNTRY,
    median(XREV) as medr,
    ln(median(XREV)) as outr,
    median(XASSTS) as meda,
    ln(median(XASSTS)) as outa
  from dataset
  group by YEAR, CNTRY
;
quit;

[JosvanderVelden]

Please read some papers and the documentation.

/* THE SELECT STATEMENT SAMPLE SYNTAX */
PROC SQL options;
   SELECT column(s)
   FROM table-name
   WHERE expression
   GROUP BY column(s)
   HAVING expression
   ORDER BY column(s);
QUIT;

A good paper to start with is: https://support.sas.com/resources/papers/proceedings/proceedings/sugi29/268-29.pdf