f



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

What's the question? I know it you presented it earlier, but you can't
expect people to find the pieces of a problem and put them together.

Look at the various intermediate results and find the point where they
first diverge from what is expected. Then look at the code which generates
that particular result. De-macroize that code and prepare some test data so
that anybody can run it easily. Then present that isolated version of the
problem (if the answer has not already emerged during this process).

On Mon, 24 Apr 2006 11:19:35 -0700, saivs28@GMAIL.COM wrote:

>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
nospam1405 (4666)
5/3/2006 2:30:13 PM
comp.soft-sys.sas 142828 articles. 3 followers. Post Follow

0 Replies
714 Views

Similar Articles

[PageSpeed] 38

Reply: