We still are not seeing how the value is being stored in your work data set.  Need to see the value of hos_date, in addition to currdate.  Try adding the following statements to the data step:      put hos_date= ;    * uses data set format of hos_date ;       put hos_date= date9. ; * format as a date;      put hos_date=  16.14 ;  * format as number not date ; Also please run a proc contents on your working data set and look at the format associated with hos_date.  If there is no format, you may need to add a date format so that SAS knows what to do when it writes the data it sends to oracle. Also run proc contents on the oracle table, Perhaps it has hos_date formatted as a string and not a date value.In which case, you would need to store currdate as a string in the work data set. ... View more

Hi Fleroo, First some points about how both SAS and Oracle store dates.  Sas stores dates as numeric value representing the number of days since 1/1/1960.  I believe oracle also stores as numeric count with a different base date but I could be wrong.  Also Unix does the same but again with a different reference point. Here is what I think is happening: 1.  Macro compiler: The statement HOS_DATE=&curdate; is resolved to HOS_DATE = 07/19/2013 ; by the macro compiler. 2.  The data step compiler sees HOS_DATE = 07/19/2013 ; and assumes HOS_DATE is a numeric variable because it does not see a quoted literal.  It then performs the arithmetic operation of 7 DIVIDED BY 19 DIVIDED BY 2013.  This results in 0.0001931887. 3.  When SAS is communicating to oracle, it takes its internal value and formats as a date string which Oracle can interpret.  Since SAS dates are stored as the number of days since 1/1/1960, a value of 0 ==> 1/1/1960, which is what is sent to Oracle. To fix, change your assignment statement to either: 1. HOS_DATE="&curdate"d;  Note the double quotes are important to get the macro variable resolved and quoting a date-like string and appending the character d at the end tells SAS this is a date value. 2. Use built-in sas function and avoid system dependent commands:    HOS_DATE = today() ; Note ; if you want to check what the internal value of HOS_date is in your work data set run the following code: data _null_ ;      set work.safety_hrs;      put hos_date= ;    * uses data set format of hos_date ;      put hos_date= date9. ; * format as a date;      put hos_date=  16.14 ;  * format as number not date ;      run ; Some reading that might be helpful would be to look at the SAS 9.X Language Reference Concepts document for your release of SAS -- chapter 2 and 7 in particular.  Here is link for 9.2 pdf version, http://support.sas.com/documentation/cdl/en/lrcon/62955/PDF/default/lrcon.pdf Hope this helps. ... View more

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

Based on your code I suspect you are trying to run this in a different SQL dialect than proc sql.  Your use of the decode and lpad functions suggests that to me If you are running in a different SQL dialect, 'group by' aggregation works a little differently.  This has caused me confusion in the past. Some other dialects of sql do not allow you to have any fields in the select clause except for the group by variables and aggregate functions.  SAS Proc SQL lets you do this.  So as  examples, the following queries will work in SAS SQL but not some other dialects: create table count as select a          ,b         ,count(*) as cnt   from myTable group by a ; create table dups as select *   from myTable group by a having count(*) > 1; If there is no need to do in another SQL dialect then you can do this within SAS, but some of the functions will need to be converted. If you need to do this in another SQL dialect, you will need to do some variation on AnotherDream's last suggestion.  Two ways of doing this are 1.  Join the original table with a sub-query of the original table with the group by in the sub-query 2. Create a temporary table from the group by and then join temporary table to original table. Hope this helps. ... View more

Peter, Not sure what results you see (you're holding your cards clase to your vest).  b But when I try what you suggest on AIX system using sas 9.2, I see not differences between dictionary and sashelp views.  I get the same results with pc-sas -- no measurable difference. Of course this was q quick and dirty test using some code on which I am currently working. Here is the log for everyone to examine and comment on. Larry   NOTE: Remote submit to MARYKAY commencing. 　 1301 proc sql stimer; NOTE: SQL Statement used (Total process time): real time 0.00 seconds cpu time 0.00 seconds 1302 1303 create table work_peims_list as 1304 select memname 1305 ,nobs 1306 ,nvar 1307 from sashelp.vtable 1308 where libname = 'WORK' 1309 and memname like 'PEIMS%' 1310 order by memname 1311 ; NOTE: SAS threaded sort was used. NOTE: Table WORK.WORK_PEIMS_LIST created, with 7 rows and 3 columns. NOTE: SQL Statement used (Total process time): real time 0.02 seconds cpu time 0.02 seconds 1312 create table class as 1313 select * 1314 from sashelp.class ; NOTE: Table WORK.CLASS created, with 19 rows and 5 columns. NOTE: SQL Statement used (Total process time): real time 0.01 seconds cpu time 0.00 seconds 1315 1316 create table perm_peims_list as 1317 select memname 1318 ,nobs 1319 ,nvar 1320 from sashelp.vtable 1321 where libname = 'PERM' 1322 and memname like 'PEIMS%' 1323 order by memname 1324 ; NOTE: SAS threaded sort was used. NOTE: Table WORK.PERM_PEIMS_LIST created, with 7 rows and 3 columns. NOTE: SQL Statement used (Total process time): real time 0.02 seconds cpu time 0.01 seconds 1325 1326 create table class as 1327 select * 1328 from sashelp.class ; NOTE: Table WORK.CLASS created, with 19 rows and 5 columns. NOTE: SQL Statement used (Total process time): real time 0.00 seconds cpu time 0.01 seconds 1329 1330 create table perm_drop_columns as 1331 select memname 1332 ,name 1333 from sashelp.vcolumn 1334 where libname = 'PERM' 1335 and memname like 'PEIMS%' 1336 and upcase(name) in ('STUDENTID' 1337 ,'SSN_ALTID' 1338 ,'LOCALID' 1339 ,'FNAME' 1340 ,'MNAME' 1341 ,'LNAME' 1342 ,'GEN' 1343 ,'BIRTHDATE' 1344 ) 1345 order by memname 1346 ; NOTE: SAS threaded sort was used. NOTE: Table WORK.PERM_DROP_COLUMNS created, with 14 rows and 2 columns. NOTE: SQL Statement used (Total process time): real time 0.01 seconds cpu time 0.01 seconds 1347 1348 create table class as 1349 select * 1350 from sashelp.class ; NOTE: Table WORK.CLASS created, with 19 rows and 5 columns. NOTE: SQL Statement used (Total process time): real time 0.00 seconds cpu time 0.00 seconds 1351 1352 1353 create table work_peims_list as 1354 select memname 1355 ,nobs 1356 ,nvar 1357 from dictionary.tables 1358 where libname = 'WORK' 1359 and memname like 'PEIMS%' 1360 order by memname 1361 ; NOTE: SAS threaded sort was used. NOTE: Table WORK.WORK_PEIMS_LIST created, with 7 rows and 3 columns. NOTE: SQL Statement used (Total process time): real time 0.01 seconds cpu time 0.02 seconds 1362 create table class as 1363 select * 1364 from sashelp.class ; NOTE: Table WORK.CLASS created, with 19 rows and 5 columns. NOTE: SQL Statement used (Total process time): real time 0.00 seconds cpu time 0.00 seconds 1365 1366 create table perm_peims_list as 1367 select memname 1368 ,nobs 1369 ,nvar 1370 from dictionary.tables 1371 where libname = 'PERM' 1372 and memname like 'PEIMS%' 1373 order by memname 1374 ; NOTE: SAS threaded sort was used. NOTE: Table WORK.PERM_PEIMS_LIST created, with 7 rows and 3 columns. NOTE: SQL Statement used (Total process time): real time 0.01 seconds cpu time 0.01 seconds 1375 1376 create table class as 1377 select * 1378 from sashelp.class ; NOTE: Table WORK.CLASS created, with 19 rows and 5 columns. NOTE: SQL Statement used (Total process time): real time 0.00 seconds cpu time 0.00 seconds 1379 1380 create table perm_drop_columns as 1381 select memname 1382 ,name 1383 from dictionary.columns 1384 where libname = 'PERM' 1385 and memname like 'PEIMS%' 1386 and upcase(name) in ('STUDENTID' 1387 ,'SSN_ALTID' 1388 ,'LOCALID' 1389 ,'FNAME' 1390 ,'MNAME' 1391 ,'LNAME' 1392 ,'GEN' 1393 ,'BIRTHDATE' 1394 ) 1395 order by memname 1396 ; NOTE: SAS threaded sort was used. NOTE: Table WORK.PERM_DROP_COLUMNS created, with 14 rows and 2 columns. NOTE: SQL Statement used (Total process time): real time 0.01 seconds cpu time 0.02 seconds 1397 create table class as 1398 select * 1399 from sashelp.class ; NOTE: Table WORK.CLASS created, with 19 rows and 5 columns. NOTE: SQL Statement used (Total process time): real time 0.00 seconds cpu time 0.00 seconds 1400 1401 1402 create table work_peims_list as 1403 select memname 1404 ,nobs 1405 ,nvar 1406 from sashelp.vtable 1407 where libname = 'WORK' 1408 and memname like 'PEIMS%' 1409 order by memname 1410 ; NOTE: SAS threaded sort was used. NOTE: Table WORK.WORK_PEIMS_LIST created, with 7 rows and 3 columns. NOTE: SQL Statement used (Total process time): real time 0.01 seconds cpu time 0.01 seconds 1411 create table class as 1412 select * 1413 from sashelp.class ; NOTE: Table WORK.CLASS created, with 19 rows and 5 columns. NOTE: SQL Statement used (Total process time): real time 0.00 seconds cpu time 0.00 seconds 1414 1415 create table perm_peims_list as 1416 select memname 1417 ,nobs 1418 ,nvar 1419 from sashelp.vtable 1420 where libname = 'PERM' 1421 and memname like 'PEIMS%' 1422 order by memname 1423 ; NOTE: SAS threaded sort was used. NOTE: Table WORK.PERM_PEIMS_LIST created, with 7 rows and 3 columns. NOTE: SQL Statement used (Total process time): real time 0.01 seconds cpu time 0.02 seconds 1424 1425 create table class as 1426 select * 1427 from sashelp.class ; NOTE: Table WORK.CLASS created, with 19 rows and 5 columns. NOTE: SQL Statement used (Total process time): real time 0.00 seconds cpu time 0.01 seconds 1428 1429 create table perm_drop_columns as 1430 select memname 1431 ,name 1432 from sashelp.vcolumn 1433 where libname = 'PERM' 1434 and memname like 'PEIMS%' 1435 and upcase(name) in ('STUDENTID' 1436 ,'SSN_ALTID' 1437 ,'LOCALID' 1438 ,'FNAME' 1439 ,'MNAME' 1440 ,'LNAME' 1441 ,'GEN' 1442 ,'BIRTHDATE' 1443 ) 1444 order by memname 1445 ; NOTE: SAS threaded sort was used. NOTE: Table WORK.PERM_DROP_COLUMNS created, with 14 rows and 2 columns. NOTE: SQL Statement used (Total process time): real time 0.01 seconds cpu time 0.01 seconds 1446 quit ; NOTE: PROCEDURE SQL used (Total process time): real time 0.00 seconds cpu time 0.00 seconds 　 NOTE: Remote submit to MARYKAY complete. ... View more

I meant to add a liitle sample code. Assuming windows OS, this code will be a data set with list of filenames in a directory filename my_files pipe 'dir /b c:\my_directory' ; data file_list ;   infile my_files ;   input file_name ; run ; ... View more