01-04-2017 08:27 AM - edited 01-04-2017 08:27 AM
Hi SAS Experts,
I am struggling a bit with Information Map Expressions.
I want to only count distinct for if another value is in (12,13,14).
So far I did not get very close.
CASE WHEN <<T_STA.PNMG>>eq 12 THEN count(distinct(<<T_STA.STA_ID>>)) END
WHEN <<T_STA.PNMG>> = 12 THEN count(distinct(<<T_STA.STA_ID>>))
CASE WHEN <<T_STA.PNMG>> CONTAINS (12,13,14) THEN count(distinct(<<T_STA.STA_ID>>)) END
but there is always an error telling me that:
ERROR 22-322: Syntax error, expected one of the following: !, !!, &, (, *, **, +, -, /, <, <=, <>, =, >, >=, ?, AND, BETWEEN,
CONTAINS, EQ, EQT, GE, GET, GT, GTT, LE, LET, LIKE, LT, LTT, NE, NET, OR, ^=, |, ||, ~=
01-04-2017 11:30 AM
01-05-2017 03:17 AM
I see. I keep playing around with it.
The IM is the base for a web report and Web Report Studio does not offer aggregation functions with count distinct so is there any other place where I can put them?
User want to build ad-hoc reports with different count-distinct's depending on the requirements. I can define the count-distinct (thanks to information map studio) but that is it right?
01-05-2017 04:52 AM
As @LinusH already pointed out, performance could become an issue. You need to test with your full volume data.
As for IMs: I haven't done a lot with it but I always understood them as some sort of SQL view on metadata level. In order to figure out what SQL syntax would work, you could first just play around with code and then only build it in metadata once you've got a coding solution.
I believe for what you're after, you would have to come up with something as "ugly" as below:
libname t_sta (work); data T_STA.have; do PNMG=12 to 12; do STA_ID=1 to 3; output; output; end; end; do PNMG=13 to 14; do STA_ID=1 to 6; output; end; end; run; proc sql; select sta_id, PNMG, CASE WHEN have.PNMG = 12 THEN select COUNT(DISTINCT STA_ID) from t_sta.have where pnmg=12 WHEN have.PNMG = 13 THEN select COUNT(DISTINCT STA_ID) from t_sta.have where pnmg=13 else . END as id_cnt from t_sta.have ; quit; libname t_sta clear;
01-05-2017 06:31 AM
01-06-2017 03:05 AM
01-06-2017 06:09 AM
01-09-2017 08:58 AM - edited 01-09-2017 10:02 AM
I think I found a work around by first creating an IM Studio variable filling it with distinct sta_ID case when xxxx=xyy and then using that variable to do a count distinct on it (creating a classification variable and a counting variable)