Sorry, I don't use these hash items, however if you pop in your sql code there could be optimizations to be had on that side.  E.g. create an intermediary dataset from the big one with only ids from the small one, then merge that on. ... View more

As you are only looking at a few possibilities, maybe best to just do it with manual if statements, e.g. If weekday(dlvr_strt_date)=1 then checkdt=dlvr_strt_date + 4; /* Sunday so Mon/Tues/Wed */ If weekday(dlvr_strt_date)=2 then checkdt=dlvr_strt_date + 3; /* Monday so Tues/Wed/Thurs */ ... If weekday(dvlr_strt_date)=6 then checkdt=dlvr_strt_date + 5;      /* Friday so wed */ ... If dlvr_strt_date <= oe_date <= checkdt then "made"; else "miss"; Note-can't check this at the moment. ... View more

Typo in word anyalpha: where anyalpha(ZIP); ... View more

You could consider normalizing your data, so something like: Section  Type  Value 1               A1        1 1               A2        2 ... 3               B3         6 Then you could use SQL to sum and find missing values.  This may help with performance, however the secondary benefit of this is scalability.  So you have >60 variables at the moment, this is 60 rows per group, however what about 120, or more.  As for your example, I couldn't see any way of speeding it up other than that. ... View more

Hi, As LinusH mentioned there is calculated.  However you can also do these things by subquerying.  There are times when each method is useful, depending on the complexity of the SQL being created.  The below is logically the same, the first using a value which is calculated within the same select, the second having that value created as part of the select in the subquery.  In this simple example it may look a bit pointless, however if you are writing large queries which utilize many datasets, calculations, grouping etc. (say something like and ADSL table) then it can be useful to pre-process data going into your query to simplify things. proc sql;   create table ... as   select  values,           case when else,           case calculated when else   from    a_dataset; quit; proc sql;   create table ... as   select  values,              case when calculated else   from    (select values,                   case when else as calculated            from   a_dateset); quit; ... View more

Hi, Could you clarify your request a bit.  A left join is not an append but a merge.  You have also put conditions 1-3 in the from of dataset 2 where 1 and 2 are applicable to only dataset 1 from your text.  As a rough guess: proc sql;      create table WANT as      select     A.*,                    B.VAR3      from       (select VAR1,MAX(VAR2) from DATASET1 group by VAR1) A          left join   DATASET2 B      on           A.VAR1=B.VAR1; quit; ... View more

Hi, Maybe break your logic down into smaller parts. proc sql;   create table ... as   select  COALESCE(A.DATE,B.DATE) as DATE,           COALESCE(A.STORE_NO,B.STORE_NO) as STORE_NO,           COALESCE(A.ITEM_NO,B.ITEM_NO) as ITEM_NO,           A.COUNT_OF_ITEM_NO,           B.SUM_OF_SALE_QTY   from    (select DATE,STORE_NO,ITEM_NO,COUNT(ITEM_NO) as COUNT_OF_ITEM_NO from ... group by DATE,STORE_NO) A   full join (select DATE,STORE_NO,ITEM_NO,SUM(SALE_QTY) as SUM_OF_SALE_QTY from ... group by DATE,STORE_NO) B   on      A.DATE=B.DATE   and     A.STORE_NO=B.STORE_NO   and     A.ITEM_NO=B.ITEM_NO; quit; So in the above, doing the sum in a subquery and the count in a subquery, then join them together. ... View more