Kurt, How do you give the output a label. Currently it comes back as _TEMG001. Thanks. ... View more

Thanks Kurt,   I tried the following code - notice group by is corrected.   proc sql; create table fault_count as select fault_code, count(*) from all_units_results group by fault_code; quit; It works well. Thank you very much. capam   ... View more

Thanks Tom, this is very helpful and may be a partial solution. capam ... View more

Hi Ballardw,   Excellent comment.   Have table with var1, var2, var3, var4, var5.   var1 & var3 have limits and var3 has the minimum value output.   grouped by var2.   var2 is unique/distinct so that the minimum value of var3 is in reference to each var2.   Hope that is more clear.   Thanks again. ... View more

Thanks for your comments, however, the code below generates nearly 10M rows of data where the unique vehicle_no should be ~40. 'distinct' doesn't appear to be operating on vehicle_no.    proc sql; create table InstallDate as SELECT distinct FLT.vehicle_no, FLT.vehicle_header, FLT.occur_date, FLT.FAULT_CODE, FLT.FAULT_DESCRIPTION, datepart(min(FLT.occur_date)) as sw_install_date format date9. /*min(FLT.occur_date) as sw_install_date format dtdate9.*/ FROM RMDEOAP.GETS_DW_EOA_FAULTS FLT WHERE FLT.vehicle_header = 'NS' and datepart(FLT.occur_date) between '01Sep2016'd and datetime() /*FLT.occur_date between '25Jul2017:00:00'dt and datetime()*/ group by FLT.vehicle_no ; quit; ... View more

Thanks for the comment. It doesn't appear to work in this case. The indented code is below:   proc sql; create table InstallDate as SELECT FLT.vehicle_header, distinct FLT.vehicle_no, FLT.occur_date, FLT.FAULT_CODE, FLT.FAULT_DESCRIPTION, datepart(min(FLT.occur_date)) as sw_install_date format date9. /*min(FLT.occur_date) as sw_install_date format dtdate9.*/ FROM RMDEOAP.GETS_DW_EOA_FAULTS FLT WHERE FLT.vehicle_header = 'NS' and datepart(FLT.occur_date) between '01Sep2016'd and datetime() /*FLT.occur_date between '25Jul2017:00:00'dt and datetime()*/ group by FLT.vehicle_no ; quit; ... View more

novinosrin, Thanks for the quick response. I tried your suggestion and got the following:   16 proc sql; 17 create table InstallDate as 18 SELECT 19 FLT.vehicle_header, 20 distinct FLT.vehicle_no, ___ 22 ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, (, *, **, +, ',', -, /, <, <=, <>, =, >, >=, ?, AND, BETWEEN, CONTAINS, EQ, EQT, FROM, GE, GET, GT, GTT, LE, LET, LIKE, LT, LTT, NE, NET, OR, ^=, |, ||, ~=. 21 FLT.occur_date, 22 FLT.FAULT_CODE, 23 FLT.FAULT_DESCRIPTION, 24 datepart(min(FLT.occur_date)) as sw_install_date format date9. 25 /*min(FLT.occur_date) as sw_install_date format dtdate9.*/ 26 FROM RMDEOAP.GETS_DW_EOA_FAULTS FLT 27 WHERE 28 FLT.vehicle_header = 'NS' and 29 datepart(FLT.occur_date) between '01Sep2016'd and datetime() 30 /*FLT.occur_date between '25Jul2017:00:00'dt and datetime()*/ 31 group by FLT.vehicle_no 32 ; NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements. 33 quit; ... View more

Thanks. This works well. ... View more

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

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 ... View more