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?
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;
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;
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.