Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for
- Home
- /
- Programming
- /
- Programming
- /
- Re: ERROR: Column itmCode_id could not be found in the table/view iden...
Options
- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
Obsidian | Level 7
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Posted 08-27-2019 02:29 PM
(471 views)
Does anyone know why I get this error at the end? I ran out of ideas.
Thank you!
12141 dm "log; clear;";
12142 ************************************************************;
12143
12144 %let drive=j:\IEA Products\ACPSA;
12145
12146 %let acpsaItemConc = acpsa_item_concord_2012naics;
12147 %let acpsaitemcc = acpsa_item_concord_2012naics;
12148 %let summaryConc = itemacpsa_sum_conc_naics2012;
12149 %let summaryOrder = itemacpsa_sum_order_naics2012;
12150
12151
12152 %let dsName = 'Annual17';
12153
12154 %let prodyr=2019;
12155
12156 %let b_ioyear = 1998; * Beginning year for the macro called "price_series";
12157 /* Note: The price data set up so the output dataset (p_q_relatives_&currioyr.) contains the price relatives
12158 for the year in the dataset name (b_ioear) and the year before. That means that "p_q_relatives_1998"
12159 dataset are the price relatives between 1997 and 1998. */
12160 %let e_ioyear = 1998; * Ending year for the macro called "price_series";
12161
12162 %let refyr=2012;
12163
12164 ************************************************************;
12165 ************************************************************;
12166 libname TRN odbc dsn="IEtest17" schema=trn;
NOTE: Libref TRN was successfully assigned as follows:
Engine: ODBC
Physical Name: IEtest17
12167 libname MAR odbc dsn="IEtest17" schema=mar;
NOTE: Libref MAR was successfully assigned as follows:
Engine: ODBC
Physical Name: IEtest17
12168 libname TCC odbc dsn="IEtest17" schema=tcc;
NOTE: Libref TCC was successfully assigned as follows:
Engine: ODBC
Physical Name: IEtest17
12169 libname sql_iips odbc dsn="IEtest17" schema=dbo;
NOTE: Libref SQL_IIPS was successfully assigned as follows:
Engine: ODBC
Physical Name: IEtest17
12170 libname DBO odbc dsn="IEtest17" schema=dbo;
NOTE: Libref DBO was successfully assigned as follows:
Engine: ODBC
Physical Name: IEtest17
12171 libname CDE odbc dsn="IEtest17" schema=cde;
NOTE: Libref CDE was successfully assigned as follows:
Engine: ODBC
Physical Name: IEtest17
12172 libname ICC odbc dsn="IEtest17" schema=icc;
NOTE: Libref ICC was successfully assigned as follows:
Engine: ODBC
Physical Name: IEtest17
12173 libname occ odbc dsn="IEtest17" schema=occ;
NOTE: Libref OCC was successfully assigned as follows:
Engine: ODBC
Physical Name: IEtest17
12174 libname concord "&drive\Concordances";
SYMBOLGEN: Macro variable DRIVE resolves to j:\IEA Products\ACPSA
NOTE: Libref CONCORD was successfully assigned as follows:
Engine: V9
Physical Name: j:\IEA Products\ACPSA\Concordances
12175 libname int_sas "&drive\Estimates\Intermediate\SAS\&prodyr";
SYMBOLGEN: Macro variable DRIVE resolves to j:\IEA Products\ACPSA
SYMBOLGEN: Macro variable PRODYR resolves to 2019
NOTE: Libref INT_SAS was successfully assigned as follows:
Engine: V9
Physical Name: j:\IEA Products\ACPSA\Estimates\Intermediate\SAS\2019
12176 libname TEMPSAS "c:\sas";
NOTE: Libref TEMPSAS was successfully assigned as follows:
Engine: V9
Physical Name: c:\sas
12177
12178 *********************************************************************************************;
12179 proc datasets library=work kill;
NOTE: Deleting WORK.SASMACR (memtype=CATALOG).
NOTE: File WORK.SASMACR (memtype=CATALOG) cannot be deleted because it is in use.
NOTE: PROCEDURE DATASETS used (Total process time):
real time 0.02 seconds
cpu time 0.01 seconds
12180 proc datasets library=tempsas kill;
NOTE: Deleting TEMPSAS.ALL_PARTIALS_REMERGED (memtype=DATA).
NOTE: Deleting TEMPSAS.ALL_PARTIALS_REMERGED_SORT (memtype=DATA).
NOTE: Deleting TEMPSAS.BAD_CONC (memtype=DATA).
NOTE: Deleting TEMPSAS.CURRENT_ITEM_CONCORDANCE (memtype=DATA).
NOTE: Deleting TEMPSAS.CURRENT_ITEM_CONCORDANCE_SORT (memtype=DATA).
NOTE: Deleting TEMPSAS.CURRENT_ITEM_CONCORDANCE_SUM (memtype=DATA).
NOTE: Deleting TEMPSAS.GOOD_CONC (memtype=DATA).
NOTE: Deleting TEMPSAS.ITEMACPSA_23 (memtype=DATA).
NOTE: Deleting TEMPSAS.ITEMACPSA_3X (memtype=DATA).
NOTE: Deleting TEMPSAS.ITEMACPSA_51 (memtype=DATA).
NOTE: Deleting TEMPSAS.ITEMACPSA_54 (memtype=DATA).
NOTE: Deleting TEMPSAS.ITEMACPSA_56 (memtype=DATA).
NOTE: Deleting TEMPSAS.ITEMACPSA_61 (memtype=DATA).
NOTE: Deleting TEMPSAS.ITEMACPSA_71 (memtype=DATA).
NOTE: Deleting TEMPSAS.ITEMACPSA_81 (memtype=DATA).
NOTE: Deleting TEMPSAS.ITEMACPSA_99 (memtype=DATA).
NOTE: Deleting TEMPSAS.ITEMACPSA_INTERP (memtype=DATA).
NOTE: Deleting TEMPSAS.ITEMACPSA_PARTIAL_COMBINE (memtype=DATA).
NOTE: Deleting TEMPSAS.ITEMACPSA_PARTIAL_COMBINE_0 (memtype=DATA).
NOTE: Deleting TEMPSAS.ITEMACPSA_PARTIAL_COMBINE_SORT (memtype=DATA).
NOTE: Deleting TEMPSAS.ITEMACPSA_PARTIAL_SUMMARY (memtype=DATA).
NOTE: Deleting TEMPSAS.ITEM_CONC (memtype=DATA).
NOTE: Deleting TEMPSAS.NEW_ITEM_CONC (memtype=DATA).
NOTE: Deleting TEMPSAS.NEW_ITEM_CONC_SORT (memtype=DATA).
NOTE: Deleting TEMPSAS.NEW_ITEM_CONC_SUMMARY (memtype=DATA).
NOTE: Deleting TEMPSAS.NO_UPDATE (memtype=DATA).
NOTE: Deleting TEMPSAS.PARTIAL_ACPSA_NOT1 (memtype=DATA).
NOTE: Deleting TEMPSAS.PARTIAL_ACPSA_NOT1_SORT (memtype=DATA).
NOTE: Deleting TEMPSAS.PARTIAL_ACPSA_REMERGE (memtype=DATA).
NOTE: Deleting TEMPSAS.PARTIAL_ACPSA_SUM1 (memtype=DATA).
NOTE: Deleting TEMPSAS.PARTIAL_ACPSA_SUM_CHECK (memtype=DATA).
NOTE: Deleting TEMPSAS.PARTIAL_ALL_OTHER (memtype=DATA).
NOTE: Deleting TEMPSAS.PARTIAL_ITEM_ACPSA (memtype=DATA).
NOTE: Deleting TEMPSAS.PARTIAL_ITEM_ACPSA_FORMAT (memtype=DATA).
NOTE: Deleting TEMPSAS.PARTIAL_ITEM_ACPSA_SORT (memtype=DATA).
NOTE: Deleting TEMPSAS.PARTIAL_ITEM_ACPSA_SUMMARY (memtype=DATA).
NOTE: Deleting TEMPSAS.PARTIAL_NOT1_ADJ (memtype=DATA).
NOTE: Deleting TEMPSAS.PARTIAL_NOT1_ADJ_SORT (memtype=DATA).
NOTE: Deleting TEMPSAS.PARTIAL_NOT1_CORRECTION (memtype=DATA).
NOTE: Deleting TEMPSAS.TEMP_PI_AFOR1 (memtype=DATA).
12181 run;
12182 quit;
NOTE: PROCEDURE DATASETS used (Total process time):
real time 0.19 seconds
cpu time 0.10 seconds
12183 **************************************************************************************************************;
12184 *********************** Item weight Excel imports **********************************************************;
12185 **************************************************************************************************************;
12186
12187 PROC IMPORT DATAFILE= "&drive\ACPSA_Items\Item_Partials\&prodyr\ACPSA_Items_Partials_23.xlsx"
SYMBOLGEN: Macro variable DRIVE resolves to j:\IEA Products\ACPSA
SYMBOLGEN: Macro variable PRODYR resolves to 2019
12188 OUT= tempsas.itemACPSA_23
12189 DBMS=xlsx;
12190 SHEET="Item_Partial_List_23";
12191 GETNAMES=YES;
12192
12193 run;
NOTE: The import data set has 9 observations and 28 variables.
NOTE: TEMPSAS.ITEMACPSA_23 data set was successfully created.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 0.04 seconds
cpu time 0.01 seconds
12194
12195 PROC IMPORT DATAFILE= "&drive\ACPSA_Items\Item_Partials\&prodyr\ACPSA_Items_Partials_3X.xlsx"
SYMBOLGEN: Macro variable DRIVE resolves to j:\IEA Products\ACPSA
SYMBOLGEN: Macro variable PRODYR resolves to 2019
12196 OUT= tempsas.itemACPSA_3X
12197 DBMS=xlsx;
12198 SHEET="Item_Partial_List_3X";
12199 GETNAMES=YES;
12200
12201 run;
NOTE: The import data set has 35 observations and 28 variables.
NOTE: TEMPSAS.ITEMACPSA_3X data set was successfully created.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 0.05 seconds
cpu time 0.03 seconds
12202
12203 PROC IMPORT DATAFILE= "&drive\ACPSA_Items\Item_Partials\&prodyr\ACPSA_Items_Partials_51.xlsx"
SYMBOLGEN: Macro variable DRIVE resolves to j:\IEA Products\ACPSA
SYMBOLGEN: Macro variable PRODYR resolves to 2019
12204 OUT= tempsas.itemACPSA_51
12205 DBMS=xlsx;
12206 SHEET="Item_Partial_List_51";
12207 GETNAMES=YES;
12208
12209 run;
NOTE: The import data set has 35 observations and 28 variables.
NOTE: TEMPSAS.ITEMACPSA_51 data set was successfully created.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 0.04 seconds
cpu time 0.03 seconds
12210
12211 PROC IMPORT DATAFILE= "&drive\ACPSA_Items\Item_Partials\&prodyr\ACPSA_Items_Partials_54.xlsx"
SYMBOLGEN: Macro variable DRIVE resolves to j:\IEA Products\ACPSA
SYMBOLGEN: Macro variable PRODYR resolves to 2019
12212 OUT= tempsas.itemACPSA_54
12213 DBMS=xlsx;
12214 SHEET="Item_Partial_List_54";
12215 GETNAMES=YES;
12216
12217 run;
NOTE: The import data set has 14 observations and 28 variables.
NOTE: TEMPSAS.ITEMACPSA_54 data set was successfully created.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 0.04 seconds
cpu time 0.01 seconds
12218
12219 PROC IMPORT DATAFILE= "&drive\ACPSA_Items\Item_Partials\&prodyr\ACPSA_Items_Partials_56.xlsx"
SYMBOLGEN: Macro variable DRIVE resolves to j:\IEA Products\ACPSA
SYMBOLGEN: Macro variable PRODYR resolves to 2019
12220 OUT= tempsas.itemACPSA_56
12221 DBMS=xlsx;
12222 SHEET="Item_Partial_List_56";
12223 GETNAMES=YES;
12224
12225 run;
NOTE: The import data set has 1 observations and 28 variables.
NOTE: TEMPSAS.ITEMACPSA_56 data set was successfully created.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 0.03 seconds
cpu time 0.01 seconds
12226
12227 PROC IMPORT DATAFILE= "&drive\ACPSA_Items\Item_Partials\&prodyr\ACPSA_Items_Partials_61.xlsx"
SYMBOLGEN: Macro variable DRIVE resolves to j:\IEA Products\ACPSA
SYMBOLGEN: Macro variable PRODYR resolves to 2019
12228 OUT= tempsas.itemACPSA_61
12229 DBMS=xlsx;
12230 SHEET="Item_Partial_List_61";
12231 GETNAMES=YES;
12232
12233 run;
NOTE: The import data set has 9 observations and 28 variables.
NOTE: TEMPSAS.ITEMACPSA_61 data set was successfully created.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 0.04 seconds
cpu time 0.03 seconds
12234
12235 PROC IMPORT DATAFILE= "&drive\ACPSA_Items\Item_Partials\&prodyr\ACPSA_Items_Partials_71.xlsx"
SYMBOLGEN: Macro variable DRIVE resolves to j:\IEA Products\ACPSA
SYMBOLGEN: Macro variable PRODYR resolves to 2019
12236 OUT= tempsas.itemACPSA_71
12237 DBMS=xlsx;
12238 SHEET="Item_Partial_List_71";
12239 GETNAMES=YES;
12240
12241 run;
NOTE: The import data set has 61 observations and 28 variables.
NOTE: TEMPSAS.ITEMACPSA_71 data set was successfully created.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 0.05 seconds
cpu time 0.06 seconds
12242
12243 PROC IMPORT DATAFILE= "&drive\ACPSA_Items\Item_Partials\&prodyr\ACPSA_Items_Partials_81.xlsx"
SYMBOLGEN: Macro variable DRIVE resolves to j:\IEA Products\ACPSA
SYMBOLGEN: Macro variable PRODYR resolves to 2019
12244 OUT= tempsas.itemACPSA_81
12245 DBMS=xlsx;
12246 SHEET="Item_Partial_List_81";
12247 GETNAMES=YES;
12248
12249 run;
NOTE: The import data set has 6 observations and 28 variables.
NOTE: TEMPSAS.ITEMACPSA_81 data set was successfully created.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 0.04 seconds
cpu time 0.01 seconds
12250
12251 PROC IMPORT DATAFILE= "&drive\ACPSA_Items\Item_Partials\&prodyr\ACPSA_Items_Partials_99.xlsx"
SYMBOLGEN: Macro variable DRIVE resolves to j:\IEA Products\ACPSA
SYMBOLGEN: Macro variable PRODYR resolves to 2019
12252 OUT= tempsas.itemACPSA_99
12253 DBMS=xlsx;
12254 SHEET="Item_Partial_List_99";
12255 GETNAMES=YES;
12256
12257 run;
NOTE: The import data set has 6 observations and 28 variables.
NOTE: TEMPSAS.ITEMACPSA_99 data set was successfully created.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 0.03 seconds
cpu time 0.01 seconds
12258
12259 PROC IMPORT DATAFILE= "&drive\ACPSA_Items\Item_Partials\&prodyr\ACPSA_Partial_Item_Ratio_Interpolation.xlsx"
SYMBOLGEN: Macro variable DRIVE resolves to j:\IEA Products\ACPSA
SYMBOLGEN: Macro variable PRODYR resolves to 2019
12260 OUT= tempsas.itemACPSA_Interp
12261 DBMS=xlsx
12262 replace;
12263 SHEET="Item_Partial_List_Interp";
12264 GETNAMES=YES;
12265
12266 run;
NOTE: The import data set has 58 observations and 27 variables.
NOTE: TEMPSAS.ITEMACPSA_INTERP data set was successfully created.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 0.04 seconds
cpu time 0.03 seconds
12267 *********************************************************************************************************;
12268
12269 %macro prep_series;
12270 %do ioyear = &b_ioYear %to &e_ioyear;
12271
12272 **************************************************************************************************************;
12273 ******************************* Item concordance weight update **********************************************;
12274 /*
12275
12276 This section updates the weights in the item concordance for the item partials. Some of the itemCodes have weights
12277 that change from year to year. To incorporate those into the ACPSA, the item concordance needs to be changed for
12278 each processing year. The weights that don't change will remain as they were but the ones that do change will
12279 be updated.
12280
12281 */
12282 ***************************************************************************************************************;
12283
12284
12285 data tempsas.current_item_concordance;
12286 set concord.&acpsaitemcc;
12287
12288 proc sort data=tempsas.current_item_concordance out=tempsas.current_item_concordance_sort;
12289 by itemCode itemACPSA;
12290 run;
12291
12292 data tempsas.itemACPSA_partial_combine_0;
12293 length itemCode $ 20 itemDescr $ 300 itemACPSA $ 9 itemACPSADescr $ 90;
12294 set tempsas.itemACPSA_23 tempsas.itemACPSA_3X tempsas.itemACPSA_51 tempsas.itemACPSA_54 tempsas.itemACPSA_56
12294! tempsas.itemACPSA_61 tempsas.itemACPSA_71 tempsas.itemACPSA_81 tempsas.itemACPSA_99;
12295
12296 where upcase(isInterp) ne 'Y';
12297
12298 format itemCode $20. itemDescr $300. itemACPSA $9. itemACPSADescr $90.;
12299
12300 data tempsas.itemACPSA_partial_combine;
12301 set tempsas.itemACPSA_partial_combine_0 tempsas.itemACPSA_Interp;
12302
12303 proc sort data=tempsas.itemACPSA_partial_combine out=tempsas.itemACPSA_partial_combine_sort;
12304 by itemCode itemACPSA;
12305
12306 proc summary data=tempsas.current_item_concordance_sort;
12307 by itemCode;
12308 id itemDescr;
12309 var weight;
12310 output out=tempsas.current_item_concordance_sum(drop=_type_) sum= ;
12311 run;
12312
12313 /* itemCode weight check */
12314 proc summary data=tempsas.itemACPSA_partial_combine_sort;
12315 by itemCode;
12316 id itemDescr;
12317 var y1997 - y2017;
12318 output out=tempsas.itemACPSA_partial_summary(drop=_type_) sum= ;
12319 run;
12320
12321 data tempsas.partial_item_ACPSA
12322 tempsas.partial_all_other
12323 tempsas.no_update (keep=itemCode_id itemCode itemDescr partial weight itemACPSA itemACPSADescr);
12324 merge tempsas.current_item_concordance_sort(in=a) tempsas.itemACPSA_partial_combine_sort (in=b);
12325 by itemCode;
12326
12327 if b=1 and itemACPSA ne 'item99' then output tempsas.partial_item_ACPSA;
12328 else if b=1 and itemACPSA = 'item99' then output tempsas.partial_all_other;
12329 else output tempsas.no_update;
12330
12331 run;
12332
12333 data tempsas.partial_item_ACPSA_format;
12334 set tempsas.partial_item_ACPSA;
12335
12336 weight = y&ioyear;
12337
12338 keep itemCode_id itemCode itemDescr partial weight itemACPSA itemACPSADescr;
12339 run;
12340
12341 proc sort data=tempsas.partial_item_ACPSA_format out=tempsas.partial_item_ACPSA_sort;
12342 by itemCode_id itemCode;
12343
12344 proc summary data=tempsas.partial_item_ACPSA_sort;
12345 by itemCode_id itemCode;
12346 id itemDescr;
12347 var weight;
12348 output out=tempsas.partial_item_ACPSA_summary(drop=_type_) sum=;
12349 run;
12350
12351 data tempsas.partial_ACPSA_sum1
12352 tempsas.partial_ACPSA_not1;
12353 set tempsas.partial_item_ACPSA_summary;
12354
12355 if weight < 0.999 then output tempsas.partial_ACPSA_not1;
12356 else output tempsas.partial_ACPSA_sum1;
12357
12358 run;
12359
12360 proc sort data=tempsas.partial_ACPSA_not1 out=tempsas.partial_ACPSA_not1_sort;
12361 by itemCode_id itemCode;
12362
12363 data tempsas.partial_not1_adj;
12364 merge tempsas.partial_ACPSA_not1_sort(in=a rename=(weight=total_weight)) tempsas.partial_item_ACPSA_sort;
12365 by itemCode_id;
12366
12367 if a=1;
12368
12369 weight = 1 - total_weight;
12370 itemACPSA = 'item99';
12371 itemACPSADescr = 'All Other Commodities';
12372
12373 drop _freq_ total_weight;
12374
12375 run;
12376
12377 proc sort data=tempsas.partial_not1_adj out=tempsas.partial_not1_adj_sort;
12378 by itemCode_id itemCode itemDescr partial weight itemACPSA itemACPSADescr;
12379
12380 proc summary data=tempsas.partial_not1_adj_sort;
12381 by itemCode_id itemCode itemDescr partial weight itemACPSA itemACPSADescr;
12382 output out=tempsas.partial_not1_correction(drop=_type_ _freq_);
12383 run;
12384
12385 data tempsas.partial_ACPSA_remerge;
12386 set tempsas.partial_item_ACPSA_format tempsas.partial_not1_correction;
12387 run;
12388
12389 ***************** Checks the sum of partial ACPSA items. Weights should sum to 1. **************;
12390 data tempsas.all_partials_remerged;
12391 set tempsas.partial_ACPSA_remerge;
12392
12393 proc sort data=tempsas.all_partials_remerged out=tempsas.all_partials_remerged_sort;
12394 by itemCode;
12395
12396 proc summary data=tempsas.all_partials_remerged_sort;
12397 by itemCode;
12398 id itemDescr;
12399 var weight;
12400 output out=tempsas.partial_ACPSA_sum_check(drop=_type_ _freq_) sum= ;
12401 run; /*sbo 9.2.2016 - Check works for 2012 */
12402
12403 proc print data=tempsas.partial_ACPSA_sum_check noobs;
12404 title3 "Partial ACPSA item weight check, &ioYear";
12405 TITLE4 "%sysfunc(today(),weekdate.), %sysfunc(time(),time8.)";
12406 var itemCode weight;
12407 run;
12408 ****************************************************************************;
12409
12410 data tempsas.new_item_conc;
12411 set tempsas.partial_ACPSA_remerge tempsas.no_update;
12412
12413 itemACPSADescr = propcase(itemACPSADescr);
12414
12415 proc sort data=tempsas.new_item_conc out=tempsas.new_item_conc_sort;
12416 by itemCode itemACPSA;
12417
12418 *******************************************************************************************;
12419 /* Check on new concordance to make sure weights sum to 1 */
12420 proc summary data = tempsas.new_item_conc_sort;
12421 by itemCode;
12422 id itemDescr;
12423 var weight;
12424 output out=tempsas.new_item_conc_summary(drop=_type_ _freq_) sum= ;
12425 run;
12426
12427 data tempsas.good_conc
12428 tempsas.bad_conc;
12429 set tempsas.new_item_conc_summary;
12430
12431 if weight ne 1 then output tempsas.bad_conc;
12432 else output tempsas.good_conc;
12433
12434 run;
12435
12436 ******************************************************************************************
12437 *****************************************************************************************************
12438 ACPSA item concordance
12439 *****************************************************************************************************;
12440
12441 data tempsas.item_conc;
12442 set tempsas.new_item_conc_sort;
12443
12444 itemACPSADescr = propcase(itemACPSADescr);
12445
12446
12447 /*
12448 The following program creates three tables for used in calculating Laspeyres and Pasche quantity and price
12449 indices. The methodology follows that of a program written by Gabriel Medeiros to calculate prices for annual
12450 IO.
12451 */
12452
12453 proc sql;
12454
12455 connect to odbc as IEtest17
12456 (datasrc="IEtest17");
12457
12458 create table tempsas.temp_PI_afor1 as
12459
12460 select *
12461 from connection to IEtest17
12462
12463 (select riv.itmCode_id as item_id
12464 , riv.itmCode as itemCode
12465 , riv.period as ioYear
12466 , riv.basePeriod as basingYear
12467 , riv.pRelGO as PI
12468 , oiv.val as basicVal
12469 from rel_itmView riv
12470 join occ.itmView oiv
12471 on riv.itmCode_id = oiv.itmCode_id
12472 and riv.datasetPeriod_id = oiv.datasetPeriod_id
12473 and riv.dataSet_id = oiv.dataSet_id
12474 where riv.datasetName = 'annual17'
12475 and riv.valType = 'DOM'
12476 and oiv.period = '1998'
12477 and riv.period = '1998'
12478 order by riv.itmCode, riv.period
12479 );
12480
12481 disconnect from IETest17;
12482
12483 proc sql;
12484 create table tempsas.temp_PI_&ioYear as
12485
12486 select tab.itmCode_id as item_id label="item_id"
12487 , tab.itmCode as itemCode label="itemCode"
12488 , &ioYear as ioYear label="ioYear"
12489 , tab.basePeriod as basingYear label="basingYear"
12490 , tab.pRelGO as PI label="PI"
12491 , tab.basival as basicVal
12492 , ic.itemACPSA
12493 , ic.itemACPSADescr
12494 from tempsas.temp_PI_afor1 tab
12495 join tempsas.item_conc ic
12496 on tab.itemCode = ic.itemCode
12497 /*where tab.datasetName = &dsName
12498 and tab.valType = 'DOM'
12499 and oiv.period = "&ioYear"
12500 and tab.period = &ioYear.*/
12501 ;
12502 quit;
12503
12504
12505 %end;
12506 %mend prep_series;
12507
12508 %prep_series
SYMBOLGEN: Macro variable B_IOYEAR resolves to 1998
SYMBOLGEN: Macro variable E_IOYEAR resolves to 1998
MPRINT(PREP_SERIES):
**************************************************************************************************************;
MPRINT(PREP_SERIES): ******************************* Item concordance weight update
**********************************************;
MPRINT(PREP_SERIES):
***************************************************************************************************************;
MPRINT(PREP_SERIES): data tempsas.current_item_concordance;
SYMBOLGEN: Macro variable ACPSAITEMCC resolves to acpsa_item_concord_2012naics
MPRINT(PREP_SERIES): set concord.acpsa_item_concord_2012naics;
NOTE: There were 5318 observations read from the data set CONCORD.ACPSA_ITEM_CONCORD_2012NAICS.
NOTE: The data set TEMPSAS.CURRENT_ITEM_CONCORDANCE has 5318 observations and 7 variables.
NOTE: DATA statement used (Total process time):
real time 0.10 seconds
cpu time 0.01 seconds
MPRINT(PREP_SERIES): proc sort data=tempsas.current_item_concordance out=tempsas.current_item_concordance_sort;
MPRINT(PREP_SERIES): by itemCode itemACPSA;
MPRINT(PREP_SERIES): run;
NOTE: There were 5318 observations read from the data set TEMPSAS.CURRENT_ITEM_CONCORDANCE.
NOTE: The data set TEMPSAS.CURRENT_ITEM_CONCORDANCE_SORT has 5318 observations and 7 variables.
NOTE: PROCEDURE SORT used (Total process time):
real time 0.03 seconds
cpu time 0.03 seconds
MPRINT(PREP_SERIES): data tempsas.itemACPSA_partial_combine_0;
MPRINT(PREP_SERIES): length itemCode $ 20 itemDescr $ 300 itemACPSA $ 9 itemACPSADescr $ 90;
MPRINT(PREP_SERIES): set tempsas.itemACPSA_23 tempsas.itemACPSA_3X tempsas.itemACPSA_51 tempsas.itemACPSA_54
tempsas.itemACPSA_56 tempsas.itemACPSA_61 tempsas.itemACPSA_71 tempsas.itemACPSA_81 tempsas.itemACPSA_99;
MPRINT(PREP_SERIES): where upcase(isInterp) ne 'Y';
MPRINT(PREP_SERIES): format itemCode $20. itemDescr $300. itemACPSA $9. itemACPSADescr $90.;
NOTE: There were 9 observations read from the data set TEMPSAS.ITEMACPSA_23.
WHERE UPCASE(isInterp) not = 'Y';
NOTE: There were 35 observations read from the data set TEMPSAS.ITEMACPSA_3X.
WHERE UPCASE(isInterp) not = 'Y';
NOTE: There were 35 observations read from the data set TEMPSAS.ITEMACPSA_51.
WHERE UPCASE(isInterp) not = 'Y';
NOTE: There were 12 observations read from the data set TEMPSAS.ITEMACPSA_54.
WHERE UPCASE(isInterp) not = 'Y';
NOTE: There were 1 observations read from the data set TEMPSAS.ITEMACPSA_56.
WHERE UPCASE(isInterp) not = 'Y';
NOTE: There were 9 observations read from the data set TEMPSAS.ITEMACPSA_61.
WHERE UPCASE(isInterp) not = 'Y';
NOTE: There were 10 observations read from the data set TEMPSAS.ITEMACPSA_71.
WHERE UPCASE(isInterp) not = 'Y';
NOTE: There were 6 observations read from the data set TEMPSAS.ITEMACPSA_81.
WHERE UPCASE(isInterp) not = 'Y';
NOTE: There were 1 observations read from the data set TEMPSAS.ITEMACPSA_99.
WHERE UPCASE(isInterp) not = 'Y';
NOTE: The data set TEMPSAS.ITEMACPSA_PARTIAL_COMBINE_0 has 118 observations and 28 variables.
NOTE: DATA statement used (Total process time):
real time 0.02 seconds
cpu time 0.01 seconds
MPRINT(PREP_SERIES): data tempsas.itemACPSA_partial_combine;
MPRINT(PREP_SERIES): set tempsas.itemACPSA_partial_combine_0 tempsas.itemACPSA_Interp;
NOTE: There were 118 observations read from the data set TEMPSAS.ITEMACPSA_PARTIAL_COMBINE_0.
NOTE: There were 58 observations read from the data set TEMPSAS.ITEMACPSA_INTERP.
NOTE: The data set TEMPSAS.ITEMACPSA_PARTIAL_COMBINE has 176 observations and 28 variables.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds
MPRINT(PREP_SERIES): proc sort data=tempsas.itemACPSA_partial_combine out=tempsas.itemACPSA_partial_combine_sort;
MPRINT(PREP_SERIES): by itemCode itemACPSA;
NOTE: There were 176 observations read from the data set TEMPSAS.ITEMACPSA_PARTIAL_COMBINE.
NOTE: The data set TEMPSAS.ITEMACPSA_PARTIAL_COMBINE_SORT has 176 observations and 28 variables.
NOTE: PROCEDURE SORT used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds
MPRINT(PREP_SERIES): proc summary data=tempsas.current_item_concordance_sort;
MPRINT(PREP_SERIES): by itemCode;
MPRINT(PREP_SERIES): id itemDescr;
MPRINT(PREP_SERIES): var weight;
MPRINT(PREP_SERIES): output out=tempsas.current_item_concordance_sum(drop=_type_) sum= ;
MPRINT(PREP_SERIES): run;
NOTE: There were 5318 observations read from the data set TEMPSAS.CURRENT_ITEM_CONCORDANCE_SORT.
NOTE: The data set TEMPSAS.CURRENT_ITEM_CONCORDANCE_SUM has 4708 observations and 4 variables.
NOTE: PROCEDURE SUMMARY used (Total process time):
real time 0.05 seconds
cpu time 0.04 seconds
MPRINT(PREP_SERIES): proc summary data=tempsas.itemACPSA_partial_combine_sort;
MPRINT(PREP_SERIES): by itemCode;
MPRINT(PREP_SERIES): id itemDescr;
MPRINT(PREP_SERIES): var y1997 - y2017;
MPRINT(PREP_SERIES): output out=tempsas.itemACPSA_partial_summary(drop=_type_) sum= ;
MPRINT(PREP_SERIES): run;
NOTE: There were 176 observations read from the data set TEMPSAS.ITEMACPSA_PARTIAL_COMBINE_SORT.
NOTE: The data set TEMPSAS.ITEMACPSA_PARTIAL_SUMMARY has 120 observations and 24 variables.
NOTE: PROCEDURE SUMMARY used (Total process time):
real time 0.02 seconds
cpu time 0.01 seconds
MPRINT(PREP_SERIES): data tempsas.partial_item_ACPSA tempsas.partial_all_other tempsas.no_update (keep=itemCode_id itemCode
itemDescr partial weight itemACPSA itemACPSADescr);
MPRINT(PREP_SERIES): merge tempsas.current_item_concordance_sort(in=a) tempsas.itemACPSA_partial_combine_sort (in=b);
MPRINT(PREP_SERIES): by itemCode;
MPRINT(PREP_SERIES): if b=1 and itemACPSA ne 'item99' then output tempsas.partial_item_ACPSA;
MPRINT(PREP_SERIES): else if b=1 and itemACPSA = 'item99' then output tempsas.partial_all_other;
MPRINT(PREP_SERIES): else output tempsas.no_update;
MPRINT(PREP_SERIES): run;
WARNING: Multiple lengths were specified for the variable itemDescr by input data set(s). This may cause truncation of data.
WARNING: Multiple lengths were specified for the variable itemACPSA by input data set(s). This may cause truncation of data.
WARNING: Multiple lengths were specified for the variable itemACPSADescr by input data set(s). This may cause truncation of data.
NOTE: MERGE statement has more than one data set with repeats of BY values.
NOTE: There were 5318 observations read from the data set TEMPSAS.CURRENT_ITEM_CONCORDANCE_SORT.
NOTE: There were 176 observations read from the data set TEMPSAS.ITEMACPSA_PARTIAL_COMBINE_SORT.
NOTE: The data set TEMPSAS.PARTIAL_ITEM_ACPSA has 177 observations and 29 variables.
NOTE: The data set TEMPSAS.PARTIAL_ALL_OTHER has 115 observations and 29 variables.
NOTE: The data set TEMPSAS.NO_UPDATE has 5027 observations and 7 variables.
NOTE: DATA statement used (Total process time):
real time 0.05 seconds
cpu time 0.03 seconds
MPRINT(PREP_SERIES): data tempsas.partial_item_ACPSA_format;
MPRINT(PREP_SERIES): set tempsas.partial_item_ACPSA;
SYMBOLGEN: Macro variable IOYEAR resolves to 1998
MPRINT(PREP_SERIES): weight = y1998;
MPRINT(PREP_SERIES): keep itemCode_id itemCode itemDescr partial weight itemACPSA itemACPSADescr;
MPRINT(PREP_SERIES): run;
NOTE: There were 177 observations read from the data set TEMPSAS.PARTIAL_ITEM_ACPSA.
NOTE: The data set TEMPSAS.PARTIAL_ITEM_ACPSA_FORMAT has 177 observations and 7 variables.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.00 seconds
MPRINT(PREP_SERIES): proc sort data=tempsas.partial_item_ACPSA_format out=tempsas.partial_item_ACPSA_sort;
MPRINT(PREP_SERIES): by itemCode_id itemCode;
NOTE: There were 177 observations read from the data set TEMPSAS.PARTIAL_ITEM_ACPSA_FORMAT.
NOTE: The data set TEMPSAS.PARTIAL_ITEM_ACPSA_SORT has 177 observations and 7 variables.
NOTE: PROCEDURE SORT used (Total process time):
real time 0.01 seconds
cpu time 0.00 seconds
MPRINT(PREP_SERIES): proc summary data=tempsas.partial_item_ACPSA_sort;
MPRINT(PREP_SERIES): by itemCode_id itemCode;
MPRINT(PREP_SERIES): id itemDescr;
MPRINT(PREP_SERIES): var weight;
MPRINT(PREP_SERIES): output out=tempsas.partial_item_ACPSA_summary(drop=_type_) sum=;
MPRINT(PREP_SERIES): run;
NOTE: There were 177 observations read from the data set TEMPSAS.PARTIAL_ITEM_ACPSA_SORT.
NOTE: The data set TEMPSAS.PARTIAL_ITEM_ACPSA_SUMMARY has 121 observations and 5 variables.
NOTE: PROCEDURE SUMMARY used (Total process time):
real time 0.03 seconds
cpu time 0.03 seconds
MPRINT(PREP_SERIES): data tempsas.partial_ACPSA_sum1 tempsas.partial_ACPSA_not1;
MPRINT(PREP_SERIES): set tempsas.partial_item_ACPSA_summary;
MPRINT(PREP_SERIES): if weight < 0.999 then output tempsas.partial_ACPSA_not1;
MPRINT(PREP_SERIES): else output tempsas.partial_ACPSA_sum1;
MPRINT(PREP_SERIES): run;
NOTE: There were 121 observations read from the data set TEMPSAS.PARTIAL_ITEM_ACPSA_SUMMARY.
NOTE: The data set TEMPSAS.PARTIAL_ACPSA_SUM1 has 9 observations and 5 variables.
NOTE: The data set TEMPSAS.PARTIAL_ACPSA_NOT1 has 112 observations and 5 variables.
NOTE: DATA statement used (Total process time):
real time 0.02 seconds
cpu time 0.03 seconds
MPRINT(PREP_SERIES): proc sort data=tempsas.partial_ACPSA_not1 out=tempsas.partial_ACPSA_not1_sort;
MPRINT(PREP_SERIES): by itemCode_id itemCode;
NOTE: There were 112 observations read from the data set TEMPSAS.PARTIAL_ACPSA_NOT1.
NOTE: The data set TEMPSAS.PARTIAL_ACPSA_NOT1_SORT has 112 observations and 5 variables.
NOTE: PROCEDURE SORT used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds
MPRINT(PREP_SERIES): data tempsas.partial_not1_adj;
MPRINT(PREP_SERIES): merge tempsas.partial_ACPSA_not1_sort(in=a rename=(weight=total_weight)) tempsas.partial_item_ACPSA_sort;
MPRINT(PREP_SERIES): by itemCode_id;
MPRINT(PREP_SERIES): if a=1;
MPRINT(PREP_SERIES): weight = 1 - total_weight;
MPRINT(PREP_SERIES): itemACPSA = 'item99';
MPRINT(PREP_SERIES): itemACPSADescr = 'All Other Commodities';
MPRINT(PREP_SERIES): drop _freq_ total_weight;
MPRINT(PREP_SERIES): run;
NOTE: There were 112 observations read from the data set TEMPSAS.PARTIAL_ACPSA_NOT1_SORT.
NOTE: There were 177 observations read from the data set TEMPSAS.PARTIAL_ITEM_ACPSA_SORT.
NOTE: The data set TEMPSAS.PARTIAL_NOT1_ADJ has 114 observations and 7 variables.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.03 seconds
MPRINT(PREP_SERIES): proc sort data=tempsas.partial_not1_adj out=tempsas.partial_not1_adj_sort;
MPRINT(PREP_SERIES): by itemCode_id itemCode itemDescr partial weight itemACPSA itemACPSADescr;
NOTE: There were 114 observations read from the data set TEMPSAS.PARTIAL_NOT1_ADJ.
NOTE: The data set TEMPSAS.PARTIAL_NOT1_ADJ_SORT has 114 observations and 7 variables.
NOTE: PROCEDURE SORT used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds
MPRINT(PREP_SERIES): proc summary data=tempsas.partial_not1_adj_sort;
MPRINT(PREP_SERIES): by itemCode_id itemCode itemDescr partial weight itemACPSA itemACPSADescr;
MPRINT(PREP_SERIES): output out=tempsas.partial_not1_correction(drop=_type_ _freq_);
MPRINT(PREP_SERIES): run;
NOTE: There were 114 observations read from the data set TEMPSAS.PARTIAL_NOT1_ADJ_SORT.
NOTE: The data set TEMPSAS.PARTIAL_NOT1_CORRECTION has 112 observations and 7 variables.
NOTE: PROCEDURE SUMMARY used (Total process time):
real time 0.02 seconds
cpu time 0.03 seconds
MPRINT(PREP_SERIES): data tempsas.partial_ACPSA_remerge;
MPRINT(PREP_SERIES): set tempsas.partial_item_ACPSA_format tempsas.partial_not1_correction;
MPRINT(PREP_SERIES): run;
NOTE: There were 177 observations read from the data set TEMPSAS.PARTIAL_ITEM_ACPSA_FORMAT.
NOTE: There were 112 observations read from the data set TEMPSAS.PARTIAL_NOT1_CORRECTION.
NOTE: The data set TEMPSAS.PARTIAL_ACPSA_REMERGE has 289 observations and 7 variables.
NOTE: DATA statement used (Total process time):
real time 0.02 seconds
cpu time 0.01 seconds
MPRINT(PREP_SERIES): ***************** Checks the sum of partial ACPSA items. Weights should sum to 1. **************;
MPRINT(PREP_SERIES): data tempsas.all_partials_remerged;
MPRINT(PREP_SERIES): set tempsas.partial_ACPSA_remerge;
NOTE: There were 289 observations read from the data set TEMPSAS.PARTIAL_ACPSA_REMERGE.
NOTE: The data set TEMPSAS.ALL_PARTIALS_REMERGED has 289 observations and 7 variables.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds
MPRINT(PREP_SERIES): proc sort data=tempsas.all_partials_remerged out=tempsas.all_partials_remerged_sort;
MPRINT(PREP_SERIES): by itemCode;
NOTE: There were 289 observations read from the data set TEMPSAS.ALL_PARTIALS_REMERGED.
NOTE: The data set TEMPSAS.ALL_PARTIALS_REMERGED_SORT has 289 observations and 7 variables.
NOTE: PROCEDURE SORT used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds
MPRINT(PREP_SERIES): proc summary data=tempsas.all_partials_remerged_sort;
MPRINT(PREP_SERIES): by itemCode;
MPRINT(PREP_SERIES): id itemDescr;
MPRINT(PREP_SERIES): var weight;
MPRINT(PREP_SERIES): output out=tempsas.partial_ACPSA_sum_check(drop=_type_ _freq_) sum= ;
MPRINT(PREP_SERIES): run;
NOTE: There were 289 observations read from the data set TEMPSAS.ALL_PARTIALS_REMERGED_SORT.
NOTE: The data set TEMPSAS.PARTIAL_ACPSA_SUM_CHECK has 120 observations and 3 variables.
NOTE: PROCEDURE SUMMARY used (Total process time):
real time 0.03 seconds
cpu time 0.03 seconds
MPRINT(PREP_SERIES): proc print data=tempsas.partial_ACPSA_sum_check noobs;
SYMBOLGEN: Macro variable IOYEAR resolves to 1998
MPRINT(PREP_SERIES): title3 "Partial ACPSA item weight check, 1998";
MPRINT(PREP_SERIES): TITLE4 " Tuesday, August 27, 2019, 14:20:49";
MPRINT(PREP_SERIES): var itemCode weight;
MPRINT(PREP_SERIES): run;
NOTE: There were 120 observations read from the data set TEMPSAS.PARTIAL_ACPSA_SUM_CHECK.
NOTE: PROCEDURE PRINT used (Total process time):
real time 0.02 seconds
cpu time 0.00 seconds
MPRINT(PREP_SERIES): ****************************************************************************;
MPRINT(PREP_SERIES): data tempsas.new_item_conc;
MPRINT(PREP_SERIES): set tempsas.partial_ACPSA_remerge tempsas.no_update;
MPRINT(PREP_SERIES): itemACPSADescr = propcase(itemACPSADescr);
NOTE: There were 289 observations read from the data set TEMPSAS.PARTIAL_ACPSA_REMERGE.
NOTE: There were 5027 observations read from the data set TEMPSAS.NO_UPDATE.
NOTE: The data set TEMPSAS.NEW_ITEM_CONC has 5316 observations and 7 variables.
NOTE: DATA statement used (Total process time):
real time 0.02 seconds
cpu time 0.01 seconds
MPRINT(PREP_SERIES): proc sort data=tempsas.new_item_conc out=tempsas.new_item_conc_sort;
MPRINT(PREP_SERIES): by itemCode itemACPSA;
MPRINT(PREP_SERIES): *******************************************************************************************;
NOTE: There were 5316 observations read from the data set TEMPSAS.NEW_ITEM_CONC.
NOTE: The data set TEMPSAS.NEW_ITEM_CONC_SORT has 5316 observations and 7 variables.
NOTE: PROCEDURE SORT used (Total process time):
real time 0.03 seconds
cpu time 0.03 seconds
MPRINT(PREP_SERIES): proc summary data = tempsas.new_item_conc_sort;
MPRINT(PREP_SERIES): by itemCode;
MPRINT(PREP_SERIES): id itemDescr;
MPRINT(PREP_SERIES): var weight;
MPRINT(PREP_SERIES): output out=tempsas.new_item_conc_summary(drop=_type_ _freq_) sum= ;
MPRINT(PREP_SERIES): run;
NOTE: There were 5316 observations read from the data set TEMPSAS.NEW_ITEM_CONC_SORT.
NOTE: The data set TEMPSAS.NEW_ITEM_CONC_SUMMARY has 4709 observations and 3 variables.
NOTE: PROCEDURE SUMMARY used (Total process time):
real time 0.05 seconds
cpu time 0.06 seconds
MPRINT(PREP_SERIES): data tempsas.good_conc tempsas.bad_conc;
MPRINT(PREP_SERIES): set tempsas.new_item_conc_summary;
MPRINT(PREP_SERIES): if weight ne 1 then output tempsas.bad_conc;
MPRINT(PREP_SERIES): else output tempsas.good_conc;
MPRINT(PREP_SERIES): run;
NOTE: There were 4709 observations read from the data set TEMPSAS.NEW_ITEM_CONC_SUMMARY.
NOTE: The data set TEMPSAS.GOOD_CONC has 4336 observations and 3 variables.
NOTE: The data set TEMPSAS.BAD_CONC has 373 observations and 3 variables.
NOTE: DATA statement used (Total process time):
real time 0.03 seconds
cpu time 0.01 seconds
MPRINT(PREP_SERIES): ******************************************************************************************
***************************************************************************************************** ACPSA item concordance
*****************************************************************************************************;
MPRINT(PREP_SERIES): data tempsas.item_conc;
MPRINT(PREP_SERIES): set tempsas.new_item_conc_sort;
MPRINT(PREP_SERIES): itemACPSADescr = propcase(itemACPSADescr);
NOTE: There were 5316 observations read from the data set TEMPSAS.NEW_ITEM_CONC_SORT.
NOTE: The data set TEMPSAS.ITEM_CONC has 5316 observations and 7 variables.
NOTE: DATA statement used (Total process time):
real time 0.02 seconds
cpu time 0.03 seconds
MPRINT(PREP_SERIES): proc sql;
MPRINT(PREP_SERIES): connect to odbc as IEtest17 (datasrc="IEtest17");
MPRINT(PREP_SERIES): create table tempsas.temp_PI_afor1 as select * from connection to IEtest17 (select riv.itmCode_id as
item_id , riv.itmCode as itemCode , riv.period as ioYear , riv.basePeriod as basingYear , riv.pRelGO as PI , oiv.val as basicVal
from rel_itmView riv join occ.itmView oiv on riv.itmCode_id = oiv.itmCode_id and riv.datasetPeriod_id = oiv.datasetPeriod_id and
riv.dataSet_id = oiv.dataSet_id where riv.datasetName = 'annual17' and riv.valType = 'DOM' and oiv.period = '1998' and riv.period
= '1998' order by riv.itmCode, riv.period );
NOTE: Table TEMPSAS.TEMP_PI_AFOR1 created, with 5505 rows and 6 columns.
MPRINT(PREP_SERIES): disconnect from IETest17;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.75 seconds
cpu time 0.04 seconds
MPRINT(PREP_SERIES): proc sql;
SYMBOLGEN: Macro variable IOYEAR resolves to 1998
SYMBOLGEN: Macro variable IOYEAR resolves to 1998
MPRINT(PREP_SERIES): create table tempsas.temp_PI_1998 as select tab.itmCode_id as item_id label="item_id" , tab.itmCode as
itemCode label="itemCode" , 1998 as ioYear label="ioYear" , tab.basePeriod as basingYear label="basingYear" , tab.pRelGO as PI
label="PI" , tab.basival as basicVal , ic.itemACPSA , ic.itemACPSADescr from tempsas.temp_PI_afor1 tab join tempsas.item_conc ic
on tab.itemCode = ic.itemCode ;
ERROR: Column itmCode_id could not be found in the table/view identified with the correlation name TAB.
ERROR: Column itmCode could not be found in the table/view identified with the correlation name TAB.
ERROR: Column basePeriod could not be found in the table/view identified with the correlation name TAB.
ERROR: Column pRelGO could not be found in the table/view identified with the correlation name TAB.
ERROR: Column basival could not be found in the table/view identified with the correlation name TAB.
MPRINT(PREP_SERIES): quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
cpu time 0.01 seconds
2 REPLIES 2
Diamond | Level 26
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
ERROR: Column itmCode_id could not be found in the table/view identified with the correlation name TAB.
It means exactly what it says. There is no such variable itmCode_id in the table with alias name TAB. You need to find out if you made a spelling error, or if the variable really isn't there because of a programming error somewhere.
Don't reply with "Yes it is in there". Show us the PROC CONTENTS of that table if you think it is in there.
--
Paige Miller
Paige Miller
Super User
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Why did you post such a long log?
Start from the first ERROR message and trace back to see what is happening.
ERROR: Column itmCode_id could not be found in the table/view identified with the correlation name TAB.So now check what dataset (or subquery) did you call TAB?
from tempsas.temp_PI_afor1 tab Now check where you made that dataset.
create table tempsas.temp_PI_afor1 as select * from connection to IEtest17 (select riv.itmCode_id as
item_id , riv.itmCode as itemCode , riv.period as ioYear , riv.basePeriod as basingYear , riv.pRelGO as PI , oiv.val as basicVal
from rel_itmView riv join occ.itmView oiv on riv.itmCode_id = oiv.itmCode_id and riv.datasetPeriod_id = oiv.datasetPeriod_id and
riv.dataSet_id = oiv.dataSet_id where riv.datasetName = 'annual17' and riv.valType = 'DOM' and oiv.period = '1998' and riv.period
= '1998' order by riv.itmCode, riv.period );And see if there is a variable with that name
select riv.itmCode_id as item_idAnd you will see that the reason there is no itmCode_id variable is because you renamed it to item_id.
SAS Innovate 2025: Save the Date
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
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.
SAS Training: Just a Click Away
Ready to level-up your skills? Choose your own adventure.
Discussion stats
-
2 replies
-
08-27-2019 02:29 PM
-
472 views
-
2 likes
-
3 in conversation
-
