Dear fellow SAS-users, This is very likely to be a dreadfully unintelligent question on my part with a (hopefully) trivial answer, but please bear with me as I am mainly used to other programming languages and failry new to SAS. Altough the actual problem is somewhat more elaborate, I can simplify and reduce it to the following: I have table t1 containing a very large list of individuals and the dates they were on holiday in the format [ID] [HOLIDAY_STARTDATE] [HOLIDAY_ENDDATE]. In table t2, I have a list of every date in a given year with a column of 0's, as in [DATE_DAY][ONHOLIDAY]. Now what I would like to do is 1) check for each individual in t1 and each dat in t2 whether or not the particular date is within the range of an individual's holiday and if so 2) set the corresponding [ONHOLIDAY] value to 1. The end product should look something like this: [ID] [DATE] [ONHOLIDAY] 1 01/01/2014 0 1 02/01/2014 0 1 03/01/2014 1 1 04/01/2014 1 1 05/01/2014 0 ... ID 31/12/2014 0 Effectively creating 365/366 entries for each individual having a 1 for each day they were on holiday. Now I did try the following (although quite amateuristic) in PROC SQL, given the "zero-vector" per day (t1) and individual holiday dates (t2): proc sql; create table t3 as select distinct ID, DATE, case when DATE between HOLIDAY_STARTDATE and HOLIDAY_ENDDATE then 1 else 0 end as ONHOLIDAY from t1, t2 group by DATE having (ONHOLIDAY = max(ONHOLIDAY)) order by DATE, ONHOLIDAY; quit; This works fine for small numbers of individuals, however when attempting to run this against the large database I have, I get error 5430 (http://support.sas.com/kb/5/430.html) , essentially telling me my query was too complex (note that I cannot change the cfg-files on my company laptop so I am stuck with this error unless I can optimize the query, also compression did not help). Now, I would like to know whether there is an alternative to this. I am used to fairly easily writing loops and do work on matrices for this kind of simple problem (i.e. MATLAB), but SAS necessitates a different way of thinking, hence my question. Any suggestions are more than welcome! Thanks a lot in advance!
... View more