Thank you so much, this works perfectly and is exactly what I needed.  ... View more

thanks for the link Rick  ... View more

@thomp7050 great that you have it working!   I had some fun 'playing with Jupyter' Also good that you have posted the steps that were needed to get it working. This will help others that my run into the same issue.   Regards, --Resa ... View more

Thank you very much!! ... View more

For the sake of posterity:   PROC SQL; CREATE TABLE REPORT AS SELECT 'OVERALL' AS Jurisdiction , SUM(CASE WHEN BUILDINGTYPE = 'private' THEN ENR ELSE . END)/SUM(CASE WHEN BUILDINGTYPE = 'private' AND ENR IS NOT NULL THEN 1 ELSE 0 END) As Private , SUM(CASE WHEN BUILDINGTYPE = 'public' THEN ENR ELSE . END)/SUM(CASE WHEN BUILDINGTYPE = 'public' AND ENR IS NOT NULL THEN 1 ELSE 0 END) as Public FROM HAVE UNION ALL SELECT DISTINCT 'Jurisdiction', ., . FROM HAVE UNION ALL SELECT jurisdictn , SUM(CASE WHEN BUILDINGTYPE = 'private' THEN ENR ELSE . END)/SUM(CASE WHEN BUILDINGTYPE = 'private' AND ENR IS NOT NULL THEN 1 ELSE 0 END) As Private , SUM(CASE WHEN BUILDINGTYPE = 'public' THEN ENR ELSE . END)/SUM(CASE WHEN BUILDINGTYPE = 'public' AND ENR IS NOT NULL THEN 1 ELSE 0 END) as Public FROM HAVE GROUP BY jurisdictn ; QUIT; ... View more

Thank you very much for your help once again. I was wondering if there is a way to modify the sql code so that it outputs 2 additional columns containing the 2 dates that were used to compute that difference/overlapping days. I can do it for two combinations in a simple separate data step, but I found that it gets quite hairy when considering 3-5 combinations. Thank you very much! ... View more

So, product should not be included in teh select statement? ... View more