Hi,   So I have a large administrative school dataset where I want to delete and replace the last 30 days of data.    One varable called "date" which is date9 format (DDMONYYYY) is giving me some havoc during this process. For some reason, "date" in the base dataset gets changed to datetime20 format during the below proc sql command, and then the data for the "date" variable in the appending 30 days dataset is entered improperly because the two date formats no longer match.    I really don't know why this would be occuring.    I have double checked all the date formating up to the following SAS syntax, and the date format is Date9. in all tables up to this point. After this syntax is run, it becomes datetime20 and all dates from the last 30 days are entered as "01JAN1960:05:47:52".     proc sql; connect to odbc (dsn="SCHEMA" user=USERNAME password=PASSWORD); execute ( delete from "school_rp" WHERE event_reported_datetime >= to_date(%BQUOTE('&last30days'),'DDMONYYYY') ) by odbc; INSERT into schema.school_rp Select * from temp.school_rp_last30; disconnect from odbc; quit; Any ideas on what's happening? ... View more

Hi All,   So I am working with two different datasets which have data coming in from multiple facilities. Both datasets report much of the same information, but have some differences (including entries that are available in one, but not the other). We are currently working on switching from using one of these datasets to the other for a number of products, and I am having to perform some QA on them.    I want to create two subsets of the data, where I only pull entries that occur in both datasets (and would also like to do the opposite, to determine which data is appearing in one but not the other and why).   Each entry has a "unique" identifier number, but unfortunately they can appear more than once because each of the facilities generates this number indipendantly of any other facility, and sometimes they end up being the same by chance. Because if this, I can't simply match based on the "unique" identifier alone.    I used the following code to match based on the identifier, but need to tell it to also match the facility WITHIN THE SAME ROW: proc sql noprint; create table SET1_MATCHED as select * from ATDED.SET1 where EPISODE_NR in (select EPISODE_NR from SET2) by facility ; quit; proc sql noprint; create table SET2_MATCHED as select * from ATDED.SET2 where EPISODE_NR in (select EPISODE_NR from SET1) by facility ; quit; Unfortunately, I can't simply add "and FACILITY in(select FACILITY from SET1/2)" because it won't search within the same row, it will just check if the facility is within the other data set at all, and therefore produces identical subsets of the data to ust excluding this line.    Any suggestions?   (Note: I could approach this a little differently and merge the datasets, but would prefer to keep them separate if possible. I could also add in a "and FACILITY = facility_name" and create a subset for each facility and append the sets back together, but there are a lot of facilities and I figured there had to be a more efficient way. ).  ... View more

Hi All,   So I have what I feel like should be a simple task, but I just can't figure it out.    I am working with very large dataset stores on Oracle servers and need to use the data for a variety of Tableau dashboards. The datasets are so large that working off a live feed from the Oracle servers isn't feasible, so we have to create subsets of the data and either connect live to those, or create extracts.    What I would like to do is set it up so that every time my code runs it selects the last 1 year of data, relative to the date that the code is run. Right now it is set up to pull anything over a pre-specified date, but obviously as the date gets further away the larger the dataset becomes and I don't want to have to worry about going in and changing it overy so often.    Here is the code I tried: data SUBSET; set ORIGINAL; where EVENT_REPORTED_DATETIME >= (%sysfunc(DATETIME(),datetime20.) -365); run;   It results in the following error: NOTE: Line generated by the macro function "SYSFUNC". 1 07DEC2016:11:10:26 ------- 22 76 ERROR: Syntax error while parsing WHERE clause. ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, ), *, **, +, -, /, <, <=, <>, =, >, >=, ?, AND, BETWEEN, CONTAINS, EQ, GE, GT, IN, IS, LE, LIKE, LT, NE, NOT, NOTIN, OR, ^, ^=, |, ||, ~, ~=. ERROR 76-322: Syntax error, statement will be ignored.   I tried a few other similar methods, but they either returned no observations or all observations.      ... View more