DATA Step, Macro, Functions and more

minimum of a column grouped by another column

Accepted Solution Solved
Reply
Contributor
Posts: 52
Accepted Solution

minimum of a column grouped by another column

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


Accepted Solutions
Solution
‎08-08-2017 07:41 AM
Respected Advisor
Posts: 4,930

Re: minimum of a column grouped by another column

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;
PG

View solution in original post


All Replies
Super User
Posts: 5,435

Re: minimum of a column grouped by another column

Your first SQL looks fine. But why do you switch to a data step (and mix with SQL syntax - which isn't supported)?
Data never sleeps
Solution
‎08-08-2017 07:41 AM
Respected Advisor
Posts: 4,930

Re: minimum of a column grouped by another column

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;
PG
Contributor
Posts: 52

Re: minimum of a column grouped by another column

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

Contributor
Posts: 52

Re: minimum of a column grouped by another column

Sorry, I forgot a comma after line 24 above. Thanks for the help.
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 147 views
  • 0 likes
  • 3 in conversation