f



Re: SAS/Warehouse Administrator Problem?? or SAS/ACCESS problem??

I'm pretty sure that behind the scenes SAS/Warehouse Administrator is
generating and running SQL code. If you can expose that code and post it
here it might help.

On Sun, 23 Apr 2006 21:53:41 -0700, saivs28@GMAIL.COM wrote:

>hello,
>
>I've built a process in SAS/Warehouse Administrator, where i'm trying
>to extract distinct cities from the 3 tables(source schema) in Oracle
>into a new table (target schema) called CITY and again into UniqueCity
>(target schema) bcos of some redundancy. The process looks like this.
>
>                                 Unique City
>                                         |
>                                         |
>                                    mapping
>                                        |
>                                        |
>                                     CITY
>                                        |
>                    ----------------------------------------------
>                    |                    |                   |
>                    |                    |                   |
>              mapping 1         mapping 2       mapping 3
>                    |                     |                   |
>                    |                     |                   |
>             SUPPLIER_TBL    BANK_TBL         TRANSPORTER_TBL
>
>Of course its all metadata driven. Now the issue on hand is the table
>does get created but the values in the table are missing. Say the table
>created should consist of 9 rows with only one variable CITY. When i
>query this in the Oracle(target schema) its showing only rows: 1,5,9
>rest in between rows are blank. The final table should actually consist
>of only 7 rows wheras its showing only 4 rows. I hope the following
>gives a better perspective.
>
>I think its a problem with the SAS/ACCESS part.
>
>I'll give u the result of the views I created for better
>understanding.Here are the following results:
>
>________________________________________________________________________
>
>                      UNIQUE CITIES IN BANK TABLE                   1
>                                       04:13 Wednesday, April 19, 2006
>
>                         Obs    CITY
>
>                           1    Hyderabad
>                           2    Mumbai
>
>________________________________________________________________________
>
>                   UNIQUE CITIES IN TRANSPORTER TABLE          2
>                                       04:13 Wednesday, April 19, 2006
>
>                         Obs    CITY
>
>                           1    Chennai
>                           2    Mumbai
>                           3    Patna
>                           4    Secunderabad
>
>________________________________________________________________________
>
>                   UNIQUE CITIES IN SUPP TABLE                      3
>                                      04:13 Wednesday, April 19, 2006
>
>                         Obs    CITY
>
>                           1    Bangalore
>                           2    New Delhi
>                           3    Secunderabad
>
>________________________________________________________________________
>
>            CONCATENATION OF 3 UNIQUE(BY CITY) SQL VIEWS         4
>                                      04:13 Wednesday, April 19, 2006
>
>                         Obs    CITY
>
>                           1    Hyderabad
>                           2    Mumbai
>                           3    Chennai
>                           4    Mumbai
>                           5    Patna
>                           6    Secunderabad
>                           7    Bangalore
>                           8    New Delhi
>                           9    Secunderabad
>
>________________________________________________________________________
>
>  CONCATENATION OF 3 VIEWS UNIQUE(BY CITY) LOADED TO ORACLE       5
>                                       04:13 Wednesday, April 19, 2006
>
>                         Obs    CITY
>
>                           1    Hyderabad
>                           2
>                           3
>                           4
>                           5    Patna
>                           6
>                           7
>                           8
>                           9    Secunde
>
>________________________________________________________________________
>
>                DISTINCT CITIES FROM ALL IN SQL VIEW                 6
>                                      04:13 Wednesday, April 19, 2006
>
>                         Obs    CITY
>
>                           1    Hyderabad
>                           2    Patna
>                           3    Secunde
>                           4
>________________________________________________________________________
>
>                DISTINCT CITIES FROM ALL 3 IN ORACLE                 7
>                                     04:13 Wednesday, April 19, 2006
>
>                         Obs    CITY
>
>                           1    Hyderabad
>                           2
>                           3
>                           4
>
>---------------------------------------------------------------------------
-----
0
nospam1405 (4666)
4/24/2006 4:15:18 PM
comp.soft-sys.sas 142828 articles. 3 followers. Post Follow

1 Replies
677 Views

Similar Articles

[PageSpeed] 54

The following code is only a part of it. ie. upto CITY table. The CITY
table is created in Oracle(target schema) but the table is displaying
only rows 1,5,9 jfyi.

Invoking SAS/Warehouse Administrator Software, Release 2.3

1    /************************************************************/
2    /* Name: BANK */
3    /* Description: Access the Data for this step */
4    /* Generated:   24APR2006:23:21:14 */
5    /************************************************************/
6    libname libref1 Oracle
7    PRESERVE_COL_NAMES=YES PRESERVE_TAB_NAMES=YES
8    PASSWORD=XXXXXXX USER="mmsrc" path=orcl;
NOTE: Libref LIBREF1 was successfully assigned as follows:
      Engine:        ORACLE
      Physical Name: orcl
