Okay, so say I have a large dataset of transactions with a store's ID, MARKET, QUARTER, and USERID and I want to count the number of distinct user transactions for each ID within each MARKET and for each QUARTER, and then find the total count for each Market's Quarters. There are 2 ID's and 2 Markets of interest, and an unknown number of users. I have working code that types out all 16 combination cases of ID, Market, and Quarter, but I'd like to do this in shorter code with macros if I can to apply to similar future data. Current code looks like: PROC SQL; CREATE TABLE FINAL_COUNTS AS SELECT COUNT(DISTINCT (CASE WHEN ID = '1234' AND MARKET IN ('ONLINE','SHOP') AND QUARTERS = 'Q1' THEN USERID END)) AS ABCREGQ1 FORMAT=COMMA20., ... COUNT(DISTINCT (CASE WHEN ID = '6789' AND MARKET NOT IN ('ONLINE','SHOP') AND QUARTERS = 'Q4' THEN USERID END)) AS XYZOTHERQ4 FORMAT=COMMA20. CALCULATED ABCREGQ1 + CALCULATED XYZREGQ1 as TOTALREGQ1 FORMAT=COMMA20., ... CALCULATED ABCREGQ4 + CALCULATED XYZREGQ4 as TOTALREGQ4 FORMAT=COMMA20., CALCULATED ABCOTHERQ1 + CALCULATED XYZOTHERQ1 as TOTALOTHERQ1 FORMAT=COMMA20., ... CALCULATED ABCOTHERQ1 + CALCULATED XYZOTHERQ1 as TOTALOTHERQ1 FORMAT=COMMA20. So I noticed there's a pattern in this I can hopefully exploit. Here's the logic of what I want to do, but I'm not sure about using macros or maybe Dictionary within Proc SQL: PROC SQL; CREATE TABLE FINAL_COUNTS AS SELECT COUNT(DISTINCT(CASE WHEN ID = '1234' then PREFIX = 'ABC' WHEN ID = '6789' then PREFIX = 'XYZ' WHEN MARKET IN ('ONLINE','SHOP') then GROUP = 'REG' WHEN MARKET NOT IN ('ONLINE','SHOP') then GROUP = 'OTHER' END) THEN USERID) AS COMPRESS(PREFIX||GROUP||QUARTERS) FORMAT=COMMA20., %let COMPRESS('TOTAL'||GROUP||QUARTERS) = sum(where (GROUP||QUARTERS) match) Once again, I'm not too good with Proc SQL, so I tried this with proc FREQ but I wasn't able to exactly replicate without SQL's DISTINCT. It looks like it can be done in less code with something like this, but I need some guidance. Thanks!
... View more