How do I create Dynamic Variable names in PROC SQL?

[mcxqbz]

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!

[ballardw]

Question: Is this to be used for a report? Instead of putting DATA (region and quarter and apparently Id ) into variable names I would look into Proc Report and use the Id, region and quarter as group variables and have the summaries calculated at appropriate points.

The approach you are taking looks like it would result in summarizing an entire dataset into one record which is likely to be a mess to get stuff out of, especially since you looking for a not trivial algorithm to build the variable names in the first place.

[RW9]

Post some test data, in the form of a datastep and what you want the output to look like.  Its hard to tell from the code posted there, but at a guess, I would say some grouping, and maybe a subquery could do it.