We talked to SAS support. They said that the problem is that our Oracle DB uses a different encoding. ... View more

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)   ... View more

The big problem now is that as soon as I wrap the count distinct in a CASE WHEN/THEN the result is NOT grouped. That makes is useless. Every row is shown by itself. As soon as I take the CASE WHEN/THEN out the group by works. How can I fix that? I noticed a "warning" saying that the aggregation option have had to be refreshed however when I look at them they look the same. ... View more

OK I got it working but the data does not aggregate nor does it count correctly. CASE WHEN (<<T_STAM.PNMG>> IN ('12','15','78')) THEN COUNT(DISTINCT <<T_STAM.PRONR>>) END the sql generated by the IM looks like this: PROC SQL; Create table WORK.%RESULT_TABLE_NAME% as SELECT table0.year AS DIR_2 LABEL='year' FORMAT=$4., table1.PARAMETER AS DIR_3 LABEL='Parameter' FORMAT=$30., ( CASE WHEN (table0.PNMG IN ('12','15','78')) THEN COUNT(DISTINCT table0.PROBNR) END ) AS DIR_4 LABEL='counting' FROM T_STA( compress=YES) table0 Inner join T_ERG( compress=YES) table1 on table0.STA_ID = table1.STA_ID GROUP BY 1, 2; quit; ... View more

Hi LinusH 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? ... View more

Yes, SAS tech support just said I should rename the IM to its original name. That fixed the problem but...hmm it should be stated somewhere that the naming reference relantionship between IM and web reports is fixed. Yes, I have replicated the issue with Firefox and Chrome. ... View more

When I right click in Management Console and Edit the Name (right click - rename is greyed out) then open IE 11 (logout, empty cache, etc.) then I get the error "file cannot be opened" It gives an error in the SAS log as well saying that the underlying information map is missing. ... View more

Different ones. Chome v55, Firefox v45. ... View more

@Patrick yes, indeed I forgot the CREATE TABLE. Thanks for that. I have used Hash Tables and DI Studio as well to create the two big tables in the first place (those two big tables are made of 30 join) BUT looking through a "small" subset of those two tables I can not tell developers to create HASH objects in between just to do the join. It is just bad usability and they wont accept it. I would use alias but this is just a tiny test of what is about to hit our SAS server when 20+ developers start working with those two big tables. @rogerjdeangelis. Thank you very much for your example! Great work! I can imagine it is trouble coming up with 110m rows of example data. That is why this remains a theoretical discusison about concepts and server settings. 14m is the data for 2015. That is a total of 14m rows that are then counted (distinct) and are aggregated to maybe 400 rows - cant tell for sure because the result has not yet been produced by myself 🙂 Of course. I actually only need 11 variables. year, office, country, office_name,car,car_name,residue,residuename, count(distinct STAMM.sta_id), count (distinct erg_id), count(distinct cartypeid) one STA_ID might have 1 to n ERG_IDs and one STA_ID might have 1 to n CARTYPE_IDs The names of offices and cars and residues is hard to predict. pick anything. there are no exact duplicated ERG_ID is a GUID and I do a count distinct on sta_id and cartypeID. ... View more