On the concept of "extract them first": The simplest way is to use proc copy to copy the entire tables. You would want to do that with SID.SID_Person_Demo and Prod.Spraddr. Then use a data step or proc sql to copy the data from SID.SID_Enrollments_All_Vw with Where TERMID IN ('201905','201908','202001') and BENCH='B'; Since all the data is now in SAS, the join should be fast. ... View more

Are these non SAS tables, I.e in Oracle or some other database? If so, you are doing what is called a cross-sever join. You may consider using pass-through to join the tables in the background database server. In that case, you would need that one of the Oracle schemes be granted access to the other. ... View more

I dug out a very old macro ...done in SAS 9.1.3!  It was made to be used inside a DI Studio transformation, but it should run stand-alone! If I understand your description correctly, it should do exactly what you need.    It may use code that could be done more efficiently or more concisely with features from later SAS releases.    Let me know if it works for you.   If this is useful, then I could document it better     (I posted in the wrong place before.   sorry) Here is the macro header:     %macro ut_consolidate_periods (indata = , /* input table */ outdata = , /* result table */ view = VIEW, /* Result is VIEW or dataset */ fromdt = effective_from_date, /* date range start is in this column */ todate = effective_end_date, /* date range end is in this column */ compvar_list = , /* Columns to used to compare rows to determine if a difference exists */ sortkey = , /* List of one or more columns that are the keys of the table */ additional_keepvars = , /* Columns that are not keys and are not used for detecting differences + These columns are also kept in the result - the values are taken from + the last row in each date range */ firstrowvars = , /* Columns that are not keys and are not used for detecting differences + These columns are also kept in the result - the values are taken from + the first row in each date range - note that the firstrowvars= option + uses more resources than the additional_keepvars= parameter. if values + are constant in a date range then use additional_keepvars= instead of + firstrowvars= */ maxvalue_keepvars = , /* Column(s) that must be kept where the maximum value + for the column(s) for all consolidated rows rows */ must_sort = NO, /* If must_sort = YES then the table will be sorted by the macro */ inpwd = , /* optional: provide sas read password if one is required */ fill_gaps = NO /* =YES when gaps should be filled - ie. if two records have + dateranges that are not exactly consecutive then they will + be consolidated anyway * if the dates are actually datetimes with times at 00:00:00 * as dates are stored in Oracle, then periods may have a gap of 23:59:59*/ ); /*Module------------------------------------------------------------------------------------------ + ut_consolidate_periods - Consolidate periods in a table + The ut_consolidate_periods macro works with tables that have date range columns. + The macro identifies key values where there are no changes in the corresponding, non-key + columns that are specified. Identical and adjacent data is then consolidated into a single date range. + This macro consolidates, into a single record, consecutive date ranges for key that is passed as a parameter. + The example below shows a data table before and after the Period Consolidation transformation. + The key consists of Key1 and Key2. Begin Date and End Date specify the date range. + The important columns for consolidation are non_key1 and non_key3. + The non_key2 column is dropped from the output table because its value might not be correct + for the full, consolidated date ranges. + + Input and Output Tables Using Period Consolidation Transformation + Input Table + Key1 Key2 Begin Date End Date non_key1 non_key2(not NB) non_key3 + 1 123 A 01JAN2009 31JAN2009 21 ABC XYZ + 2 123 A 01FEB2009 15FEB2009 21 DEF XYZ + 3 123 A 16FEB2009 15JUL2009 21 DEF XYZZY + 4 123 A 16JUL2009 31OCT2009 21 BCD XYZZY + 5 123 A 01NOV2009 31DEC2009 21 CEF XYZZY + 6 125 A 01JAN2009 31AUG2009 21 ABC CCC + 7 125 A 01SEP2009 31DEC2009 22 ABC CCC + 8 126 A 01JAN2009 15FEB2009 22 FED DDD + 9 126 A 16FEB2009 31MAR2009 22 DEF DDD + 10 126 A 01AUG2009 31DEC2009 22 DEF DDD + + Output Table + Key1 Key2 Begin Date End Date non_key1 non_key3 contributing rows + from input table + 1 123 A 01JAN2009 15FEB2009 21 XYZ 1,2 + 2 123 A 16FEB2009 31DEC2009 21 XYZZY 3-5 + 3 125 A 01JAN2009 31DEC2009 21 CCC 6,7 + 4 126 A 01JAN2009 31MAR2009 22 DDD 8,9 + 5 126 A 01AUG2009 31DEC2009 22 DDD 10 + (This row is not concatenated to 9 because there is a gap between + the end date of 9 and the begin date of 10.) + If option fill_gaps = YES is used then result would consolidate rows 4 and 5: + Key1 Key2 Begin Date End Date non_key1 non_key3 contributing rows + from input table + 4 126 A 01JAN2009 31DEC2009 22 DDD 8,9,10     ... View more

I dug out a very old macro ...done in SAS 9.1.3!  It was made to be used inside a DI Studio transformation, but it should hopefully run stand-alone! If I understand your description correctly, it should do exactly what you need.    It may use code that could be done more efficiently or more concisely with features from later SAS releases.    Let me know if it works for you.   If this is useful, then I could cast a more experienced look at it.     ... View more

You could use the ut_sql_equijoin macro  that I posted to generate the conditions and potentially %ut_varlist  to generate the select statements.     ... View more

I have been using SAS for 30+ years and I never knew that, Thanks! I will modify the macro and repost. (But I need to leave to go to a drive-in Coronavirus test in 30 minutes, so that will have to wait!) ... View more