Help using Base SAS procedures

Indexes are not always being created

Accepted Solution Solved
Reply
Contributor
Posts: 32
Accepted Solution

Indexes are not always being created

Hi community,


I have a set of 20 tables for which I need to create different types of indexes.


I use the following procedures to create them:

a) for composite indexes:

proc datasets library=&Lib. nolist;

     modify &tblname;

     index create indexname = (&indxvars) / UNIQUE;

quit;

b) for simple indexes:

proc datasets library=&Lib. nolist;

     modify &tblname;

     index create LVID / UNIQUE;

quit;

(if the table needs a simple index, the index variable is always called LVID.)

all procedures seem to be processed correctly:

NOTE: Composite index indexname has been defined.

NOTE: MODIFY was successful for WORK.VB1.DATA.

NOTE: PROCEDURE DATASETS used (Total process time):

      real time           0.00 seconds

      cpu time            0.00 seconds

but in fact three out of the 20 tables have not been indexed, which I see when I excecute:

proc sql;

   select * from dictionary.indexes;

quit;

anyone having an idea what is happening here?


Accepted Solutions
Solution
‎07-20-2015 07:16 AM
Respected Advisor
Posts: 4,173

Re: Indexes are not always being created

Posted in reply to andreas_schmitz

Yes, you are right because you are actually not modifying the tables but you are creating totally new tables with the same name than the original tables.

If you want to apply changes to an existing table then you need to use either a data step "update" or "modify" statement or SQL update/insert/delete

/* modify tables */

data VT1;

  modify VT1;

  if not lvid=2 then

    do;

      ds='hugo';

      replace;

    end;

run;

View solution in original post


All Replies
Respected Advisor
Posts: 4,173

Re: Indexes are not always being created

Posted in reply to andreas_schmitz

At first sight this looks like a bug as the log tells you everything is o.k. But then you don't give us all the code and all the log (eg. is your Proc Datasets within a macro and you're calling this macro multiple times  - or how does this work?).

The log message you've posted indicates that you're running this against tables in work. If so: Are you able to "mock-up" a sample code for us which would allow us to replicate what you observe?

Contributor
Posts: 32

Re: Indexes are not always being created

Hi Patrick,

thank you for your answer. I'll try to provide more information (it's hard to give you all the code and all the log since the whole sas program does a lot more stuff and the code snippets are at different positions of the program):

Basically it's coded like you already guessed: the proc datasets procedure is inside a macro which is call several times. The full macro looks like this:

%macro index_erstellung (lib,tblname,indxvars);

    %if %sysfunc(exist(&lib..&tblname.)) %then

          %do;

              %put Tabelle vorhanden: &lib..&tblname.;

              %if "&indxvars" ^= "LVID" %then

                    %do;

                        proc datasets library=&Lib. nolist;

                              modify &tblname;

                              index create indexname = (&indxvars) / UNIQUE;

                        quit;

                    %end;

              %else

                    %do;

                        proc datasets library=&Lib. nolist;

                              modify &tblname;

                              index create LVID / UNIQUE;

                        quit;

              %end;

          %end;

%mend index_erstellung;


I will try to describe the whole process:

I have a .csv file indicating the different tables and which index variables are needed for which of these tables. You find the content of the .csv-file at the end of this post. Then, I import this .csv file to SAS.

To generate the input for my macro I use this data step (I guess one could code it way more beautiful - any suggestions are welcome):

data WORK.INDEX_ERSTELLUNG;

    set WORK.INDEX;

    format indexspalten $250.;

    if LVID = 1 then

          indexspalten = 'LVID';

    if VTID = 1 then

          indexspalten = cat(strip(indexspalten),' ','VTID');

    if VARIANTEID = 1 then

          indexspalten = cat(strip(indexspalten),' ','VARIANTEID');

    if VBID = 1 then

          indexspalten = cat(strip(indexspalten),' ','VBID');

    if AGENTURID = 1 then

          indexspalten = cat(strip(indexspalten),' ','AGENTURID');

    if FONDSID = 1 then

          indexspalten = cat(strip(indexspalten),' ','FONDSID');

    if KTOBEWID = 1 then

          indexspalten = cat(strip(indexspalten),' ','KTOBEWID');

    if KTOSTDTYPID = 1 then

          indexspalten = cat(strip(indexspalten),' ','KTOSTDTYPID');

    if PARTNERID = 1 then

          indexspalten = cat(strip(indexspalten),' ','PARTNERID');

    if ROLLENID = 1 then

          indexspalten = cat(strip(indexspalten),' ','ROLLENID');

    if AKTYP = 1 then

          indexspalten = cat(strip(indexspalten),' ','AKTYP');

    if KZWERTSICHER = 1 then

          indexspalten = cat(strip(indexspalten),' ','KZWERTSICHER');

    if JAHR = 1 then

          indexspalten = cat(strip(indexspalten),' ','JAHR');

    if MONAT = 1 then

          indexspalten = cat(strip(indexspalten),' ','MONAT');

    if VSYS = 1 then

          indexspalten = cat(strip(indexspalten),' ','VSYS');

run;

Now, I have all the input for my macro, which is called by this data step:

data _null_;

    set work.INDEX_ERSTELLUNG;

    call symput('VTAB',name);

    call symput('VINDEXSPALTEN',indexspalten);

    call execute('%index_erstellung(WORK,&vtab.,&vindexspalten.)');

run;

Unfortunately I cannot attach any file, so I'll copy the relevant log parts:

---------------------------------------------------------------------------------- START OF LOG ------------------------------------------------------------------------------------------

NOTE: The infile "/appdata/sas/fb_daten/vrm/lp/lf/E3_index.csv" is:

      Filename=/appdata/sas/fb_daten/vrm/lp/lf/E3_index.csv,

      Owner Name=X914119,Group Name=UX_SAS_VRM_LP,

      Access Permission=rwxrw-r--,

      Last Modified=Fri Jul 17 08:42:39 2015,

      File Size (bytes)=905

NOTE: 20 records were read from the infile "/appdata/sas/fb_daten/vrm/lp/lf/E3_index.csv".

      The minimum record length was 32.

      The maximum record length was 44.

NOTE: The data set WORK.INDEX has 20 observations and 16 variables.

NOTE: Compressing data set WORK.INDEX increased size by 100.00 percent.

      Compressed is 2 pages; un-compressed would require 1 pages.

NOTE: DATA statement used (Total process time):

      real time           0.00 seconds

      cpu time            0.01 seconds

 

NOTE: There were 20 observations read from the data set WORK.INDEX.

NOTE: The data set WORK.INDEX_ERSTELLUNG has 20 observations and 17 variables.

NOTE: Compressing data set WORK.INDEX_ERSTELLUNG increased size by 100.00 percent.

      Compressed is 2 pages; un-compressed would require 1 pages.

NOTE: DATA statement used (Total process time):

      real time           0.00 seconds

      cpu time            0.00 seconds

Tabelle vorhanden: WORK.ABSCHLKO1

Tabelle vorhanden: WORK.ANLOPT1

Tabelle vorhanden: WORK.BEARBNW

Tabelle vorhanden: WORK.C_RVWERTE1

Tabelle vorhanden: WORK.HVGRUPPE

Tabelle vorhanden: WORK.JURLV

Tabelle vorhanden: WORK.JURVT

Tabelle vorhanden: WORK.KTOBWG1

Tabelle vorhanden: WORK.KTOSTD1

Tabelle vorhanden: WORK.LV

Tabelle vorhanden: WORK.PRV1

Tabelle vorhanden: WORK.RISSCHAETZ

Tabelle vorhanden: WORK.SKBILWERT1

Tabelle vorhanden: WORK.SKFONDSANTEIL1

Tabelle vorhanden: WORK.SKSGA

Tabelle vorhanden: WORK.SKUEBVERW1

Tabelle vorhanden: WORK.VB1

Tabelle vorhanden: WORK.VERTSCHL1

Tabelle vorhanden: WORK.VPVT1

Tabelle vorhanden: WORK.VT1

NOTE: There were 20 observations read from the data set WORK.INDEX_ERSTELLUNG.

NOTE: DATA statement used (Total process time):

      real time           0.00 seconds

      cpu time            0.00 seconds

 

NOTE: CALL EXECUTE generated line.

1         + proc datasets library=WORK nolist;       modify ABSCHLKO1;

1         +                                                                  index create indexname = (LVID VTID VBID AKTYP JAHR

MONAT) / UNIQUE;

NOTE: Composite index indexname has been defined.

1         +

      quit;

NOTE: MODIFY was successful for WORK.ABSCHLKO1.DATA.

NOTE: PROCEDURE DATASETS used (Total process time):

      real time           0.01 seconds

      cpu time            0.01 seconds

 

2         + proc datasets library=WORK nolist;       modify ANLOPT1;

2         +                                                                index create indexname = (LVID VTID VARIANTEID VBID) /

UNIQUE;

NOTE: Composite index indexname has been defined.

2         +

      quit;

NOTE: MODIFY was successful for WORK.ANLOPT1.DATA.

NOTE: PROCEDURE DATASETS used (Total process time):

      real time           0.00 seconds

      cpu time            0.00 seconds

 

3         + proc datasets library=WORK nolist;       modify BEARBNW;

3         +                                                                index create LVID / UNIQUE;

NOTE: Simple index lvId has been defined.

3         +                                                                                                 quit;

NOTE: MODIFY was successful for WORK.BEARBNW.DATA.

NOTE: PROCEDURE DATASETS used (Total process time):

      real time           0.00 seconds

      cpu time            0.01 seconds

55                                                         The SAS System                                16:20 Friday, July 17, 2015

4         + proc datasets library=WORK nolist;       modify C_RVWERTE1;

4         +                                                                   index create indexname = (LVID VTID VARIANTEID) /

UNIQUE;

NOTE: Composite index indexname has been defined.

4         +

      quit;

NOTE: MODIFY was successful for WORK.C_RVWERTE1.DATA.

NOTE: PROCEDURE DATASETS used (Total process time):

      real time           0.00 seconds

      cpu time            0.00 seconds

5         + proc datasets library=WORK nolist;       modify HVGRUPPE;

5         +                                                                 index create LVID / UNIQUE;

NOTE: Simple index lvId has been defined.

5         +                                                                                                  quit;

NOTE: MODIFY was successful for WORK.HVGRUPPE.DATA.

NOTE: PROCEDURE DATASETS used (Total process time):

      real time           0.00 seconds

      cpu time            0.01 seconds

6         + proc datasets library=WORK nolist;       modify JURLV;

6         +                                                              index create LVID / UNIQUE;

NOTE: Simple index lvId has been defined.

6         +                                                                                               quit;

NOTE: MODIFY was successful for WORK.JURLV.DATA.

NOTE: PROCEDURE DATASETS used (Total process time):

      real time           0.00 seconds

      cpu time            0.00 seconds

7         + proc datasets library=WORK nolist;       modify JURVT;

7         +                                                              index create indexname = (LVID VTID) / UNIQUE;

NOTE: Composite index indexname has been defined.

7         +                                                                                                                  quit;

NOTE: MODIFY was successful for WORK.JURVT.DATA.

NOTE: PROCEDURE DATASETS used (Total process time):

      real time           0.00 seconds

      cpu time            0.00 seconds

8         + proc datasets library=WORK nolist;       modify KTOBWG1;

8         +                                                                index create indexname = (LVID VTID VBID KTOBEWID) /

UNIQUE;

NOTE: Composite index indexname has been defined.

8         +

      quit;

NOTE: MODIFY was successful for WORK.KTOBWG1.DATA.

NOTE: PROCEDURE DATASETS used (Total process time):

      real time           0.00 seconds

56                                                         The SAS System                                16:20 Friday, July 17, 2015

      cpu time            0.00 seconds

9         + proc datasets library=WORK nolist;       modify KTOSTD1;

9         +                                                                index create indexname = (LVID VTID VBID FONDSID

KTOSTDTYPID) / UNIQUE;

NOTE: Composite index indexname has been defined.

9         +

      quit;

NOTE: MODIFY was successful for WORK.KTOSTD1.DATA.

NOTE: PROCEDURE DATASETS used (Total process time):

      real time           0.02 seconds

      cpu time            0.03 seconds

 

10        + proc datasets library=WORK nolist;       modify LV;

10        +                                                           index create LVID / UNIQUE;

NOTE: Simple index lvId has been defined.

10        +                                                                                            quit;

NOTE: MODIFY was successful for WORK.LV.DATA.

NOTE: PROCEDURE DATASETS used (Total process time):

      real time           0.00 seconds

      cpu time            0.00 seconds

 
11        + proc datasets library=WORK nolist;       modify PRV1;

11        +                                                             index create indexname = (LVID AGENTURID ROLLENID) / UNIQUE;

NOTE: Composite index indexname has been defined.

11        +                                                                                                                    

      quit;


NOTE: MODIFY was successful for WORK.PRV1.DATA.

NOTE: PROCEDURE DATASETS used (Total process time):

      real time           0.00 seconds

      cpu time            0.00 seconds

 

12        + proc datasets library=WORK nolist;       modify RISSCHAETZ;

12        +                                                                   index create indexname = (LVID VTID PARTNERID

ROLLENID) / UNIQUE;

NOTE: Composite index indexname has been defined.

12        +

      quit;

NOTE: MODIFY was successful for WORK.RISSCHAETZ.DATA.

NOTE: PROCEDURE DATASETS used (Total process time):

      real time           0.00 seconds

      cpu time            0.00 seconds

 

13        + proc datasets library=WORK nolist;       modify SKBILWERT1;

13        +                                                                   index create indexname = (LVID VTID VARIANTEID) /

UNIQUE;

NOTE: Composite index indexname has been defined.

13        +

      quit;

57                                                         The SAS System                                16:20 Friday, July 17, 2015


NOTE: MODIFY was successful for WORK.SKBILWERT1.DATA.

NOTE: PROCEDURE DATASETS used (Total process time):

      real time           0.00 seconds

      cpu time            0.00 seconds

 

14        + proc datasets library=WORK nolist;       modify SKFONDSANTEIL1;

14        +                                                                       index create indexname = (LVID VARIANTEID

FONDSID) / UNIQUE;

NOTE: Composite index indexname has been defined.

14        +

      quit;

NOTE: MODIFY was successful for WORK.SKFONDSANTEIL1.DATA.

NOTE: PROCEDURE DATASETS used (Total process time):

      real time           0.00 seconds

      cpu time            0.01 seconds

 

15        + proc datasets library=WORK nolist;       modify SKSGA;

15        +                                                              index create indexname = (LVID VTID VBID) / UNIQUE;

NOTE: Composite index indexname has been defined.

15        +                                                                                                                  

quit;

NOTE: MODIFY was successful for WORK.SKSGA.DATA.

NOTE: PROCEDURE DATASETS used (Total process time):

      real time           0.00 seconds

      cpu time            0.00 seconds

 

16        + proc datasets library=WORK nolist;       modify SKUEBVERW1;

16        +                                                                   index create indexname = (LVID VTID VARIANTEID VSYS)

/ UNIQUE;

NOTE: Composite index indexname has been defined.

16        +

      quit;


NOTE: MODIFY was successful for WORK.SKUEBVERW1.DATA.

NOTE: PROCEDURE DATASETS used (Total process time):

      real time           0.00 seconds

      cpu time            0.00 seconds

 

17        + proc datasets library=WORK nolist;       modify VB1;

17        +                                                            index create indexname = (LVID VTID VBID) / UNIQUE;

NOTE: Composite index indexname has been defined.

17        +                                                                                                                

quit;


NOTE: MODIFY was successful for WORK.VB1.DATA.

NOTE: PROCEDURE DATASETS used (Total process time):

      real time           0.00 seconds

      cpu time            0.01 seconds

 

18        + proc datasets library=WORK nolist;       modify VERTSCHL1;

58                                                         The SAS System                                16:20 Friday, July 17, 2015


18        +                                                                  index create indexname = (LVID VTID FONDSID

KZWERTSICHER) / UNIQUE;

NOTE: Composite index indexname has been defined.

18        +

      quit;


NOTE: MODIFY was successful for WORK.VERTSCHL1.DATA.

