topic Re: minimum of a column grouped by another column in SAS Programming

minimum of a column grouped by another column

capam — Mon, 07 Aug 2017 20:59:38 GMT

The following code produces a 22-322 and a 76-322 syntax error:

proc sql;
	create table InstallDate as
		SELECT
			FLT.vehicle_header,
			FLT.vehicle_no,
			FLT.occur_date,
			FLT.FAULT_CODE,
			FLT.FAULT_DESCRIPTION
		FROM RMDEOAP.GETS_DW_EOA_FAULTS FLT
			WHERE 
			FLT.vehicle_header = 'NS'
			/*and FLT.occur_date between '25Sep2016:00:00:00'dt and datetime()*/
			and FLT.occur_date between '25Jul2017:00:00:00'dt and datetime()
	;
quit;
data InstallDate;
set InstallDate;
select min( occur_date) as sw_install_date
group by vehicle_no
;
run;

The error message appears following the statement:

select min( occur_date) as sw_install_date

I'm trying to find the minimum occur_date grouped by vehicle number.

Thanks and best regards.

capam

Re: minimum of a column grouped by another column

LinusH — Mon, 07 Aug 2017 21:12:47 GMT

Your first SQL looks fine. But why do you switch to a data step (and mix with SQL syntax - which isn't supported)?

Re: minimum of a column grouped by another column

PGStats — Tue, 08 Aug 2017 02:50:19 GMT

You only need a single query:

proc sql;
create table InstallDate as
SELECT
	FLT.vehicle_header,
	FLT.vehicle_no,
	FLT.occur_date,
	FLT.FAULT_CODE,
	FLT.FAULT_DESCRIPTION,
	min(FLT.occur_date) as sw_install_date
FROM RMDEOAP.GETS_DW_EOA_FAULTS FLT
WHERE 
	FLT.vehicle_header = 'NS' and
	FLT.occur_date between '25Jul2017:00:00:00'dt and datetime()
group by FLT.vehicle_no;
quit;

Re: minimum of a column grouped by another column

capam — Tue, 08 Aug 2017 10:41:25 GMT

Hi PGStats

I tried your suggestion and got the following error:

17 proc sql;
18 create table InstallDate as
19 SELECT
20 FLT.vehicle_header,
21 FLT.vehicle_no,
22 FLT.occur_date,
23 FLT.FAULT_CODE,
24 FLT.FAULT_DESCRIPTION
25 min(FLT.occur_date) as sw_install_date
26 FROM RMDEOAP.GETS_DW_EOA_FAULTS FLT
27 WHERE
28 FLT.vehicle_header = 'NS' and
29 FLT.occur_date between '25Jul2017:00:00:00'dt and datetime()
30 group by FLT.vehicle_no
31 ;
ERROR: Expression using minimum of (><) has components that are of different data types.
WARNING: A GROUP BY clause has been transformed into an ORDER BY clause because neither the SELECT clause nor the optional HAVING
clause of the associated table-expression referenced a summary function.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
32 quit;
NOTE: The SAS System stopped processing this step because of errors.

Thanks for your quick response.

capam

Re: minimum of a column grouped by another column

capam — Tue, 08 Aug 2017 11:41:02 GMT

Sorry, I forgot a comma after line 24 above. Thanks for the help.