9    %let syslast=libref1.BANK_TBL;
10   /************************************************************/
11   /* Name: Mapping */
12   /* Description: Execute the Process for this step */
13   /* Generated:   24APR2006:23:21:14 */
14   /************************************************************/
15   PROC DATASETS LIB= WORK NOLIST NOWARN MEMTYPE=DATA;
16   DELETE A00000VR;
17   QUIT;

NOTE: PROCEDURE DATASETS used (Total process time):
      real time           0.12 seconds
      cpu time            0.04 seconds


18   PROC SQL;
19   CREATE VIEW WORK.A00000VR AS
20   SELECT
21   DISTINCT(BANK_CITY)
22   AS CITY length=20
23   FROM
24   libref1.BANK_TBL
25   ;
NOTE: SQL view WORK.A00000VR has been defined.
26   QUIT;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.18 seconds
      cpu time            0.09 seconds


27   %let syslast=WORK.A00000VR;
28   /************************************************************/
29   /* Name: TRANSPORTER */
30   /* Description: Access the Data for this step */
31   /* Generated:   24APR2006:23:21:15 */
32   /************************************************************/
33   libname libref1 Oracle
34   PRESERVE_COL_NAMES=YES PRESERVE_TAB_NAMES=YES
35   PASSWORD=XXXXXXX USER="mmsrc" path=orcl;
NOTE: Libref LIBREF1 was successfully assigned as follows:
      Engine:        ORACLE
      Physical Name: orcl
36   %let syslast=libref1.TRANSPORTER_TBL;
37   /************************************************************/
38   /* Name: Mapping */
39   /* Description: Execute the Process for this step */
40   /* Generated:   24APR2006:23:21:15 */
41   /************************************************************/
42   PROC DATASETS LIB= WORK NOLIST NOWARN MEMTYPE=DATA;
43   DELETE A00000VW;
44   QUIT;

NOTE: PROCEDURE DATASETS used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds


45   PROC SQL;
46   CREATE VIEW WORK.A00000VW AS
47   SELECT
48   DISTINCT(TRANSPORTER_CITY)
49   AS CITY length=20
50   FROM
51   libref1.TRANSPORTER_TBL
52   ;
NOTE: SQL view WORK.A00000VW has been defined.
53   QUIT;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.04 seconds
      cpu time            0.04 seconds


54   %let syslast=WORK.A00000VW;
55   /************************************************************/
56   /* Name: Supplier_ODD */
57   /* Description: Access the Data for this step */
58   /* Generated:   24APR2006:23:21:15 */
59   /************************************************************/
60   libname libref1 Oracle
61   PRESERVE_COL_NAMES=YES PRESERVE_TAB_NAMES=YES
62   PASSWORD=XXXXXXX USER="mmsrc" path=orcl;
NOTE: Libref LIBREF1 was successfully assigned as follows:
      Engine:        ORACLE
      Physical Name: orcl
63   %let syslast=libref1.SUPPLIER_TBL;
64   /************************************************************/
65   /* Name: Mapping */
66   /* Description: Execute the Process for this step */
67   /* Generated:   24APR2006:23:21:15 */
68   /************************************************************/
69   PROC DATASETS LIB= WORK NOLIST NOWARN MEMTYPE=DATA;
70   DELETE A00000W1;
71   QUIT;

NOTE: PROCEDURE DATASETS used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds


72   PROC SQL;
73   CREATE VIEW WORK.A00000W1 AS
74   SELECT
75   DISTINCT(SUPP_CITY)
76   AS CITY length=20
77   FROM
78   libref1.SUPPLIER_TBL
79   ;
NOTE: SQL view WORK.A00000W1 has been defined.
80   QUIT;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.03 seconds
      cpu time            0.03 seconds


81   %let syslast=WORK.A00000W1;
82   /************************************************************/
83   /* Name: CITY */
84   /* Description: Execute the Process for this step */
85   /* Generated:   24APR2006:23:21:15 */
86   /************************************************************/
87   libname libref2 Oracle
88   PRESERVE_COL_NAMES=YES PRESERVE_TAB_NAMES=YES
89   PASSWORD=XXXXXXX USER="mmtgt" path=orcl;
NOTE: Libref LIBREF2 was successfully assigned as follows:
      Engine:        ORACLE
      Physical Name: orcl
90   libname libref2 Oracle
91   PRESERVE_COL_NAMES=YES PRESERVE_TAB_NAMES=YES
92   PASSWORD=XXXXXXX USER="mmtgt" path=orcl;
NOTE: Libref LIBREF2 was successfully assigned as follows:
      Engine:        ORACLE
      Physical Name: orcl
