topic Re: HELP!!! TROUBLE CREATING TIME DIMENSION WITH PROC OLAP!!!! in SAS Programming

HELP!!! TROUBLE CREATING TIME DIMENSION WITH PROC OLAP!!!!

deleted_user — Thu, 08 Oct 2009 15:46:22 GMT

I'm trying to create a Time dimension trough a Proc Olap but I have been having a fustrating experience.

The date is stored in my detailed table under the column "Reported_Month". The dates are stored as "01Jul2009", 01Aug2009", etc... I've attached my code and the log with errors.

Where is my code:

dimension Time
hierarchies=(Time)
type=time
;
hierarchy Time
levels=(Year Quarter Month Day)
;
level Year
type=year
column=reported_month
format=year.
;
level Quarter
type=quarters
column=reported_month
format=qtr.
;
level Month
type=months
column=reported_month
format=monname.
;
level Day
type=days
column=reported_month
format=weekdate.
;

Here is the LOG with errors!:

49 dimension Time
50 hierarchies=(Time)
51 type=time
52 ;
53 hierarchy Time
54 levels=(Year Quarter Month Day)
55 ;
56 level Year
57 type=year
58 column=reported_month
______
22
76
ERROR 22-322: Syntax error, expecting one of the following: ;, CAPTION, DESC, EMPTY, IGNORE_EMPTY, SORT_ORDER, TYPE.

ERROR 76-322: Syntax error, statement will be ignored.

59 format=year.
60 ;
61 level Quarter
62 type=quarters
63 column=reported_month
______
22
76
ERROR 22-322: Syntax error, expecting one of the following: ;, CAPTION, DESC, EMPTY, IGNORE_EMPTY, SORT_ORDER, TYPE.

ERROR 76-322: Syntax error, statement will be ignored.

64 format=qtr.
65 ;
66 level Month
67 type=months
68 column=reported_month
______
22
76
ERROR 22-322: Syntax error, expecting one of the following: ;, CAPTION, DESC, EMPTY, IGNORE_EMPTY, SORT_ORDER, TYPE.

ERROR 76-322: Syntax error, statement will be ignored.

69 format=monname.
70 ;
71 level Day
72 type=days
73 column=reported_month
______
22
3 The SAS System 09:38 Wednesday, October 7, 2009

76
ERROR 22-322: Syntax error, expecting one of the following: ;, CAPTION, DESC, EMPTY, IGNORE_EMPTY, SORT_ORDER, TYPE.
ERROR 76-322: Syntax error, statement will be ignored.
74 format=weekdate.
75 ;
76

Re: HELP!!! TROUBLE CREATING TIME DIMENSION WITH PROC OLAP!!!!

sbb — Thu, 08 Oct 2009 17:01:04 GMT

Given the SAS log diagnostic error, maybe you can explain what you would have expected to occur (ideally not in all CAPS PLEASE). Also, you will most likely find useful technical documentation and supplemental conference reference papers at the SAS support http://support.sas.com/ website, either using the site's SEARCH facility or with using a Google advanced search (adding the keyword site:sas.com to limit the search to the SAS.COM domain resources).

Scott Barry
SBBWorks, Inc.

SAS product documentation for reference:
http://support.sas.com/documentation/onlinedoc/olapcube/index.html

Re: HELP!!! TROUBLE CREATING TIME DIMENSION WITH PROC OLAP!!!!

deleted_user — Thu, 08 Oct 2009 17:17:06 GMT

That wasn;t very helpful... the code is taken right out of the SAS support library, that is why it is soo fustrating!

Re: HELP!!! TROUBLE CREATING TIME DIMENSION WITH PROC OLAP!!!!

sbb — Thu, 08 Oct 2009 17:54:48 GMT

Suggest you might want to mention the specific page, web link or other reference where you found the code -- might be a bit helpful for the SAS Institute staff. Also, SAS code samples should not necessarily be considered fool-proof - sort-of buyer beware.

It sounds as though you are expecting someone else to figure out what's wrong with the "sample" code and fix it for you? It would be helpful to clearly state what you are looking to get out of the experience. Again, I clue you into the useful SAS support website DOC and "as-is, where-is" code samples and explanations.

Scott Barry
SBBWorks, Inc.

Re: HELP!!! TROUBLE CREATING TIME DIMENSION WITH PROC OLAP!!!!

deleted_user — Thu, 08 Oct 2009 18:07:02 GMT

That's exactly what I'm looking for...

http://support.sas.com/documentation/cdl/en/olapug/59574/HTML/default/a003228513.htm

Re: HELP!!! TROUBLE CREATING TIME DIMENSION WITH PROC OLAP!!!!

sbb — Thu, 08 Oct 2009 18:20:40 GMT

Took a look at the DOC link, and I don't see where a LEVEL statement is being used with a COLUMN= keyword parameter. Suggest reviewing the various statement parameter coding and syntax - back to some basics and self-initiated deskchecking.

Scott Barry
SBBWorks, Inc.

Re: HELP!!! TROUBLE CREATING TIME DIMENSION WITH PROC OLAP!!!!

