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
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;
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;
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
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: