Add the group by statement to your sql code.
If you're doing in PROC SQL without pass through
proc sql;
create table top_keys as
select key, max(id) as max_value
from have
group by id;
quit;
I don't know why Peter is using the having clause, but I don't think you need it unless you want the max for each group in each row as well. If you do then can remerge in a second sql step to avoid the issue Peter has mentioned.
Generally, any variables in the group by statement can be included in the select statement.
Hi,
I think I have the same question and similar code but it does not seem to be working.
PROC SQL;
CREATE TABLE WORK.TD_TST AS
SELECT t1.id,
t1.read_time,
(MAX(t1.read_time)) FORMAT=DATETIME22.3 AS MAX_of_read_time,
t1.pmax_derated,
/* start_date */
(intnx('HOUR',TODAY(),-12)
) LABEL="start_date" AS start_date
FROM PBGMES.td_header t1
WHERE
t1.id ='1...4'
GROUP BY t1.sub_id,
t1.pmax_derated,
(CALCULATED start_date)
/* having t1.read_time = (MAX(t1.read_time))*/
;
QUIT;
id read_time max_read_time pmax_derated start_date
130716041724 17JUL2013:13:07:49.000 17JUL2013:13:07:49.000 0 -25200
130716041724 17JUL2013:14:54:01.000 17JUL2013:14:54:01.000 89.420417786 -25200
Thanks,
saspert
I am not sure why you believe the query is not working, I see two records with the same sub_id and different pmax_derated values, so group by could be working. But I wonder if the calculation of start_date is what you intended.
I see that you use 'hour' as the interval in intnx and then specify today's date as the start-from.. The today() function returns a date value; so there is a mis-match here.
If you are trying to specify 12:00 noon yesterday as the start time, then you will need something like (intnx('HOUR',dhms(TODAY(),0,0,),-12) The DHMS converts today's date value to a datetime value which the 'HOUR' interval will work on.
Hope this helps.
I just followed my colleagues without thinking completely. I think if I remove the field pmax_derated in the group by field, I get what I want - single record for sub_id.
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Join us for two new fee-based courses: Administrative Healthcare Data and SAS via Live Web Monday-Thursday, April 24-27 from 1:00 to 4:30 PM ET each day. And Administrative Healthcare Data and SAS: Hands-On Programming Workshop via Live Web on Friday, April 28 from 9:00 AM to 5:00 PM ET.