BookmarkSubscribeRSS Feed
RSB
Calcite | Level 5 RSB
Calcite | Level 5
I am trying to apply a user defined format on a measure in my cube.After creating the format, I have added this to my proc olap code.

libname pr 'D:\Program Files\SAS\Config\Lev1\SASAPP\SASEnvironment\SASFormats';
options fmtsearch=(pr);


However this does not seem to have any affect on the measures in my cube , after some investigation I have came across this SAS Document.

http://support.sas.com/kb/11/390.html

Assuming that my format is similiar to the one that is shown in the document

proc format ;
value NACOMMA . = 'NA'
other=[comma15.]
;
run;

I Have created a calculated member as per the document, but I am getting an error message


"Formula error - The format specified in the PUT function is invalid or not found - in the "PUT" function"


Since I have placed the format in the specified directory this should be available to all the applications, Please let me know what could be the issue.
8 REPLIES 8
AngelaHall
SAS Employee
You mentioned that the libname pr statement is only being added to the proc olap code? This only makes the format catalog available during OLAP cube creation. You still need this format available to users (and the OLAP Server) when creating measures, accessing/viewing/quering the cube. Similar to my post on custom formats for the Information Maps (http://sas-bi.blogspot.com/2010/01/using-custom-formats-in-information-map.html) you will need to assign this catalog to the server's SASv9.cfg.

~ Angela
RSB
Calcite | Level 5 RSB
Calcite | Level 5
I have copied the Formats to D:\Program Files\SAS\Config\Lev1\SASAPP\SASEnvironment\SASFormats

and added these lines to the D:\Program Files\SAS\Config\Lev1\SASAPP\sasv9.usermods.cfg and
D:\Program Files\SAS\Config\Lev1\SASAPP\OLAPServer\sasv9.usermods.cfg


-set APFMTLIB "D:\Program Files\SAS\Config\Lev1\SASAPP\SASEnvironment\SASFormats"
-insert fmtsearch APFMTLIB


after adding these I have restarted the OLAP and object spawner services.


now, I can use these format in my programs without using the fmtsearch option.

However the measures in the cube are still not able to recognize the format.
AngelaHall
SAS Employee
The calculated member GUI will not display the custom formats in the dropdown list.

Have you been attempted to edit an existing calculated member and type in the user defined format manually?

Create the calculated member with a standard format (such as BEST.).
Click ok. Then click 'Edit' on the calculated member to see the MDX string. Manually change the BEST. format portion of the string to your user defined format.

Example:
[MEASURES].[WeightAVG]/[MEASURES].[TotalWeightAVG], FORMAT_STRING="BEST15."

[MEASURES].[WeightAVG]/[MEASURES].[TotalWeightAVG], FORMAT_STRING="userdef15."

~ Angela
RSB
Calcite | Level 5 RSB
Calcite | Level 5
I have created a custom measure as

[Measures].[ORIGINAL_VALUESUM]-[Measures].[HAIRCUT_AMTSUM], FORMAT_STRING="nacomma."

there is no error but the format is being ignored.
AngelaHall
SAS Employee
We've gotten past the initial error, but at this point I think you should give SAS Technical Support a ring. http://support.sas.com/ctx/supportform/createForm

You will need to provide the information in this discussion as well as information on where the format is being ignored (EG? IMap? etc).

~ Angela
RSB
Calcite | Level 5 RSB
Calcite | Level 5
The Tech. support has suggested to create the calculated measure as

DEFINE Member '[COLL_ADDITIVE].[Measures].[ddf]'
AS 'IIF([Measures].[ORIGINAL_VALUE]+[Measures].[ORIGINAL_VALUE],
PUT([Measures].[ORIGINAL_VALUESUM]+[Measures].[ORIGINAL_VALUESUM], "COMMA15."), "Not Available")';

And this has done the trick. Although this is similiar to the one defined in the document , the difference is not using the user defined format in the PUT Function. Message was edited by: RSB
tommy81
Obsidian | Level 7

Can you explain the above code a bit. Where are you now using the custom format in the code now.

iif([Measures].[Value] or [Measures].[Value] = 0,
    PUT([Measures].[Value],"myfmt."),  "NO VALUE")

twocanbazza
Quartz | Level 8
You may have to restart your olap server as well, to enable the new format to be seen...

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 1591 views
  • 0 likes
  • 4 in conversation