93   DATA WORK.A0000UL / VIEW=WORK.A0000UL;
94   SET
95   WORK.A00000VR
96   WORK.A00000VW
97   WORK.A00000W1
98   ;
99   RUN;

NOTE: DATA STEP view saved on file WORK.A0000UL.
NOTE: A stored DATA STEP view cannot run under a different operating
      system.
NOTE: DATA statement used (Total process time):
      real time           0.71 seconds
      cpu time            0.12 seconds


100  %GLOBAL DBXLAST;
101  %GLOBAL DBXRC;
102  %GLOBAL DBDATLOC;
103  %let DBXRC = -1;
104  %let DWNUMIDX = -1;
105  %let DBXLAST=&syslast;
106  %let DBDATLOC=libref2.CITY;
107  /*----------------------------------------------------------*/
108  /* Name: DBEXIST */
109  /* Description: Check Existence of Table */
110  /* Generated:   24APR2006:23:21:16 */
111  /*----------------------------------------------------------*/
112  %MACRO DBEXIST;
113  PROC SQL NOPRINT;
114  SELECT count(*) INTO :DBXRC
115  FROM dictionary.tables
116  WHERE libname= "LIBREF2" AND
117  memname= "CITY" AND memtype = 'DATA';
118  quit;
119  %MEND DBEXIST;
120  /*----------------------------------------------------------*/
121  /* Name: DBWALOAD */
122  /* Description: Define Load Data Macro */
123  /* Generated:   24APR2006:23:21:16 */
124  /*----------------------------------------------------------*/
125  %macro dbwaload;
126  %*;
127  %* Check if Table Exists;
128  %*;
129  %put NOTE: Checking Table Existence...;
130  %DBEXIST;
131  %*;
132  %* If Table Exists;
133  %*;
134  %if &DBXRC=1 %then %do;
135  %*;
136  %put NOTE: Dropping Table...;
137  /*----------------------------------------------------------*/
138  /* Name: Drop Table */
139  /* Description: Drop a table */
140  /* Generated:   24APR2006:23:21:16 */
141  /*----------------------------------------------------------*/
142  proc datasets LIB=LIBREF2 nolist nodetails;
143  delete CITY;
144  quit;
145  %let DBXRC=0;
146  %*;
147  %end;
148  %*;
149  %if &DBXRC=0 %then %do;
150  %*;
151  %* Table does not exist;
152  %*;
153  %put NOTE: Creating Table...;
154  /*----------------------------------------------------------*/
155  /* Name: Create Table */
156  /* Description: Create a new table */
157  /* Generated:   24APR2006:23:21:16 */
158  /*----------------------------------------------------------*/
159  data libref2.CITY( );
160  LENGTH CITY $ 20;
161  stop;
162  run;
163  %LET SYSLAST= &DBXLAST;
164  %*;
165  %end;
166  %*;
167  %put NOTE: Appending Data...;
168  /*----------------------------------------------------------*/
169  /* Name: Append */
170  /* Description: Append New Data */
171  /* Generated:   24APR2006:23:21:16 */
172  /*----------------------------------------------------------*/
173  proc append data= &DBXLAST base= libref2.CITY;
174  run;
175  %let syslast=&DBDATLOC;
176  %mend;
177  /************************************************************/
178  /* Name: DBWALOAD */
179  /* Description: Execute Load Data Macro */
180  /* Generated:   24APR2006:23:21:16 */
181  /************************************************************/
182  %dbwaload;
NOTE: Checking Table Existence...
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.14 seconds
      cpu time            0.01 seconds


NOTE: Dropping Table...

NOTE: Deleting LIBREF2.CITY (memtype=DATA).
NOTE: PROCEDURE DATASETS used (Total process time):
      real time           0.90 seconds
      cpu time            0.03 seconds


NOTE: Creating Table...

NOTE: Variable CITY is uninitialized.
NOTE: The data set LIBREF2.CITY has 0 observations and 1 variables.
NOTE: DATA statement used (Total process time):
      real time           0.29 seconds
      cpu time            0.07 seconds


NOTE: Appending Data...

NOTE: Appending WORK.A0000UL to LIBREF2.CITY.
NOTE: There were 9 observations read from the data set WORK.A0000UL.
NOTE: 9 observations added.
NOTE: The data set LIBREF2.CITY has . observations and 1 variables.
NOTE: View WORK.A0000UL.VIEW used (Total process time):
      real time           0.20 seconds
      cpu time            0.06 seconds

NOTE: There were 2 observations read from the data set WORK.A00000VR.
NOTE: There were 4 observations read from the data set WORK.A00000VW.
NOTE: There were 3 observations read from the data set WORK.A00000W1.
NOTE: PROCEDURE APPEND used (Total process time):
      real time           0.34 seconds
      cpu time            0.12 seconds

0
saivs28 (23)
4/24/2006 6:19:35 PM
Reply: