16 The SAS System 10:24 Tuesday, February 7, 2017 443 /*==========================================================================* 444 * Step: Table Loader A5V3LOE4.BD00048E * 445 * Transform: Table Loader (Version 2.1) * 446 * Description: * 447 * * 448 * Source Table: Extract - work.W3HR6V3 A5V3LOE4.BH0001CD * 449 * Target Table: BIF_TEST_LOAD - SRCDATA.BIF_TEST_LOAD A5V3LOE4.BV00001U * 450 *==========================================================================*/ 451 452 %let transformID = %quote(A5V3LOE4.BD00048E); 453 %let trans_rc = 0; 454 %let etls_stepStartTime = %sysfunc(datetime(), datetime20.); 455 456 /* Access the data for Library SRC Extract Tables */ 457 LIBNAME SRCDATA BASE "/DEV_DBROKER/01_source_layer/06_extract_area"; NOTE: Libref SRCDATA was successfully assigned as follows: Engine: BASE Physical Name: /DEV_DBROKER/01_source_layer/06_extract_area 458 %rcSet(&syslibrc); 459 460 %let SYSLAST = %nrquote(work.W3HR6V3); 461 462 /* Runtime statistics macros */ 463 %etls_setPerfInit; 464 %perfstrt(txnname=%BQUOTE(_DISARM|&transformID|&syshostname|Loader), metrNam6=_DISROWCNT, metrDef6=Count32) ; MPRINT(PERFSTRT): options notes nosource nosource2 nosymbolgen nomprint nomlogic MPRINT(PERFSTRT): NOSYMBOLGEN ; MPRINT(PERFSTRT): ; MPRINT(PERFSTRT): ; 465 466 /* System options */ 467 options OBS=1000; 468 469 %let SYS_SQL_IP_SPEEDO = Y; 470 %let SYS_SQL_MAPPUTTO = sas_put; 471 %let SYS_SQLREDUCEPUT = DBMS; 472 %global etls_sql_pushDown; 473 %let etls_sql_pushDown = -1; 474 option DBIDIRECTEXEC; 475 476 %global etls_tableExist; 477 %global etls_numIndex; 478 %global etls_lastTable; 479 %let etls_tableExist = -1; 480 %let etls_numIndex = -1; 481 %let etls_lastTable = &SYSLAST; 482 483 /*---- Define load data macro ----*/ 484 485 /* -------------------------------------------------------------- 486 Load Technique Selection: Replace - Delete 487 Constraint and index action selections: 'OFF','ON','ASIS','ASIS' 488 Additional options selections... 489 Set unmapped to missing on updates: false 490 -------------------------------------------------------------- */ 491 %macro etls_loader; 492 493 %let etls_tableOptions = ; 17 The SAS System 10:24 Tuesday, February 7, 2017 494 495 /* Determine if the target table exists */ 496 %let etls_tableExist = %eval(%sysfunc(exist(SRCDATA.BIF_TEST_LOAD, DATA)) or 497 %sysfunc(exist(SRCDATA.BIF_TEST_LOAD, VIEW))); 498 499 /*---- Create a new table ----*/ 500 %if (&etls_tableExist eq 0) %then 501 %do; /* if table does not exist */ 502 503 %put %str(NOTE: Creating table ...); 504 505 data SRCDATA.BIF_TEST_LOAD 506 (COMPRESS = YES); 507 attrib SBR_PNR_TATOO_TYPE length = $3 508 format = $3. 509 informat = $3. 510 label = 'SBR_PNR_TATOO_TYPE'; 511 attrib SBR_PNR_TATOO_NUMBER length = 8 512 format = 11. 513 informat = 11. 514 label = 'SBR_PNR_TATOO_NUMBER'; 515 attrib SBR_PNR_ELEMENT length = $3 516 format = $3. 517 informat = $3. 518 label = 'SBR_PNR_ELEMENT'; 519 attrib SBR_PNR_LOCATOR length = $6 520 format = $6. 521 informat = $6. 522 label = 'SBR_PNR_LOCATOR'; 523 attrib SBR_PNR_CREATION_DATE_TIME length = 8 524 format = DATETIME22.3 525 informat = DATETIME22.3 526 label = 'SBR_PNR_CREATION_DATE_TIME'; 527 attrib SBR_CURRENT_ENVELOP length = 8 528 format = 11. 529 informat = 11. 530 label = 'SBR_CURRENT_ENVELOP'; 531 attrib SBR_ITIN_INFOSTATUS length = $3 532 format = $3. 533 informat = $3. 534 label = 'SBR_ITIN_INFOSTATUS'; 535 attrib SBR_PNR_LINE_NUMBER length = 8 536 format = 20. 537 informat = 20. 538 label = 'SBR_PNR_LINE_NUMBER'; 539 attrib SBR_DEP_DATETIME_LOCAL length = 8 540 format = DATETIME22.3 541 informat = DATETIME22.3 542 label = 'SBR_DEP_DATETIME_LOCAL'; 543 attrib SBR_ARR_DATETIME_LOCAL length = 8 544 format = DATETIME22.3 545 informat = DATETIME22.3 546 label = 'SBR_ARR_DATETIME_LOCAL'; 547 attrib SBR_DAY_CHANGE_IND length = 8 548 format = 6. 549 informat = 6. 550 label = 'SBR_DAY_CHANGE_IND'; 551 attrib SBR_BRDPT_CODE length = $5 18 The SAS System 10:24 Tuesday, February 7, 2017 552 format = $5. 553 informat = $5. 554 label = 'SBR_BRDPT_CODE'; 555 attrib SBR_BRDPT_NAME length = $17 556 format = $17. 557 informat = $17. 558 label = 'SBR_BRDPT_NAME'; 559 attrib SBR_OFFPT_CODE length = $5 560 format = $5. 561 informat = $5. 562 label = 'SBR_OFFPT_CODE'; 563 attrib SBR_OFFPT_NAME length = $17 564 format = $17. 565 informat = $17. 566 label = 'SBR_OFFPT_NAME'; 567 attrib SBR_SEG_AIRLINE_CODE length = $3 568 format = $3. 569 informat = $3. 570 label = 'SBR_SEG_AIRLINE_CODE'; 571 attrib SBR_SECOND_SEG_AIRLINE_CODE length = $4 572 format = $4. 573 informat = $4. 574 label = 'SBR_SECOND_SEG_AIRLINE_CODE'; 575 attrib SBR_TOUR_SEG_SOURCE_CODE length = $4 576 format = $4. 577 informat = $4. 578 label = 'SBR_TOUR_SEG_SOURCE_CODE'; 579 attrib SBR_SEG_PRODUCT_TYPE length = $6 580 format = $6. 581 informat = $6. 582 label = 'SBR_SEG_PRODUCT_TYPE'; 583 attrib SBR_BOOKING_CLASS length = $2 584 format = $2. 585 informat = $2. 586 label = 'SBR_BOOKING_CLASS'; 587 attrib SBR_FLT_OPR_SUFFIX length = $1 588 format = $1. 589 informat = $1. 590 label = 'SBR_FLT_OPR_SUFFIX'; 591 attrib SBR_NIGHT_INDICATOR length = $1 592 format = $1. 593 informat = $1. 594 label = 'SBR_NIGHT_INDICATOR'; 595 attrib SBR_ETKT_INDICATOR length = $2 596 format = $2. 597 informat = $2. 598 label = 'SBR_ETKT_INDICATOR'; 599 attrib SBR_FLIGHT_TYPE length = $2 600 format = $2. 601 informat = $2. 602 label = 'SBR_FLIGHT_TYPE'; 603 attrib CUST_CODESHARE_TYPE length = $1 604 format = $1. 605 informat = $1. 606 label = 'CUST_CODESHARE_TYPE'; 607 attrib SBR_SEGMENT_INFO_IND length = $3 608 format = $3. 609 informat = $3. 19 The SAS System 10:24 Tuesday, February 7, 2017 610 label = 'SBR_SEGMENT_INFO_IND'; 611 attrib SBR_BKG_CREATION_TIMEZONE length = $3 612 format = $3. 613 informat = $3. 614 label = 'SBR_BKG_CREATION_TIMEZONE'; 615 attrib SBR_BKG_CREATION_DATETIME length = 8 616 format = DATETIME22.3 617 informat = DATETIME22.3 618 label = 'SBR_BKG_CREATION_DATETIME'; 619 attrib SBR_SEG_NUMBER_IN_PARTY_PREV length = 8 620 format = 6. 621 informat = 6. 622 label = 'SBR_SEG_NUMBER_IN_PARTY_PREV'; 623 attrib SBR_SEG_STATUSCODE1_PREV length = $6 624 format = $6. 625 informat = $6. 626 label = 'SBR_SEG_STATUSCODE1_PREV'; 627 attrib SBR_SEG_STATUSCODE2_PREV length = $2 628 format = $2. 629 informat = $2. 630 label = 'SBR_SEG_STATUSCODE2_PREV'; 631 attrib SBR_NEGO_SPACE_BLOCKID length = $6 632 format = $6. 633 informat = $6. 634 label = 'SBR_NEGO_SPACE_BLOCKID'; 635 attrib SBR_NEGO_SPACE_DBIDTYPE length = $1 636 format = $1. 637 informat = $1. 638 label = 'SBR_NEGO_SPACE_DBIDTYPE'; 639 attrib SBR_SEG_CREATOR_AGENTSINE length = $9 640 format = $9. 641 informat = $9. 642 label = 'SBR_SEG_CREATOR_AGENTSINE'; 643 attrib SBR_SEG_CREATOR_OFFICEID length = $9 644 format = $9. 645 informat = $9. 646 label = 'SBR_SEG_CREATOR_OFFICEID'; 647 attrib SBR_SEG_CREATOR_RESP_OFFICE length = $9 648 format = $9. 649 informat = $9. 650 label = 'SBR_SEG_CREATOR_RESP_OFFICE'; 651 attrib SBR_SEG_CABINCODE length = $1 652 format = $1. 653 informat = $1. 654 label = 'SBR_SEG_CABINCODE'; 655 attrib SBR_HTL_TARIFF_AMOUNT length = 8 656 format = 20. 657 informat = 20. 658 label = 'SBR_HTL_TARIFF_AMOUNT'; 659 attrib SBR_HTL_TARIFF_CATEGORY length = $2 660 format = $2. 661 informat = $2. 662 label = 'SBR_HTL_TARIFF_CATEGORY'; 663 attrib SBR_HTL_TARIFF_CURRENCY length = $3 664 format = $3. 665 informat = $3. 666 label = 'SBR_HTL_TARIFF_CURRENCY'; 667 attrib SBR_HTL_TARRIF_TYPE_HDF length = $3 20 The SAS System 10:24 Tuesday, February 7, 2017 668 format = $3. 669 informat = $3. 670 label = 'SBR_HTL_TARRIF_TYPE_HDF'; 671 attrib SBR_HTL_TARRIF_TYPE_HIF length = $3 672 format = $3. 673 informat = $3. 674 label = 'SBR_HTL_TARRIF_TYPE_HIF'; 675 attrib SBR_HTL_TARRIF_TYPE_HLF length = $3 676 format = $3. 677 informat = $3. 678 label = 'SBR_HTL_TARRIF_TYPE_HLF'; 679 attrib SBR_HTL_TARRIF_TYPE_HNF length = $3 680 format = $3. 681 informat = $3. 682 label = 'SBR_HTL_TARRIF_TYPE_HNF'; 683 attrib SBR_HTL_TARRIF_TYPE_HSF length = $3 684 format = $3. 685 informat = $3. 686 label = 'SBR_HTL_TARRIF_TYPE_HSF'; 687 attrib SBR_SEG_BUSINESS_FUNCTION length = $3 688 format = $3. 689 informat = $3. 690 label = 'SBR_SEG_BUSINESS_FUNCTION'; 691 attrib SBR_EXT_AIRLINE_COMPANYID length = $3 692 format = $3. 693 informat = $3. 694 label = 'SBR_EXT_AIRLINE_COMPANYID'; 695 attrib SBR_EXT_AIRLINE_RLOC length = $19 696 format = $19. 697 informat = $19. 698 label = 'SBR_EXT_AIRLINE_RLOC'; 699 attrib SBR_EXT_AIRLINE_RLOCTYPE length = $1 700 format = $1. 701 informat = $1. 702 label = 'SBR_EXT_AIRLINE_RLOCTYPE'; 703 attrib SBR_EXT_PARTNER_COMPANYID length = $3 704 format = $3. 705 informat = $3. 706 label = 'SBR_EXT_PARTNER_COMPANYID'; 707 attrib SBR_EXT_PARTNER_RLOC length = $19 708 format = $19. 709 informat = $19. 710 label = 'SBR_EXT_PARTNER_RLOC'; 711 attrib SBR_EXT_PARTNER_RLOCTYPE length = $1 712 format = $1. 713 informat = $1. 714 label = 'SBR_EXT_PARTNER_RLOCTYPE'; 715 attrib SBR_SEG_NUMBER_IN_PARTY length = 8 716 format = 6. 717 informat = 6. 718 label = 'SBR_SEG_NUMBER_IN_PARTY'; 719 attrib SBR_SEG_STATUS_CODE length = $2 720 format = $2. 721 informat = $2. 722 label = 'SBR_SEG_STATUS_CODE'; 723 attrib SBR_SEG_FLOWN_IND length = $2 724 format = $2. 725 informat = $2. 21 The SAS System 10:24 Tuesday, February 7, 2017 726 label = 'SBR_SEG_FLOWN_IND'; 727 attrib SBR_EQUIPMENT_CODE length = $4 728 format = $4. 729 informat = $4. 730 label = 'SBR_EQUIPMENT_CODE'; 731 attrib SBR_NUMBER_OF_STOPS length = 8 732 format = 6. 733 informat = 6. 734 label = 'SBR_NUMBER_OF_STOPS'; 735 attrib SBR_DAYS_OF_OPERATION length = 8 736 format = 6. 737 informat = 6. 738 label = 'SBR_DAYS_OF_OPERATION'; 739 attrib SBR_DEP_TERMINAL_NO length = $2 740 format = $2. 741 informat = $2. 742 label = 'SBR_DEP_TERMINAL_NO'; 743 attrib SBR_ARR_TERMINAL_NO length = $2 744 format = $2. 745 informat = $2. 746 label = 'SBR_ARR_TERMINAL_NO'; 747 attrib SBR_SCHED_CHECKIN_TIME length = $4 748 format = $4. 749 informat = $4. 750 label = 'SBR_SCHED_CHECKIN_TIME'; 751 attrib SBR_ENTRN_FACILITY_TYPE length = $3 752 format = $3. 753 informat = $3. 754 label = 'SBR_ENTRN_FACILITY_TYPE'; 755 attrib SBR_ENTRN_FACILITY_DESC length = $15 756 format = $15. 757 informat = $15. 758 label = 'SBR_ENTRN_FACILITY_DESC'; 759 attrib SBR_ENTRN_FACILITY_PDT_QUAL length = $2 760 format = $2. 761 informat = $2. 762 label = 'SBR_ENTRN_FACILITY_PDT_QUAL'; 763 attrib SBR_ENTRN_FACILITY_PDT_CODE length = $4 764 format = $4. 765 informat = $4. 766 label = 'SBR_ENTRN_FACILITY_PDT_CODE'; 767 attrib SBR_MEAL_FACILITY_TYPE length = $3 768 format = $3. 769 informat = $3. 770 label = 'SBR_MEAL_FACILITY_TYPE'; 771 attrib SBR_MEAL_FACILITY_DESC length = $15 772 format = $15. 773 informat = $15. 774 label = 'SBR_MEAL_FACILITY_DESC'; 775 attrib SBR_MEAL_FACILITY_PDT_QUAL length = $2 776 format = $2. 777 informat = $2. 778 label = 'SBR_MEAL_FACILITY_PDT_QUAL'; 779 attrib SBR_MEAL_FACILITY_PDT_CODE length = $4 780 format = $4. 781 informat = $4. 782 label = 'SBR_MEAL_FACILITY_PDT_CODE'; 783 attrib SBR_CONFIRMATION_NUMBER_TYPE length = $3 22 The SAS System 10:24 Tuesday, February 7, 2017 784 format = $3. 785 informat = $3. 786 label = 'SBR_CONFIRMATION_NUMBER_TYPE'; 787 attrib SBR_CONFIRMATION_NUMBER_VALUE length = $20 788 format = $20. 789 informat = $20. 790 label = 'SBR_CONFIRMATION_NUMBER_VALUE'; 791 attrib SBR_SEG_FREETEXT1_QUAL length = $3 792 format = $3. 793 informat = $3. 794 label = 'SBR_SEG_FREETEXT1_QUAL'; 795 attrib SBR_SEG_FREETEXT1_TYPE length = $4 796 format = $4. 797 informat = $4. 798 label = 'SBR_SEG_FREETEXT1_TYPE'; 799 attrib SBR_SEG_FREETEXT1 length = $999 800 format = $999. 801 informat = $999. 802 label = 'SBR_SEG_FREETEXT1'; 803 attrib SBR_SEG_FREETEXT2_QUAL length = $3 804 format = $3. 805 informat = $3. 806 label = 'SBR_SEG_FREETEXT2_QUAL'; 807 attrib SBR_SEG_FREETEXT2_TYPE length = $4 808 format = $4. 809 informat = $4. 810 label = 'SBR_SEG_FREETEXT2_TYPE'; 811 attrib SBR_SEG_FREETEXT2 length = $999 812 format = $999. 813 informat = $999. 814 label = 'SBR_SEG_FREETEXT2'; 815 attrib SBR_SEG_FREETEXT3_QUAL length = $3 816 format = $3. 817 informat = $3. 818 label = 'SBR_SEG_FREETEXT3_QUAL'; 819 attrib SBR_SEG_FREETEXT3_TYPE length = $4 820 format = $4. 821 informat = $4. 822 label = 'SBR_SEG_FREETEXT3_TYPE'; 823 attrib SBR_SEG_FREETEXT3 length = $999 824 format = $999. 825 informat = $999. 826 label = 'SBR_SEG_FREETEXT3'; 827 attrib SBR_INV_CABINCODE length = $1 828 format = $1. 829 informat = $1. 830 label = 'SBR_INV_CABINCODE'; 831 attrib SBR_INV_SUBCLASS_NUM length = 8 832 format = 6. 833 informat = 6. 834 label = 'SBR_INV_SUBCLASS_NUM'; 835 attrib SBR_INV_POS_CLASSIFICATION length = $1 836 format = $1. 837 informat = $1. 838 label = 'SBR_INV_POS_CLASSIFICATION'; 839 attrib SBR_INV_POS_COMPANYID length = $2 840 format = $2. 841 informat = $2. 23 The SAS System 10:24 Tuesday, February 7, 2017 842 label = 'SBR_INV_POS_COMPANYID'; 843 attrib SBR_INV_POS_COUNTRY length = $2 844 format = $2. 845 informat = $2. 846 label = 'SBR_INV_POS_COUNTRY'; 847 attrib SBR_ADJUSTED_YIELD_AMOUNT length = 8 848 label = 'SBR_ADJUSTED_YIELD_AMOUNT'; 849 attrib SBR_BIDPRICE_AMOUNT length = 8 850 label = 'SBR_BIDPRICE_AMOUNT'; 851 attrib SBR_EFFECTIVE_YIELD_AMOUNT length = 8 852 label = 'SBR_EFFECTIVE_YIELD_AMOUNT'; 853 attrib SBR_OND_YIELD_AMOUNT length = 8 854 label = 'SBR_OND_YIELD_AMOUNT'; 855 attrib SBR_REVENUE_LOSS_AMOUNT length = 8 856 label = 'SBR_REVENUE_LOSS_AMOUNT'; 857 attrib SBR_ORIGIN_YIELD length = $3 858 format = $3. 859 informat = $3. 860 label = 'SBR_ORIGIN_YIELD'; 861 attrib SBR_DESTINATION_YIELD length = $3 862 format = $3. 863 informat = $3. 864 label = 'SBR_DESTINATION_YIELD'; 865 attrib SBR_ORIGIN_TRIP length = $3 866 format = $3. 867 informat = $3. 868 label = 'SBR_ORIGIN_TRIP'; 869 attrib SBR_DESTINATION_TRIP length = $3 870 format = $3. 871 informat = $3. 872 label = 'SBR_DESTINATION_TRIP'; 873 attrib SBR_COMM_COUNTRY length = $2 874 format = $2. 875 informat = $2. 876 label = 'SBR_COMM_COUNTRY'; 877 attrib SBR_OVEBOOKING_TYPE length = $3 878 format = $3. 879 informat = $3. 880 label = 'SBR_OVEBOOKING_TYPE'; 881 attrib SBR_OVERBOOKING_REASON length = $3 882 format = $3. 883 informat = $3. 884 label = 'SBR_OVERBOOKING_REASON'; 885 attrib SBR_RFIC_CODE length = $3 886 format = $3. 887 informat = $3. 888 label = 'SBR_RFIC_CODE'; 889 attrib SBR_RFISC_CODE length = $3 890 format = $3. 891 informat = $3. 892 label = 'SBR_RFISC_CODE'; 893 attrib SBR_SEG_CRE_POS_OFFICEID length = $9 894 format = $9. 895 informat = $9. 896 label = 'SBR_SEG_CRE_POS_OFFICEID'; 897 attrib SBR_SEG_CRE_POS_IATA_CODE length = $9 898 format = $9. 899 informat = $9. 24 The SAS System 10:24 Tuesday, February 7, 2017 900 label = 'SBR_SEG_CRE_POS_IATA_CODE'; 901 attrib SBR_SEG_CRE_POS_QUALIFIER length = $9 902 format = $9. 903 informat = $9. 904 label = 'SBR_SEG_CRE_POS_QUALIFIER'; 905 attrib SBR_SEG_CRE_POS_COMPANYID length = $3 906 format = $3. 907 informat = $3. 908 label = 'SBR_SEG_CRE_POS_COMPANYID'; 909 attrib SBR_SEG_CRE_POS_CITY_CODE length = $3 910 format = $3. 911 informat = $3. 912 label = 'SBR_SEG_CRE_POS_CITY_CODE'; 913 attrib SBR_SEG_CRE_POS_COUNTRY length = $3 914 format = $3. 915 informat = $3. 916 label = 'SBR_SEG_CRE_POS_COUNTRY'; 917 attrib SBR_SEG_CRE_POS_CURRENCY length = $3 918 format = $3. 919 informat = $3. 920 label = 'SBR_SEG_CRE_POS_CURRENCY'; 921 attrib SBR_SEG_CRE_POS_LANGUAGE length = $3 922 format = $3. 923 informat = $3. 924 label = 'SBR_SEG_CRE_POS_LANGUAGE'; 925 attrib DWH_SNAPSHOTTIMESTAMP length = 8 926 format = DATETIME22.3 927 informat = DATETIME22.3 928 label = 'DWH_SNAPSHOTTIMESTAMP'; 929 attrib SBR_EXT_AIRLINE_DATE length = $6 930 format = $6. 931 informat = $6. 932 label = 'SBR_EXT_AIRLINE_DATE'; 933 attrib SBR_EXT_AIRLINE_TIME length = $4 934 format = $4. 935 informat = $4. 936 label = 'SBR_EXT_AIRLINE_TIME'; 937 attrib SBR_EXT_AIRLINE_PARTNER_DATE length = $6 938 format = $6. 939 informat = $6. 940 label = 'SBR_EXT_AIRLINE_PARTNER_DATE'; 941 attrib SBR_EXT_AIRLINE_PARTNER_TIME length = $4 942 format = $4. 943 informat = $4. 944 label = 'SBR_EXT_AIRLINE_PARTNER_TIME'; 945 attrib SBR_SEG_FREETEXT1_STATUS length = $3 946 format = $3. 947 informat = $3. 948 label = 'SBR_SEG_FREETEXT1_STATUS'; 949 attrib SBR_SEG_FREETEXT1_COMPID length = $3 950 format = $3. 951 informat = $3. 952 label = 'SBR_SEG_FREETEXT1_COMPID'; 953 attrib SBR_SEG_FREETEXT2_STATUS length = $3 954 format = $3. 955 informat = $3. 956 label = 'SBR_SEG_FREETEXT2_STATUS'; 957 attrib SBR_SEG_FREETEXT2_COMPID length = $3 25 The SAS System 10:24 Tuesday, February 7, 2017 958 format = $3. 959 informat = $3. 960 label = 'SBR_SEG_FREETEXT2_COMPID'; 961 attrib SBR_SEG_FREETEXT3_STATUS length = $3 962 format = $3. 963 informat = $3. 964 label = 'SBR_SEG_FREETEXT3_STATUS'; 965 attrib SBR_SEG_FREETEXT3_COMPID length = $3 966 format = $3. 967 informat = $3. 968 label = 'SBR_SEG_FREETEXT3_COMPID'; 969 attrib SBR_ADJUSTED_YIELD_QUAL length = $3 970 format = $3. 971 informat = $3. 972 label = 'SBR_ADJUSTED_YIELD_QUAL'; 973 attrib SBR_BIDPRICE_QUAL length = $3 974 format = $3. 975 informat = $3. 976 label = 'SBR_BIDPRICE_QUAL'; 977 attrib SBR_EFFECTIVE_YIELD_QUAL length = $3 978 format = $3. 979 informat = $3. 980 label = 'SBR_EFFECTIVE_YIELD_QUAL'; 981 attrib SBR_OND_YIELD_QUAL length = $3 982 format = $3. 983 informat = $3. 984 label = 'SBR_OND_YIELD_QUAL'; 985 attrib SBR_REVENUE_LOSS_QUAL length = $3 986 format = $3. 987 informat = $3. 988 label = 'SBR_REVENUE_LOSS_QUAL'; 989 call missing(of _all_); 990 stop; 991 run; 992 993 %rcSet(&syserr); 994 995 %end; /* if table does not exist */ 996 997 %else 998 %do; /* table exists */ 999 1000 %macro etls_CIContents(table=,workTableOut=,inDSOptions=); 1001 %put NOTE: Building table listing Constraints and Indexes for: &table; 1002 proc datasets lib=work nolist; delete &workTableOut; quit; 1003 proc contents data=&table&inDSOptions out2=&workTableOut noprint; run; 1004 1005 data &workTableOut; 1006 length name $60 type $20 icown idxUnique idxNoMiss $3 recreate $600; 1007 set &workTableOut; 1008 type=upcase(type); 1009 if type eq 'REFERENTIAL' then 1010 do; 1011 delete; 1012 end; 1013 if type='INDEX' and ICOwn eq 'YES' then delete; 1014 run; 1015 %rcSet(&syserr); 26 The SAS System 10:24 Tuesday, February 7, 2017 1016 1017 %mend etls_CIContents; 1018 %etls_CIContents(table=SRCDATA.BIF_TEST_LOAD, workTableOut=etls_commands, inDSOptions=); 1019 1020 data etls_commands; 1021 retain dropC 1 dropI 0; /* user choices */ 1022 set etls_commands; 1023 if upcase(type)='INDEX' then command='index delete '||trim(name)||';'; 1024 else command='ic delete '||trim(name)||';'; 1025 if dropC and upcase(type) ne 'INDEX' then output; 1026 else if dropI and upcase(type) eq 'INDEX' then output; 1027 run; 1028 1029 data _null_; 1030 set etls_commands end=eof; 1031 if _n_=1 then 1032 call execute('proc datasets nolist lib=SRCDATA;modify BIF_TEST_LOAD;'); 1033 call execute(command); 1034 if eof then call execute('; quit;'); 1035 run; 1036 %rcSet(&syserr); 1037 1038 proc datasets lib = work nolist nowarn memtype = (data view); 1039 delete etls_commands; 1040 quit; 1041 1042 %end; /* table exists */ 1043 1044 %if (&etls_tableExist eq 1) %then 1045 %do; /* table exists */ 1046 /*---- Deleting all rows ----*/ 1047 %put %str(NOTE: Deleting all rows ...); 1048 proc sql; 1049 reset noprint; 1050 1051 delete * from SRCDATA.BIF_TEST_LOAD 1052 ; 1053 quit; 1054 1055 %rcSet(&sqlrc); 1056 1057 %end; /* table exists */ 1058 1059 /*---- Append ----*/ 1060 %put %str(NOTE: Appending data ...); 1061 1062 proc append base = SRCDATA.BIF_TEST_LOAD(COMPRESS = YES) 1063 data = &etls_lastTable (&etls_tableOptions) force ; 1064 run; 1065 1066 %rcSet(&syserr); 1067 1068 /*---- proceed with any post-load constraints and indexing ----*/ 1069 %if (%sysfunc(getoption(OBS))=0 and &syserr ne 0) %then 1070 %put %str(NOTE: Constraint and index processing skipped because of errors. ); 1071 %else 1072 %do; 1073 /*---- Create the integrity constraints for a table ----*/ 27 The SAS System 10:24 Tuesday, February 7, 2017 1074 %put %str(NOTE: Creating integrity constraints ...); 1075 proc datasets library=SRCDATA nolist; 1076 modify BIF_TEST_LOAD; 1077 ic create not null (SBR_PNR_TATOO_TYPE); 1078 ic create not null (SBR_PNR_TATOO_NUMBER); 1079 ic create not null (SBR_PNR_ELEMENT); 1080 ic create not null (SBR_PNR_LOCATOR); 1081 ic create not null (SBR_PNR_CREATION_DATE_TIME); 1082 ic create not null (SBR_CURRENT_ENVELOP); 1083 quit; 1084 1085 %rcSet(&syserr); 1086 1087 %end; /* proceed with any post-load constraints and indexing */ 1088 1089 %mend etls_loader; 1090 %etls_loader; NOTE: Creating table ... MPRINT(ETLS_LOADER): data SRCDATA.BIF_TEST_LOAD (COMPRESS = YES); MPRINT(ETLS_LOADER): attrib SBR_PNR_TATOO_TYPE length = $3 format = $3. informat = $3. label = 'SBR_PNR_TATOO_TYPE'; MPRINT(ETLS_LOADER): attrib SBR_PNR_TATOO_NUMBER length = 8 format = 11. informat = 11. label = 'SBR_PNR_TATOO_NUMBER'; MPRINT(ETLS_LOADER): attrib SBR_PNR_ELEMENT length = $3 format = $3. informat = $3. label = 'SBR_PNR_ELEMENT'; MPRINT(ETLS_LOADER): attrib SBR_PNR_LOCATOR length = $6 format = $6. informat = $6. label = 'SBR_PNR_LOCATOR'; MPRINT(ETLS_LOADER): attrib SBR_PNR_CREATION_DATE_TIME length = 8 format = DATETIME22.3 informat = DATETIME22.3 label = 'SBR_PNR_CREATION_DATE_TIME'; MPRINT(ETLS_LOADER): attrib SBR_CURRENT_ENVELOP length = 8 format = 11. informat = 11. label = 'SBR_CURRENT_ENVELOP'; MPRINT(ETLS_LOADER): attrib SBR_ITIN_INFOSTATUS length = $3 format = $3. informat = $3. label = 'SBR_ITIN_INFOSTATUS'; MPRINT(ETLS_LOADER): attrib SBR_PNR_LINE_NUMBER length = 8 format = 20. informat = 20. label = 'SBR_PNR_LINE_NUMBER'; MPRINT(ETLS_LOADER): attrib SBR_DEP_DATETIME_LOCAL length = 8 format = DATETIME22.3 informat = DATETIME22.3 label = 'SBR_DEP_DATETIME_LOCAL'; MPRINT(ETLS_LOADER): attrib SBR_ARR_DATETIME_LOCAL length = 8 format = DATETIME22.3 informat = DATETIME22.3 label = 'SBR_ARR_DATETIME_LOCAL'; MPRINT(ETLS_LOADER): attrib SBR_DAY_CHANGE_IND length = 8 format = 6. informat = 6. label = 'SBR_DAY_CHANGE_IND'; MPRINT(ETLS_LOADER): attrib SBR_BRDPT_CODE length = $5 format = $5. informat = $5. label = 'SBR_BRDPT_CODE'; MPRINT(ETLS_LOADER): attrib SBR_BRDPT_NAME length = $17 format = $17. informat = $17. label = 'SBR_BRDPT_NAME'; MPRINT(ETLS_LOADER): attrib SBR_OFFPT_CODE length = $5 format = $5. informat = $5. label = 'SBR_OFFPT_CODE'; MPRINT(ETLS_LOADER): attrib SBR_OFFPT_NAME length = $17 format = $17. informat = $17. label = 'SBR_OFFPT_NAME'; MPRINT(ETLS_LOADER): attrib SBR_SEG_AIRLINE_CODE length = $3 format = $3. informat = $3. label = 'SBR_SEG_AIRLINE_CODE'; MPRINT(ETLS_LOADER): attrib SBR_SECOND_SEG_AIRLINE_CODE length = $4 format = $4. informat = $4. label = 'SBR_SECOND_SEG_AIRLINE_CODE'; MPRINT(ETLS_LOADER): attrib SBR_TOUR_SEG_SOURCE_CODE length = $4 format = $4. informat = $4. label = 'SBR_TOUR_SEG_SOURCE_CODE'; MPRINT(ETLS_LOADER): attrib SBR_SEG_PRODUCT_TYPE length = $6 format = $6. informat = $6. label = 'SBR_SEG_PRODUCT_TYPE'; MPRINT(ETLS_LOADER): attrib SBR_BOOKING_CLASS length = $2 format = $2. informat = $2. label = 'SBR_BOOKING_CLASS'; MPRINT(ETLS_LOADER): attrib SBR_FLT_OPR_SUFFIX length = $1 format = $1. informat = $1. label = 'SBR_FLT_OPR_SUFFIX'; MPRINT(ETLS_LOADER): attrib SBR_NIGHT_INDICATOR length = $1 format = $1. informat = $1. label = 'SBR_NIGHT_INDICATOR'; MPRINT(ETLS_LOADER): attrib SBR_ETKT_INDICATOR length = $2 format = $2. informat = $2. label = 'SBR_ETKT_INDICATOR'; MPRINT(ETLS_LOADER): attrib SBR_FLIGHT_TYPE length = $2 format = $2. informat = $2. label = 'SBR_FLIGHT_TYPE'; MPRINT(ETLS_LOADER): attrib CUST_CODESHARE_TYPE length = $1 format = $1. informat = $1. label = 'CUST_CODESHARE_TYPE'; MPRINT(ETLS_LOADER): attrib SBR_SEGMENT_INFO_IND length = $3 format = $3. informat = $3. label = 'SBR_SEGMENT_INFO_IND'; MPRINT(ETLS_LOADER): attrib SBR_BKG_CREATION_TIMEZONE length = $3 format = $3. informat = $3. label = 'SBR_BKG_CREATION_TIMEZONE'; MPRINT(ETLS_LOADER): attrib SBR_BKG_CREATION_DATETIME length = 8 format = DATETIME22.3 informat = DATETIME22.3 label = 'SBR_BKG_CREATION_DATETIME'; MPRINT(ETLS_LOADER): attrib SBR_SEG_NUMBER_IN_PARTY_PREV length = 8 format = 6. informat = 6. label = 'SBR_SEG_NUMBER_IN_PARTY_PREV'; MPRINT(ETLS_LOADER): attrib SBR_SEG_STATUSCODE1_PREV length = $6 format = $6. informat = $6. label = 'SBR_SEG_STATUSCODE1_PREV'; MPRINT(ETLS_LOADER): attrib SBR_SEG_STATUSCODE2_PREV length = $2 format = $2. informat = $2. label = 'SBR_SEG_STATUSCODE2_PREV'; MPRINT(ETLS_LOADER): attrib SBR_NEGO_SPACE_BLOCKID length = $6 format = $6. informat = $6. label = 'SBR_NEGO_SPACE_BLOCKID'; MPRINT(ETLS_LOADER): attrib SBR_NEGO_SPACE_DBIDTYPE length = $1 format = $1. informat = $1. label = 'SBR_NEGO_SPACE_DBIDTYPE'; 28 The SAS System 10:24 Tuesday, February 7, 2017 MPRINT(ETLS_LOADER): attrib SBR_SEG_CREATOR_AGENTSINE length = $9 format = $9. informat = $9. label = 'SBR_SEG_CREATOR_AGENTSINE'; MPRINT(ETLS_LOADER): attrib SBR_SEG_CREATOR_OFFICEID length = $9 format = $9. informat = $9. label = 'SBR_SEG_CREATOR_OFFICEID'; MPRINT(ETLS_LOADER): attrib SBR_SEG_CREATOR_RESP_OFFICE length = $9 format = $9. informat = $9. label = 'SBR_SEG_CREATOR_RESP_OFFICE'; MPRINT(ETLS_LOADER): attrib SBR_SEG_CABINCODE length = $1 format = $1. informat = $1. label = 'SBR_SEG_CABINCODE'; MPRINT(ETLS_LOADER): attrib SBR_HTL_TARIFF_AMOUNT length = 8 format = 20. informat = 20. label = 'SBR_HTL_TARIFF_AMOUNT'; MPRINT(ETLS_LOADER): attrib SBR_HTL_TARIFF_CATEGORY length = $2 format = $2. informat = $2. label = 'SBR_HTL_TARIFF_CATEGORY'; MPRINT(ETLS_LOADER): attrib SBR_HTL_TARIFF_CURRENCY length = $3 format = $3. informat = $3. label = 'SBR_HTL_TARIFF_CURRENCY'; MPRINT(ETLS_LOADER): attrib SBR_HTL_TARRIF_TYPE_HDF length = $3 format = $3. informat = $3. label = 'SBR_HTL_TARRIF_TYPE_HDF'; MPRINT(ETLS_LOADER): attrib SBR_HTL_TARRIF_TYPE_HIF length = $3 format = $3. informat = $3. label = 'SBR_HTL_TARRIF_TYPE_HIF'; MPRINT(ETLS_LOADER): attrib SBR_HTL_TARRIF_TYPE_HLF length = $3 format = $3. informat = $3. label = 'SBR_HTL_TARRIF_TYPE_HLF'; MPRINT(ETLS_LOADER): attrib SBR_HTL_TARRIF_TYPE_HNF length = $3 format = $3. informat = $3. label = 'SBR_HTL_TARRIF_TYPE_HNF'; MPRINT(ETLS_LOADER): attrib SBR_HTL_TARRIF_TYPE_HSF length = $3 format = $3. informat = $3. label = 'SBR_HTL_TARRIF_TYPE_HSF'; MPRINT(ETLS_LOADER): attrib SBR_SEG_BUSINESS_FUNCTION length = $3 format = $3. informat = $3. label = 'SBR_SEG_BUSINESS_FUNCTION'; MPRINT(ETLS_LOADER): attrib SBR_EXT_AIRLINE_COMPANYID length = $3 format = $3. informat = $3. label = 'SBR_EXT_AIRLINE_COMPANYID'; MPRINT(ETLS_LOADER): attrib SBR_EXT_AIRLINE_RLOC length = $19 format = $19. informat = $19. label = 'SBR_EXT_AIRLINE_RLOC'; MPRINT(ETLS_LOADER): attrib SBR_EXT_AIRLINE_RLOCTYPE length = $1 format = $1. informat = $1. label = 'SBR_EXT_AIRLINE_RLOCTYPE'; MPRINT(ETLS_LOADER): attrib SBR_EXT_PARTNER_COMPANYID length = $3 format = $3. informat = $3. label = 'SBR_EXT_PARTNER_COMPANYID'; MPRINT(ETLS_LOADER): attrib SBR_EXT_PARTNER_RLOC length = $19 format = $19. informat = $19. label = 'SBR_EXT_PARTNER_RLOC'; MPRINT(ETLS_LOADER): attrib SBR_EXT_PARTNER_RLOCTYPE length = $1 format = $1. informat = $1. label = 'SBR_EXT_PARTNER_RLOCTYPE'; MPRINT(ETLS_LOADER): attrib SBR_SEG_NUMBER_IN_PARTY length = 8 format = 6. informat = 6. label = 'SBR_SEG_NUMBER_IN_PARTY'; MPRINT(ETLS_LOADER): attrib SBR_SEG_STATUS_CODE length = $2 format = $2. informat = $2. label = 'SBR_SEG_STATUS_CODE'; MPRINT(ETLS_LOADER): attrib SBR_SEG_FLOWN_IND length = $2 format = $2. informat = $2. label = 'SBR_SEG_FLOWN_IND'; MPRINT(ETLS_LOADER): attrib SBR_EQUIPMENT_CODE length = $4 format = $4. informat = $4. label = 'SBR_EQUIPMENT_CODE'; MPRINT(ETLS_LOADER): attrib SBR_NUMBER_OF_STOPS length = 8 format = 6. informat = 6. label = 'SBR_NUMBER_OF_STOPS'; MPRINT(ETLS_LOADER): attrib SBR_DAYS_OF_OPERATION length = 8 format = 6. informat = 6. label = 'SBR_DAYS_OF_OPERATION'; MPRINT(ETLS_LOADER): attrib SBR_DEP_TERMINAL_NO length = $2 format = $2. informat = $2. label = 'SBR_DEP_TERMINAL_NO'; MPRINT(ETLS_LOADER): attrib SBR_ARR_TERMINAL_NO length = $2 format = $2. informat = $2. label = 'SBR_ARR_TERMINAL_NO'; MPRINT(ETLS_LOADER): attrib SBR_SCHED_CHECKIN_TIME length = $4 format = $4. informat = $4. label = 'SBR_SCHED_CHECKIN_TIME'; MPRINT(ETLS_LOADER): attrib SBR_ENTRN_FACILITY_TYPE length = $3 format = $3. informat = $3. label = 'SBR_ENTRN_FACILITY_TYPE'; MPRINT(ETLS_LOADER): attrib SBR_ENTRN_FACILITY_DESC length = $15 format = $15. informat = $15. label = 'SBR_ENTRN_FACILITY_DESC'; MPRINT(ETLS_LOADER): attrib SBR_ENTRN_FACILITY_PDT_QUAL length = $2 format = $2. informat = $2. label = 'SBR_ENTRN_FACILITY_PDT_QUAL'; MPRINT(ETLS_LOADER): attrib SBR_ENTRN_FACILITY_PDT_CODE length = $4 format = $4. informat = $4. label = 'SBR_ENTRN_FACILITY_PDT_CODE'; MPRINT(ETLS_LOADER): attrib SBR_MEAL_FACILITY_TYPE length = $3 format = $3. informat = $3. label = 'SBR_MEAL_FACILITY_TYPE'; MPRINT(ETLS_LOADER): attrib SBR_MEAL_FACILITY_DESC length = $15 format = $15. informat = $15. label = 'SBR_MEAL_FACILITY_DESC'; MPRINT(ETLS_LOADER): attrib SBR_MEAL_FACILITY_PDT_QUAL length = $2 format = $2. informat = $2. label = 'SBR_MEAL_FACILITY_PDT_QUAL'; MPRINT(ETLS_LOADER): attrib SBR_MEAL_FACILITY_PDT_CODE length = $4 format = $4. informat = $4. label = 'SBR_MEAL_FACILITY_PDT_CODE'; MPRINT(ETLS_LOADER): attrib SBR_CONFIRMATION_NUMBER_TYPE length = $3 format = $3. informat = $3. label = 'SBR_CONFIRMATION_NUMBER_TYPE'; MPRINT(ETLS_LOADER): attrib SBR_CONFIRMATION_NUMBER_VALUE length = $20 format = $20. informat = $20. label = 'SBR_CONFIRMATION_NUMBER_VALUE'; MPRINT(ETLS_LOADER): attrib SBR_SEG_FREETEXT1_QUAL length = $3 format = $3. informat = $3. label = 'SBR_SEG_FREETEXT1_QUAL'; MPRINT(ETLS_LOADER): attrib SBR_SEG_FREETEXT1_TYPE length = $4 format = $4. informat = $4. label = 'SBR_SEG_FREETEXT1_TYPE'; MPRINT(ETLS_LOADER): attrib SBR_SEG_FREETEXT1 length = $999 format = $999. informat = $999. label = 'SBR_SEG_FREETEXT1'; MPRINT(ETLS_LOADER): attrib SBR_SEG_FREETEXT2_QUAL length = $3 format = $3. informat = $3. label = 'SBR_SEG_FREETEXT2_QUAL'; MPRINT(ETLS_LOADER): attrib SBR_SEG_FREETEXT2_TYPE length = $4 format = $4. informat = $4. label = 'SBR_SEG_FREETEXT2_TYPE'; MPRINT(ETLS_LOADER): attrib SBR_SEG_FREETEXT2 length = $999 format = $999. informat = $999. label = 'SBR_SEG_FREETEXT2'; MPRINT(ETLS_LOADER): attrib SBR_SEG_FREETEXT3_QUAL length = $3 format = $3. informat = $3. label = 'SBR_SEG_FREETEXT3_QUAL'; MPRINT(ETLS_LOADER): attrib SBR_SEG_FREETEXT3_TYPE length = $4 format = $4. informat = $4. label = 'SBR_SEG_FREETEXT3_TYPE'; MPRINT(ETLS_LOADER): attrib SBR_SEG_FREETEXT3 length = $999 format = $999. informat = $999. label = 'SBR_SEG_FREETEXT3'; MPRINT(ETLS_LOADER): attrib SBR_INV_CABINCODE length = $1 format = $1. informat = $1. label = 'SBR_INV_CABINCODE'; MPRINT(ETLS_LOADER): attrib SBR_INV_SUBCLASS_NUM length = 8 format = 6. informat = 6. label = 'SBR_INV_SUBCLASS_NUM'; MPRINT(ETLS_LOADER): attrib SBR_INV_POS_CLASSIFICATION length = $1 format = $1. informat = $1. label = 'SBR_INV_POS_CLASSIFICATION'; 29 The SAS System 10:24 Tuesday, February 7, 2017 MPRINT(ETLS_LOADER): attrib SBR_INV_POS_COMPANYID length = $2 format = $2. informat = $2. label = 'SBR_INV_POS_COMPANYID'; MPRINT(ETLS_LOADER): attrib SBR_INV_POS_COUNTRY length = $2 format = $2. informat = $2. label = 'SBR_INV_POS_COUNTRY'; MPRINT(ETLS_LOADER): attrib SBR_ADJUSTED_YIELD_AMOUNT length = 8 label = 'SBR_ADJUSTED_YIELD_AMOUNT'; MPRINT(ETLS_LOADER): attrib SBR_BIDPRICE_AMOUNT length = 8 label = 'SBR_BIDPRICE_AMOUNT'; MPRINT(ETLS_LOADER): attrib SBR_EFFECTIVE_YIELD_AMOUNT length = 8 label = 'SBR_EFFECTIVE_YIELD_AMOUNT'; MPRINT(ETLS_LOADER): attrib SBR_OND_YIELD_AMOUNT length = 8 label = 'SBR_OND_YIELD_AMOUNT'; MPRINT(ETLS_LOADER): attrib SBR_REVENUE_LOSS_AMOUNT length = 8 label = 'SBR_REVENUE_LOSS_AMOUNT'; MPRINT(ETLS_LOADER): attrib SBR_ORIGIN_YIELD length = $3 format = $3. informat = $3. label = 'SBR_ORIGIN_YIELD'; MPRINT(ETLS_LOADER): attrib SBR_DESTINATION_YIELD length = $3 format = $3. informat = $3. label = 'SBR_DESTINATION_YIELD'; MPRINT(ETLS_LOADER): attrib SBR_ORIGIN_TRIP length = $3 format = $3. informat = $3. label = 'SBR_ORIGIN_TRIP'; MPRINT(ETLS_LOADER): attrib SBR_DESTINATION_TRIP length = $3 format = $3. informat = $3. label = 'SBR_DESTINATION_TRIP'; MPRINT(ETLS_LOADER): attrib SBR_COMM_COUNTRY length = $2 format = $2. informat = $2. label = 'SBR_COMM_COUNTRY'; MPRINT(ETLS_LOADER): attrib SBR_OVEBOOKING_TYPE length = $3 format = $3. informat = $3. label = 'SBR_OVEBOOKING_TYPE'; MPRINT(ETLS_LOADER): attrib SBR_OVERBOOKING_REASON length = $3 format = $3. informat = $3. label = 'SBR_OVERBOOKING_REASON'; MPRINT(ETLS_LOADER): attrib SBR_RFIC_CODE length = $3 format = $3. informat = $3. label = 'SBR_RFIC_CODE'; MPRINT(ETLS_LOADER): attrib SBR_RFISC_CODE length = $3 format = $3. informat = $3. label = 'SBR_RFISC_CODE'; MPRINT(ETLS_LOADER): attrib SBR_SEG_CRE_POS_OFFICEID length = $9 format = $9. informat = $9. label = 'SBR_SEG_CRE_POS_OFFICEID'; MPRINT(ETLS_LOADER): attrib SBR_SEG_CRE_POS_IATA_CODE length = $9 format = $9. informat = $9. label = 'SBR_SEG_CRE_POS_IATA_CODE'; MPRINT(ETLS_LOADER): attrib SBR_SEG_CRE_POS_QUALIFIER length = $9 format = $9. informat = $9. label = 'SBR_SEG_CRE_POS_QUALIFIER'; MPRINT(ETLS_LOADER): attrib SBR_SEG_CRE_POS_COMPANYID length = $3 format = $3. informat = $3. label = 'SBR_SEG_CRE_POS_COMPANYID'; MPRINT(ETLS_LOADER): attrib SBR_SEG_CRE_POS_CITY_CODE length = $3 format = $3. informat = $3. label = 'SBR_SEG_CRE_POS_CITY_CODE'; MPRINT(ETLS_LOADER): attrib SBR_SEG_CRE_POS_COUNTRY length = $3 format = $3. informat = $3. label = 'SBR_SEG_CRE_POS_COUNTRY'; MPRINT(ETLS_LOADER): attrib SBR_SEG_CRE_POS_CURRENCY length = $3 format = $3. informat = $3. label = 'SBR_SEG_CRE_POS_CURRENCY'; MPRINT(ETLS_LOADER): attrib SBR_SEG_CRE_POS_LANGUAGE length = $3 format = $3. informat = $3. label = 'SBR_SEG_CRE_POS_LANGUAGE'; MPRINT(ETLS_LOADER): attrib DWH_SNAPSHOTTIMESTAMP length = 8 format = DATETIME22.3 informat = DATETIME22.3 label = 'DWH_SNAPSHOTTIMESTAMP'; MPRINT(ETLS_LOADER): attrib SBR_EXT_AIRLINE_DATE length = $6 format = $6. informat = $6. label = 'SBR_EXT_AIRLINE_DATE'; MPRINT(ETLS_LOADER): attrib SBR_EXT_AIRLINE_TIME length = $4 format = $4. informat = $4. label = 'SBR_EXT_AIRLINE_TIME'; MPRINT(ETLS_LOADER): attrib SBR_EXT_AIRLINE_PARTNER_DATE length = $6 format = $6. informat = $6. label = 'SBR_EXT_AIRLINE_PARTNER_DATE'; MPRINT(ETLS_LOADER): attrib SBR_EXT_AIRLINE_PARTNER_TIME length = $4 format = $4. informat = $4. label = 'SBR_EXT_AIRLINE_PARTNER_TIME'; MPRINT(ETLS_LOADER): attrib SBR_SEG_FREETEXT1_STATUS length = $3 format = $3. informat = $3. label = 'SBR_SEG_FREETEXT1_STATUS'; MPRINT(ETLS_LOADER): attrib SBR_SEG_FREETEXT1_COMPID length = $3 format = $3. informat = $3. label = 'SBR_SEG_FREETEXT1_COMPID'; MPRINT(ETLS_LOADER): attrib SBR_SEG_FREETEXT2_STATUS length = $3 format = $3. informat = $3. label = 'SBR_SEG_FREETEXT2_STATUS'; MPRINT(ETLS_LOADER): attrib SBR_SEG_FREETEXT2_COMPID length = $3 format = $3. informat = $3. label = 'SBR_SEG_FREETEXT2_COMPID'; MPRINT(ETLS_LOADER): attrib SBR_SEG_FREETEXT3_STATUS length = $3 format = $3. informat = $3. label = 'SBR_SEG_FREETEXT3_STATUS'; MPRINT(ETLS_LOADER): attrib SBR_SEG_FREETEXT3_COMPID length = $3 format = $3. informat = $3. label = 'SBR_SEG_FREETEXT3_COMPID'; MPRINT(ETLS_LOADER): attrib SBR_ADJUSTED_YIELD_QUAL length = $3 format = $3. informat = $3. label = 'SBR_ADJUSTED_YIELD_QUAL'; MPRINT(ETLS_LOADER): attrib SBR_BIDPRICE_QUAL length = $3 format = $3. informat = $3. label = 'SBR_BIDPRICE_QUAL'; MPRINT(ETLS_LOADER): attrib SBR_EFFECTIVE_YIELD_QUAL length = $3 format = $3. informat = $3. label = 'SBR_EFFECTIVE_YIELD_QUAL'; MPRINT(ETLS_LOADER): attrib SBR_OND_YIELD_QUAL length = $3 format = $3. informat = $3. label = 'SBR_OND_YIELD_QUAL'; MPRINT(ETLS_LOADER): attrib SBR_REVENUE_LOSS_QUAL length = $3 format = $3. informat = $3. label = 'SBR_REVENUE_LOSS_QUAL'; MPRINT(ETLS_LOADER): call missing(of _all_); MPRINT(ETLS_LOADER): stop; MPRINT(ETLS_LOADER): run; NOTE: The data set SRCDATA.BIF_TEST_LOAD has 0 observations and 123 variables. NOTE: PROCEDURE| _DISARM| STOP| _DISARM| 2017-02-07T10:24:27,039+08:00| _DISARM| WorkspaceServer| _DISARM| SAS| _DISARM| | _DISARM| 9216000| _DISARM| 8949760| _DISARM| 11| _DISARM| 11| _DISARM| 0| _DISARM| 0| _DISARM| 0.020000| _DISARM| 0.020985| _DISARM| 1802053467.018107| _DISARM| 1802053467.039092| _DISARM| 0.010000| _DISARM| | _ENDDISARM NOTE: DATA statement used (Total process time): real time 0.02 seconds user cpu time 0.01 seconds system cpu time 0.01 seconds memory 3455.64k OS Memory 9000.00k Timestamp 02/07/2017 10:24:27 AM 30 The SAS System 10:24 Tuesday, February 7, 2017 Page Faults 2 Page Reclaims 181 Page Swaps 0 Voluntary Context Switches 6 Involuntary Context Switches 0 Block Input Operations 0 Block Output Operations 0 MPRINT(ETLS_LOADER): ; NOTE: Appending data ... MPRINT(ETLS_LOADER): proc append base = SRCDATA.BIF_TEST_LOAD(COMPRESS = YES) data = WORK.W3HR6V3 () force ; MPRINT(ETLS_LOADER): run; NOTE: Appending WORK.W3HR6V3 to SRCDATA.BIF_TEST_LOAD. NOTE: There were 1000 observations read from the data set WORK.W3HR6V3. NOTE: 1000 observations added. NOTE: The data set SRCDATA.BIF_TEST_LOAD has 1000 observations and 123 variables. NOTE: PROCEDURE APPEND used (Total process time): real time 36.39 seconds user cpu time 0.91 seconds system cpu time 1.92 seconds memory 4238.92k OS Memory 9764.00k Timestamp 02/07/2017 10:25:03 AM Page Faults 0 Page Reclaims 328818 Page Swaps 0 Voluntary Context Switches 982 Involuntary Context Switches 2290 Block Input Operations 0 Block Output Operations 0 NOTE: PROCEDURE| _DISARM| STOP| _DISARM| 2017-02-07T10:25:03,431+08:00| _DISARM| WorkspaceServer| _DISARM| SAS| _DISARM| | _DISARM| 9998336| _DISARM| 9998336| _DISARM| 11| _DISARM| 11| _DISARM| 0| _DISARM| 0| _DISARM| 2.830000| _DISARM| 36.390979| _DISARM| 1802053467.040231| _DISARM| 1802053503.431210| _DISARM| 0.910000| _DISARM| | _ENDDISARM MPRINT(ETLS_LOADER): ; NOTE: Creating integrity constraints ... MPRINT(ETLS_LOADER): proc datasets library=SRCDATA nolist; MPRINT(ETLS_LOADER): modify BIF_TEST_LOAD; MPRINT(ETLS_LOADER): ic create not null (SBR_PNR_TATOO_TYPE); NOTE: Integrity constraint _NM0001_ defined. MPRINT(ETLS_LOADER): ic create not null (SBR_PNR_TATOO_NUMBER); NOTE: Integrity constraint _NM0002_ defined. MPRINT(ETLS_LOADER): ic create not null (SBR_PNR_ELEMENT); NOTE: Integrity constraint _NM0003_ defined. MPRINT(ETLS_LOADER): ic create not null (SBR_PNR_LOCATOR); NOTE: Integrity constraint _NM0004_ defined. MPRINT(ETLS_LOADER): ic create not null (SBR_PNR_CREATION_DATE_TIME); NOTE: Integrity constraint _NM0005_ defined. MPRINT(ETLS_LOADER): ic create not null (SBR_CURRENT_ENVELOP); NOTE: Integrity constraint _NM0006_ defined. MPRINT(ETLS_LOADER): quit; NOTE: MODIFY was successful for SRCDATA.BIF_TEST_LOAD.DATA. NOTE: PROCEDURE| _DISARM| STOP| _DISARM| 2017-02-07T10:25:03,467+08:00| _DISARM| WorkspaceServer| _DISARM| SAS| _DISARM| | _DISARM| 9998336| _DISARM| 9998336| _DISARM| 11| _DISARM| 11| _DISARM| 0| _DISARM| 0| _DISARM| 0.010000| _DISARM| 0.034203| 31 The SAS System 10:24 Tuesday, February 7, 2017 _DISARM| 1802053503.433047| _DISARM| 1802053503.467250| _DISARM| 0.010000| _DISARM| | _ENDDISARM NOTE: PROCEDURE DATASETS used (Total process time): real time 0.03 seconds user cpu time 0.01 seconds system cpu time 0.00 seconds memory 4238.92k OS Memory 9764.00k Timestamp 02/07/2017 10:25:03 AM Page Faults 0 Page Reclaims 378 Page Swaps 0 Voluntary Context Switches 2 Involuntary Context Switches 2 Block Input Operations 0 Block Output Operations 0 MPRINT(ETLS_LOADER): ; 1091 1092 %let etls_recCheckExist = 0; 1093 %let etls_recnt = 0; 1094 %macro etls_recordCheck; 1095 %let etls_recCheckExist = %eval(%sysfunc(exist(SRCDATA.BIF_TEST_LOAD, DATA)) or 1096 %sysfunc(exist(SRCDATA.BIF_TEST_LOAD, VIEW))); 1097 1098 %if (&etls_recCheckExist) %then 1099 %do; 1100 %local etls_syntaxcheck; 1101 %let etls_syntaxcheck = %sysfunc(getoption(syntaxcheck)); 1102 /* Turn off syntaxcheck option to perform following steps */ 1103 options nosyntaxcheck; 1104 1105 proc contents data = SRCDATA.BIF_TEST_LOAD out = work.etls_contents(keep = nobs) noprint; 1106 run; 1107 1108 data _null_; 1109 set work.etls_contents (obs = 1); 1110 call symput("etls_recnt", left(put(nobs,32.))); 1111 run; 1112 1113 proc datasets lib = work nolist nowarn memtype = (data view); 1114 delete etls_contents; 1115 quit; 1116 1117 /* Reset syntaxcheck option to previous setting */ 1118 options &etls_syntaxcheck; 1119 %end; 1120 %mend etls_recordCheck; 1121 %etls_recordCheck; MPRINT(ETLS_RECORDCHECK): options nosyntaxcheck; MPRINT(ETLS_RECORDCHECK): proc contents data = SRCDATA.BIF_TEST_LOAD out = work.etls_contents(keep = nobs) noprint; MPRINT(ETLS_RECORDCHECK): run; NOTE: Compression was disabled for data set WORK.ETLS_CONTENTS because compression overhead would increase the size of the data set. NOTE: The data set WORK.ETLS_CONTENTS has 123 observations and 1 variables. NOTE: PROCEDURE CONTENTS used (Total process time): real time 0.00 seconds user cpu time 0.00 seconds 32 The SAS System 10:24 Tuesday, February 7, 2017 system cpu time 0.00 seconds memory 4238.92k OS Memory 10280.00k Timestamp 02/07/2017 10:25:03 AM Page Faults 0 Page Reclaims 85 Page Swaps 0 Voluntary Context Switches 8 Involuntary Context Switches 0 Block Input Operations 0 Block Output Operations 0 NOTE: PROCEDURE| _DISARM| STOP| _DISARM| 2017-02-07T10:25:03,483+08:00| _DISARM| WorkspaceServer| _DISARM| SAS| _DISARM| | _DISARM| 10526720| _DISARM| 9998336| _DISARM| 11| _DISARM| 11| _DISARM| 0| _DISARM| 0| _DISARM| 0.000000| _DISARM| 0.007823| _DISARM| 1802053503.475283| _DISARM| 1802053503.483106| _DISARM| 0.000000| _DISARM| | _ENDDISARM MPRINT(ETLS_RECORDCHECK): data _null_; MPRINT(ETLS_RECORDCHECK): set work.etls_contents (obs = 1); MPRINT(ETLS_RECORDCHECK): call symput("etls_recnt", left(put(nobs,32.))); MPRINT(ETLS_RECORDCHECK): run; NOTE: There were 1 observations read from the data set WORK.ETLS_CONTENTS. NOTE: PROCEDURE| _DISARM| STOP| _DISARM| 2017-02-07T10:25:03,486+08:00| _DISARM| WorkspaceServer| _DISARM| SAS| _DISARM| | _DISARM| 10526720| _DISARM| 9998336| _DISARM| 11| _DISARM| 11| _DISARM| 0| _DISARM| 0| _DISARM| 0.000000| _DISARM| 0.001278| _DISARM| 1802053503.485452| _DISARM| 1802053503.486730| _DISARM| 0.000000| _DISARM| | _ENDDISARM NOTE: DATA statement used (Total process time): real time 0.00 seconds user cpu time 0.00 seconds system cpu time 0.00 seconds memory 4238.92k OS Memory 10280.00k Timestamp 02/07/2017 10:25:03 AM Page Faults 0 Page Reclaims 55 Page Swaps 0 Voluntary Context Switches 0 Involuntary Context Switches 0 Block Input Operations 0 Block Output Operations 0 MPRINT(ETLS_RECORDCHECK): proc datasets lib = work nolist nowarn memtype = (data view); MPRINT(ETLS_RECORDCHECK): delete etls_contents; MPRINT(ETLS_RECORDCHECK): quit; NOTE: Deleting WORK.ETLS_CONTENTS (memtype=DATA). NOTE: PROCEDURE| _DISARM| STOP| _DISARM| 2017-02-07T10:25:03,493+08:00| _DISARM| WorkspaceServer| _DISARM| SAS| _DISARM| | _DISARM| 10526720| _DISARM| 9998336| _DISARM| 11| _DISARM| 11| _DISARM| 0| _DISARM| 0| _DISARM| 0.000000| _DISARM| 0.006021| _DISARM| 1802053503.487404| _DISARM| 1802053503.493425| _DISARM| 0.000000| _DISARM| | _ENDDISARM NOTE: PROCEDURE DATASETS used (Total process time): real time 0.00 seconds user cpu time 0.00 seconds system cpu time 0.00 seconds memory 4238.92k OS Memory 10280.00k Timestamp 02/07/2017 10:25:03 AM Page Faults 0 Page Reclaims 0 33 The SAS System 10:24 Tuesday, February 7, 2017 Page Swaps 0 Voluntary Context Switches 7 Involuntary Context Switches 0 Block Input Operations 0 Block Output Operations 0 MPRINT(ETLS_RECORDCHECK): options SYNTAXCHECK; 1122 1123 %perfstop(metrVal6=%sysfunc(max(&etls_recnt,-1))); MPRINT(PERFSTOP): options notes nosource nosource2 nosymbolgen nomprint nomlogic NOTE: _DISARM|A5V3LOE4.BD00048E|SZBKR202|Loader| _DISARM| STOP| _DISARM| 2017-02-07T10:25:03,496+08:00| _DISARM| SAS_Data_Integration_Studio| _DISARM| SAS| _DISARM| 1000| _DISARM| 10526720| _DISARM| 9998336| _DISARM| 11| _DISARM| 11| _DISARM| 0| _DISARM| 0| _DISARM| 2.870000| _DISARM| 36.500517| _DISARM| 1802053466.995732| _DISARM| 1802053503.496249| _DISARM| 0.940000| _DISARM| | _ENDDISARM MPRINT(PERFSTOP): NOSYMBOLGEN ; MPRINT(PERFSTOP): ; MPRINT(PERFSTOP): ; 1124 %let etls_recnt=-1; 1125 1126 1127 1128 /** Step end Table Loader **/ 1129 1130 34 The SAS System 10:24 Tuesday, February 7, 2017 1131 %let etls_endTime = %sysfunc(datetime(),datetime.); 1132 1133 /* Turn off performance statistics collection */ 1134 data _null_; 1135 if "&_perfinit" eq "1" then 1136 call execute('%perfend;'); 1137 1138 run; MPRINT(PERFEND): options notes nosource nosource2 nosymbolgen nomprint nomlogic ; MPRINT(PERFEND): ; MPRINT(PERFEND): ; MPRINT(PERFEND): options NOTES SOURCE NOSOURCE2 MPRINT NOMLOGIC NOSYMBOLGEN ; MPRINT(PERFEND): ; MPRINT(PERFEND): ; NOTE: PROCEDURE| _DISARM| STOP| _DISARM| 2017-02-07T10:25:03,551+08:00| _DISARM| WorkspaceServer| _DISARM| SAS| _DISARM| | _DISARM| 10526720| _DISARM| 9998336| _DISARM| 11| _DISARM| 11| _DISARM| 0| _DISARM| 0| _DISARM| 0.000000| _DISARM| 0.012981| _DISARM| 1802053503.538395| _DISARM| 1802053503.551376| _DISARM| 0.000000| _DISARM| | _ENDDISARM NOTE: DATA statement used (Total process time): real time 0.01 seconds user cpu time 0.00 seconds system cpu time 0.00 seconds memory 4238.92k OS Memory 10280.00k Timestamp 02/07/2017 10:25:03 AM Page Faults 1 Page Reclaims 145 Page Swaps 0 Voluntary Context Switches 0 Involuntary Context Switches 0 Block Input Operations 0 Block Output Operations 0 NOTE: CALL EXECUTE generated line. 1 + options notes nosource nosource2 nosymbolgen nomprint nomlogic ;;; options NOTES SOURCE NOSOURCE2 MPRINT NOMLOGIC NOSYMBOLGEN ;;;; 1139