NOTE: PROCEDURE DATASETS used (Total process time):

      real time           0.00 seconds

      cpu time            0.00 seconds

 

19        + proc datasets library=WORK nolist;       modify VPVT1;

19        +                                                              index create indexname = (LVID VTID PARTNERID ROLLENID) /

UNIQUE;

NOTE: Composite index indexname has been defined.

19        +

      quit;


NOTE: MODIFY was successful for WORK.VPVT1.DATA.

NOTE: PROCEDURE DATASETS used (Total process time):

      real time           0.00 seconds

      cpu time            0.00 seconds

 

20        + proc datasets library=WORK nolist;       modify VT1;

20        +                                                            index create indexname = (LVID VTID) / UNIQUE;

NOTE: Composite index indexname has been defined.

20        +                                                                                                                quit;


NOTE: MODIFY was successful for WORK.VT1.DATA.

NOTE: PROCEDURE DATASETS used (Total process time):

      real time           0.00 seconds

      cpu time            0.01 seconds

---------------------------------------------------------------------------------- END OF LOG ------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------- START OF CSV ------------------------------------------------------------------------------------------

NAME,LVID,VTID,VARIANTEID,VBID,AGENTURID,FONDSID,KTOBEWID,KTOSTDTYPID,PARTNERID,ROLLENID,AKTYP,KZWERTSICHER,JAHR,MONAT,VSYS

ABSCHLKO1,1,1,0,1,0,0,0,0,0,0,1,0,1,1,0

ANLOPT1,1,1,1,1,0,0,0,0,0,0,0,0,0,0,0

BEARBNW,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0

C_RVWERTE1,1,1,1,0,0,0,0,0,0,0,0,0,0,0,0

HVGRUPPE,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0

JURLV,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0

JURVT,1,1,0,0,0,0,0,0,0,0,0,0,0,0,0

KTOBWG1,1,1,0,1,0,0,1,0,0,0,0,0,0,0,0

KTOSTD1,1,1,0,1,0,1,0,1,0,0,0,0,0,0,0

LV,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0

PRV1,1,0,0,0,1,0,0,0,0,1,0,0,0,0,0

RISSCHAETZ,1,1,0,0,0,0,0,0,1,1,0,0,0,0,0

SKBILWERT1,1,1,1,0,0,0,0,0,0,0,0,0,0,0,0

SKFONDSANTEIL1,1,0,1,0,0,1,0,0,0,0,0,0,0,0,0

SKSGA,1,1,0,1,0,0,0,0,0,0,0,0,0,0,0

SKUEBVERW1,1,1,1,0,0,0,0,0,0,0,0,0,0,0,1

VB1,1,1,0,1,0,0,0,0,0,0,0,0,0,0,0

VERTSCHL1,1,1,0,0,0,1,0,0,0,0,0,1,0,0,0

VPVT1,1,1,0,0,0,0,0,0,1,1,0,0,0,0,0

VT1,1,1,0,0,0,0,0,0,0,0,0,0,0,0,0

---------------------------------------------------------------------------------- END OF CSV ------------------------------------------------------------------------------------------

Respected Advisor
Posts: 4,173

Re: Indexes are not always being created

Posted in reply to andreas_schmitz

Staying very close to the code as provided I can't replicate what you describe. If I execute the code the indexes get created as stated in the log. That makes it a bit hard to investigate what possibly could go wrong in your environment.

Can you please run below code in your environment and check if you get the desired result (indexex on all 20 tables)?

data work.index;

  infile datalines dsd truncover;

  input

    name:$20.

    (LVID VTID VARIANTEID VBID AGENTURID FONDSID KTOBEWID KTOSTDTYPID PARTNERID ROLLENID AKTYP KZWERTSICHER JAHR MONAT VSYS) (:8.);

  datalines;

ABSCHLKO1,1,1,0,1,0,0,0,0,0,0,1,0,1,1,0

ANLOPT1,1,1,1,1,0,0,0,0,0,0,0,0,0,0,0

BEARBNW,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0

C_RVWERTE1,1,1,1,0,0,0,0,0,0,0,0,0,0,0,0

HVGRUPPE,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0

JURLV,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0

JURVT,1,1,0,0,0,0,0,0,0,0,0,0,0,0,0

KTOBWG1,1,1,0,1,0,0,1,0,0,0,0,0,0,0,0

KTOSTD1,1,1,0,1,0,1,0,1,0,0,0,0,0,0,0

LV,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0

PRV1,1,0,0,0,1,0,0,0,0,1,0,0,0,0,0

RISSCHAETZ,1,1,0,0,0,0,0,0,1,1,0,0,0,0,0

SKBILWERT1,1,1,1,0,0,0,0,0,0,0,0,0,0,0,0

SKFONDSANTEIL1,1,0,1,0,0,1,0,0,0,0,0,0,0,0,0

SKSGA,1,1,0,1,0,0,0,0,0,0,0,0,0,0,0

SKUEBVERW1,1,1,1,0,0,0,0,0,0,0,0,0,0,0,1

VB1,1,1,0,1,0,0,0,0,0,0,0,0,0,0,0

VERTSCHL1,1,1,0,0,0,1,0,0,0,0,0,1,0,0,0

VPVT1,1,1,0,0,0,0,0,0,1,1,0,0,0,0,0

VT1,1,1,0,0,0,0,0,0,0,0,0,0,0,0,0

;

run;

data WORK.INDEX_ERSTELLUNG;

  format indexspalten $250.;

  set WORK.INDEX;

  array idx_spalten {*} LVID VTID VARIANTEID VBID AGENTURID FONDSID KTOBEWID KTOSTDTYPID PARTNERID ROLLENID AKTYP KZWERTSICHER JAHR MONAT VSYS;

  do _i=1 to dim(idx_spalten);

    if idx_spalten[_i]=1 then

      indexspalten=catx(' ',indexspalten,upcase(vname(idx_spalten[_i])));

  end;

run;

/** create sample data **/

%macro create_sample_data(lib,tblname,indxvars);

  data &lib..&tblname;

    length ds $65;

    ds="&lib..&tblname";

    %let Nwords=%sysfunc(countw(&indxvars));

    %do i=1 %to &Nwords;

      do %scan(&indxvars,&i)=1 to 5;

    %end;

      output;

    %do i=1 %to &Nwords;

      end;

    %end;

  run;

%mend;

data _null_;

  set work.INDEX_ERSTELLUNG;

  call execute(cats('%create_sample_data(WORK,',name,',',indexspalten,')'));

run;

/** create index for sample data **/

%macro index_erstellung (lib,tblname,indxvars);

  %if %sysfunc(exist(&lib..&tblname.)) %then

    %do;

      %put Tabelle vorhanden: &lib..&tblname.;

      %if "&indxvars" ^= "LVID" %then

        %do;

          proc datasets library=&Lib. nolist;

            modify &tblname;

            index create indexname = (&indxvars) / UNIQUE;

          quit;

        %end;

      %else

        %do;

          proc datasets library=&Lib. nolist;

            modify &tblname;

            index create LVID / UNIQUE;

          quit;

        %end;

    %end;

%mend;

data _null_;

  set work.INDEX_ERSTELLUNG;

  call execute(cats('%index_erstellung(WORK,',name,',',indexspalten,')'));

run;

/** check if index created **/

%macro index_list (lib,tblname,indxvars);

  %if %sysfunc(exist(&lib..&tblname.)) %then

    %do;

      title "Indexes on table &lib..&tblname";

      proc sql;

         select * from dictionary.indexes where libname=%upcase("&lib") and memname=%upcase("&tblname");

      quit

      title;

    %end;

%mend;

data _null_;

  set work.INDEX_ERSTELLUNG;

  call execute(cats('%index_list(WORK,',name,',',indexspalten,')'));

run;

On a side note: To implement your macro "%index_erstellung()" a bit more generic I'd probably would go for code as below.

%macro index_erstellung (lib,tblname,indxvars);

  %if %sysfunc(exist(&lib..&tblname.)) %then

    %do;

      %put Tabelle vorhanden: &lib..&tblname.;

      %if %sysfunc(countw(&indxvars))=1 %then

        %do;

          proc datasets library=&Lib. nolist;

            modify &tblname;

            index create &indxvars / UNIQUE;

          quit;

        %end;

      %else

        %do;

          proc datasets library=&Lib. nolist;

            modify &tblname;

            index create indexname = (&indxvars) / UNIQUE;

          quit;

        %end;

    %end;

  %else

    %do;

      %put Tabelle NICHT vorhanden: &lib..&tblname.;

    %end;

%mend;

Contributor
Posts: 32

Re: Indexes are not always being created

If I use your code with sample data, I get the indexes on all the 20 tables.

If I take my data and use your code, I get the index on all 20 tables again (which I see in the log).


But I think I am the stupid... I modify three of the tables later in the code:

data VT1;

     set VT1;

     if not %VarExist(VT1, c_wzabl) then

          c_wzabl = ' ';

     if not %VarExist(VT1, optabl) then

          optabl = ' ';

     if not %VarExist(VT1, c_urbzabl) then

          c_urbzabl = ' ';

run;


data VB1;

     set VB1;

     if not %VarExist(VB1, bszb) then

          bszb = .;

     if not %VarExist(VB1, c_amkoP1) then

          c_amkoP1 = .;

     if not %VarExist(VB1, c_amkoP2) then

          c_amkoP2 = .;

     if not %VarExist(VB1, c_betaP1) then

          c_betaP1 = .;

     if not %VarExist(VB1, c_betaP2) then

          c_betaP2 = .;

     if not %VarExist(VB1, c_durAmkoP1) then

          c_durAmkoP1 = .;

     if not %VarExist(VB1, c_durAmkoP2) then

          c_durAmkoP2 = .;

     if not %VarExist(VB1, c_durBetaP1) then

          c_durBetaP1 = .;

     if not %VarExist(VB1, la3) then

          la3 = .;

     if not %VarExist(VB1, minBtg) then

          minBtg = .;

     if not %VarExist(VB1, minBtgSum) then

          minBtgSum = .;

     if not %VarExist(VB1, c_zbSumStorno) then

          c_zbSumStorno = .;

     if not %VarExist(VB1, kuebr) then

          kuebr = .;

     if not %VarExist(VB1, C_KZZWB) then

          C_KZZWB = .;

run;


data abschlko1;

     set abschlko1;

     if not %VarExist(abschlko1, akvol) then

     akvol = .;

run;

even if the content of the table does not change, the indexes seem to be 'deleted' from the tables. Am I right?

Solution
‎07-20-2015 07:16 AM
Respected Advisor
Posts: 4,173

Re: Indexes are not always being created

Posted in reply to andreas_schmitz

Yes, you are right because you are actually not modifying the tables but you are creating totally new tables with the same name than the original tables.

If you want to apply changes to an existing table then you need to use either a data step "update" or "modify" statement or SQL update/insert/delete

/* modify tables */

data VT1;

  modify VT1;

  if not lvid=2 then

    do;

      ds='hugo';

      replace;

    end;

run;

Contributor
Posts: 32

Re: Indexes are not always being created

Ok, thanks a lot for your help and sorry for wasting your time.

Respected Advisor
Posts: 4,173

Re: Indexes are not always being created

Posted in reply to andreas_schmitz

You're welcome and no reason to apologize.

Super User
Posts: 11,343

Re: Indexes are not always being created

Posted in reply to andreas_schmitz

From the online documentation:

UNIQUE

specifies that the combination of values of the index variables must be
unique. If you specify UNIQUE and multiple observations have the same values for
the index variables, the index is not created.

Verify that the combination of values of the variable(s) used to index the tables with missing index are unique.

Respected Advisor
Posts: 4,173

Re: Indexes are not always being created

True, but this should throw an error as in below example:

data sample;

  do i=1 to 10;

    do j= 1 to 10;

      output;

      output;

    end;

  end;

run;

proc datasets library=work nolist;

  modify sample;

  index create i / UNIQUE;

quit;

proc datasets library=work nolist;

     modify sample;

     index create indexname = (i j) / UNIQUE;

quit;

Log:

ERROR: Duplicate values not allowed on index i for file SAMPLE.

....

ERROR: Duplicate values not allowed on index indexname for file SAMPLE.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 388 views
  • 0 likes
  • 3 in conversation