Solved: Indexes are not always being created

andreas_schmitz · Posted 07-17-2015 08:26 AM

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?

Patrick · Posted 07-20-2015 07:16 AM

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

Patrick · Posted 07-17-2015 08:48 AM

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?

andreas_schmitz · Posted 07-20-2015 05:04 AM

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;

%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 ------------------------------------------------------------------------------------------

Patrick · Posted 07-20-2015 06:13 AM

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;

%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;

%else

%do;

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

%end;

%mend;

andreas_schmitz · Posted 07-20-2015 07:07 AM

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?

Patrick · Posted 07-20-2015 07:16 AM

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;

andreas_schmitz · Posted 07-20-2015 07:18 AM

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

Patrick · Posted 07-20-2015 07:26 AM

You're welcome and no reason to apologize.

ballardw · Posted 07-17-2015 10:16 AM

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.

Patrick · Posted 07-17-2015 10:35 AM

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

data sample;

do i=1 to 10;

do j= 1 to 10;

output;

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.

Indexes are not always being created

Re: Indexes are not always being created

Re: Indexes are not always being created

Re: Indexes are not always being created

Re: Indexes are not always being created

Re: Indexes are not always being created

Re: Indexes are not always being created

Re: Indexes are not always being created

Re: Indexes are not always being created

Re: Indexes are not always being created

Re: Indexes are not always being created

Register Today!

SAS Training: Just a Click Away