Flip — Thu, 08 Oct 2009 18:22:16 GMT

Curiosity got the better of me. I hae never used PROC OLAP but scanning the documentation, I see no "COLUMN" option on the LEVEL statement. That looks like what the log is telling you.

Re: HELP!!! TROUBLE CREATING TIME DIMENSION WITH PROC OLAP!!!!

deleted_user — Thu, 08 Oct 2009 18:54:58 GMT

http://support.sas.com/documentation/cdl/en/olapug/59574/HTML/default/a002608965.htm

Look at this DOC... what does not make sense with the other Doc is that there is no reference to which column to reference in the detail table.

Re: HELP!!! TROUBLE CREATING TIME DIMENSION WITH PROC OLAP!!!!

Flip — Thu, 08 Oct 2009 19:03:56 GMT

Are you using 9.2 It looks like the Column option was not in 9.1.
http://support.sas.com/onlinedoc/913/docMainpage.jsp

Re: HELP!!! TROUBLE CREATING TIME DIMENSION WITH PROC OLAP!!!!

deleted_user — Thu, 08 Oct 2009 19:52:16 GMT

as expect no good....

64 dimension Reported_month
65 hierarchies=(Reported_month)
66 caption="Reported_month"
67 type=time
68 ;
69
70 hierarchy Reported_month
71 levels=(year quarter month)
72 ;
73 level year
74 type=year
75 ;
76 level quarter
77 type=quarters
78 ;
79 level Month
80 type=months
81 ;
82
83
84
85
3 The SAS System 09:38 Wednesday, October 7, 2009

86 measure Total_Revenue
87 column=Total_ACTVY_AMT
88 stat=sum
89 format=dollar12.2;
90
91 measure Average_Subs
92 column=AvgS
93 stat=sum
94 format=12.2;
95
96 DEFINE
97 MEMBER "[GLCUBE].[Measures].[ARPU]"
98 AS "'[Measures].[Total Revenue] / [Measures].[Average Subs]', FORMAT_STRING = 'dollar10.2'";
99
100
101 run;

NOTE: The cube already exists. The existing cube will be overwritten with the new cube.
ERROR: The registration for column "month" is not found on the metadata server.
ERROR: The registration for column "quarter" is not found on the metadata server.
ERROR: The registration for column "year" is not found on the metadata server.
ERROR: Cube "GLCUBE" cannot be created.
WARNING: Due to previous PROC OLAP failures, all DEFINE and UNDEFINE statements will be ignored.
NOTE: PROCEDURE OLAP used (Total process time):
real time 0.17 seconds
cpu time 0.02 seconds

Re: HELP!!! TROUBLE CREATING TIME DIMENSION WITH PROC OLAP!!!!

Cynthia_sas — Thu, 08 Oct 2009 20:45:59 GMT

Hi:
Now that you are getting a different error message, and one that implies you may have issues with the Metadata server, your best resource for resolving this problem (in my opinion) is for you to open a track with Tech Support.
http://support.sas.com/ctx/supportform/createForm

They can direct you to look in SAS Management Console, tell you how to look at Metadata information and they can help you debug what's happening in your specific configuration, with your specific Metadata and OLAP cube. They can also help you figure out how to define and reference a cube slice for reporting purposes.

cynthia

Re: HELP!!! TROUBLE CREATING TIME DIMENSION WITH PROC OLAP!!!!

deleted_user — Fri, 09 Oct 2009 14:39:27 GMT

Hello did you manage to resolve this issue? When producing a cube I am getting similar message. Any ideas as to what caused your problem? As far as I see, the tables have been declared in the metadata properly.

PRINT(GENERATE_CUBE): MEASURE PTFL_RARORAC_SUM STAT=SUM AGGR_COLUMN=PTFL_RARORAC
CAPTION='Portfolio RaRoRaC' FORMAT=NLNUM18.2 ;
MPRINT(GENERATE_CUBE): RUN ;
ERROR: The registration for column "BASTR_REGION_BU" is not found on the metadata server.
ERROR: The registration for column "BASTR_DIVISION_BU" is not found on the metadata server.
ERROR: The registration for column "BUSTR_ORGSUBSECTOR_BU" is not found on the metadata
server.
ERROR: The registration for column "BUSTR_ORGSECTOR_BU" is not found on the metadata server.
ERROR: The registration for column "BUSTR_DIVISION_BU" is not found on the metadata server.
ERROR: The registration for column "TOP" is not found on the metadata server.
ERROR: Cube "REPORT01_MAIN" cannot be created.
NOTE: PROCEDURE OLAP used (Total process time):
real time 1.53 seconds
user cpu time 0.07 seconds
system cpu time 0.20 seconds
Memory 1794k

NOTE: The SAS System stopped processing this step because of errors.
MLOGIC(GENERATE_CUBE): Ending execution.

Re: HELP!!! TROUBLE CREATING TIME DIMENSION WITH PROC OLAP!!!!

Flip — Fri, 09 Oct 2009 16:15:55 GMT

Check out http://support.sas.com/kb/33/366.html