It is to be used with data integration studio, so it will be in a where clause in a proc sql ex: proc sql; select * from oracle_table where month=%converto_yyyymm(&data_macro_var); quit; or proc sql; select * from table1, table2 where table1.month_column=%converto_yyyymm(table2.date_column) quit; ... View more

Hello everyone, JasionS, I work with SAS 9.1.3 and I do infact belive that not everything is working correctly with the prx functions when used within %sysfunc. Both the prxmatch and prxchange functions are overloaded, they can have their first argument to be a number (regex_id) or a string with a regex. Everytime one uses the string overload and use / as a delimiter I think SAS assumes that we are trying to make a division and our argument should be a number, we get a error message : %put %sysfunc(prxchange(%str(s/\d/a/),-1,123)); (ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric operand is required.) If we parse the regex in advance and then use the regex_id in the prxmatch/prxchange function, everything works like it should: %let regex=%sysfunc(prxparse(%str(s/\d/a/))); %put %sysfunc(prxchange(&regex,-1,123)); aaa Edit: using numerical operators as a delimiter have the same problem, if I use ! or ? as a delimiter all works fine. Tried also to quote only the slashes(s%str(/)\d%str(/)a%str(/)) and I get the same result Message was edited by: Pgloria ... View more

Found the problem, The prxmatch function has 2 versions one that gets a regular-expression-id and the other a perl-regular-expression has the firt parameter. SAS checks the first char in the string, and if it is a number or a aritmetic operator it %evals() the parameter. So, doing: %let regex_id=%sysfunc(prxparse(/\d/)); %put %sysfunc(prxmatch(&regex_id,123)); Works like a charm 😉 ... View more

These examples are just a reduction of my problem, on those examples I want each line to output 1 to the log. I just realy want to understand the reason for me not getting to use the slash '/' on the regex when I use the PRX functions inside a %sysfunc. On the last of my examples I got it working, so it's just to kill my curiosity. Thanks 😉 ... View more

Hi again, @Angela Hall: I don't use distinctcount in my measures, only iif's and sum's I got some new insight to the performance problem, I guess it's the number of measures I display. The report displays 5 measures (all custom), if I use only one, the report drills fast, with two a little slower but aceptable, with 3 and more it gets really slow. I don't have access to EG or the MS Office addin at the moment, so I can't say for shure that the problem is not in the WRS view, but seeing that it loads fast with one measure my new guess is that in can be a memory bottleneck, maybe from the olap server. What do you think??? Gona try to get EG untill the end of the day to test this there. Thanks for your help Pedro ... View more

The cube is a star schema, fact table 1.2 million observations. The cube has 5 dimensions, 3 are used for group breaks and have 1 level each, the other 2 are used to drill thru and have 2 levels each. The group break dimensions are small, two of them only have 1 key each with this test data, the other one has 4 distinct keys. The drill thru dimensions are also small, one has 12 keys the other a little bigger has 50 keys. On this project that I'm working I'm not allowed to change configuration/manage the servers, so I can not reboot them to allow debugging 😕 But apart from that, my guess is that the aggregations don't work with the custom measures.... and I think that because if I keep the cube structure and only use the default measures(sum for example) the cube drills take like a second, oposing to some 30 seconds when I'm using my custom measures.... Thanks in advance for your help ... View more

thank you, gonna try asking there 😉 ... View more