Re-Create Index in Enterprise Guide

[jimbarbour]

Is there a way to re-create an index in Enterprise Guide?  The old Display Manager would do it for you if the index file were missing, but Enterprise Guide just says "Cannot open; the dataset is damaged" if the index file is missing.

I'm not sure if I'm being clear, so let me give an example.  Say I have two files in a directory:

claims.sas7bdat

claims.sas7bndx

Now, let's say that for some reason claims.sas7bndx gets deleted by accident.  I still have all my data but I can't open the SAS dataset in Enterprise Guide.  Enterprise Guide says that the file is damaged.

However, I can open my SAS dataset in Display Manager just fine.  Display Manager automatically regenerates the index file if it is missing.

Is there a way I can regenerate an index under these circumstances using Enterprise Guide (or I suppose batch would be fine too).

Thank you,

Jim

[ghosh · Accepted solution]

The code here will rebuild the index for you

https://support.sas.com/kb/41/276.html

[jimbarbour]

@ghosh,

Thank you very much.  That's just the code I needed.  I was able to write a context sensitive macro to set the DLDMGOPTION.

Jim

[ChrisNZ]

Have you tried using proc dataset's repair statement?

[ChrisNZ]

See:

31         proc datasets lib=W noprint; repair T; quit;

NOTE: Repairing W.T (memtype=DATA).
NOTE: File W.T.INDEX does not exist.
NOTE: Indexes recreated:

[jimbarbour]

@ChrisNZ,

Thank you for that.  Your response is very direct and to the point -- and most effective.  However, @ghosh's answer came first, was correct, and made me aware of the DLDMGACTION option which I had not heretofore been exposed to, so I think I have to award the solution to him.  

I think the real difference between the REPAIR you suggested and the REBUILD @ghosh suggested is that the behavior of REBUILD will be different depending on the value of DLDMGACTION, a nice flexibility to have.

Knowing now about the DLDMGACTION option, I was then able to write a little macro (see below) that can set the DLDMGACTION option based on how the given SAS session was instantiated.  I then put that macro into my initialization routine, and I'm all set.

Jim

/*-------------------------------------------------------------------------------*/
/*	Macro:	Set_DLDMGACTION
/*  Author: Jim Barbour 
/*	Date:	22 June 2020
/*	Owner:	OptumInsight, ACA/MA Chart Review
/*	Info:	This macro sets the value of the DLDMGACTION option based on the 
/*			way that your SAS code was submitted.  There are four ways that SAS
/*			code can be submitted:
/*				SEG - SAS Enterprise Guide.
/*				DMS - Display Manager Session.
/*				CLI - Command Line Interface ("Batch").
/*				SCS - SAS Connect Session (Code submitted via an RSUBMIT).
/*
/*			The DLDMGACTION option controls what will be done if SAS encounters a  			
/*			damaged dataset.  The valid options are as follows:								
/*				FAIL	- Stop the step and write an error message.							
/*				ABORT	- Stop the step, write an error message, abort the session.
/*				REPAIR	- Repair the damaged dataset, including indices and 
/*							constraints, and write a warning to the log.									
/*				NOINDEX	- Repair the damaged dataset but do not re-create any 
/*							indices and write a warning to the log.									
/*				PROMPT	- Interactively prompt the user for the action to take.			
/*-------------------------------------------------------------------------------*/
/*									CHANGE LOG
/*-------------------------------------------------------------------------------*/
/*	Name:	Jim Barbour				Date:	22 June 2020
/*	Info:	Original implementation.
/*-------------------------------------------------------------------------------*/

%MACRO	Set_DLDMGACTION(DMS_Opt=REPAIR, SEG_Opt=REPAIR, Batch_Opt=NOINDEX, Debug=NO, Width=40);
	%LOCAL	Macroname;
	%LET	Macroname				=	&Sysmacroname;

	%IF	%QUPCASE(&Debug)			=	YES		%THEN
		%DO;
			OPTION	NOSOURCE;
			%PUT	&Nte2  ;
			%PUT	&Nte2  %Format_Dashes(&Width);
			%PUT	&Nte1  %Format_Line(Macro &Macroname, &Width);
			%PUT	&Nte2  %Format_Line(Before:  DLDMGACTION is %SYSFUNC(GETOPTION(DLDMGACTION))., &Width);
			OPTION	SOURCE;
		%END;

	%IF	%Get_Process_Mode			=	DMS		%THEN
		%DO;
			OPTION	DLDMGACTION		=	&DMS_Opt;
		%END;
	%ELSE
	%IF	%Get_Process_Mode			=	SEG		%THEN
		%DO;
			OPTION	DLDMGACTION		=	&SEG_Opt;
		%END;
	%ELSE
		%DO;
			OPTION	DLDMGACTION		=	&Batch_Opt;	
		%END;

	%IF	%QUPCASE(&Debug)			=	YES		%THEN
		%DO;
			OPTION	NOSOURCE;
			%PUT	&Nte2  %Format_Line(After:   DLDMGACTION is %SYSFUNC(GETOPTION(DLDMGACTION))., &Width);
			%PUT	&Nte2  %Format_Dashes(&Width);
			OPTION	SOURCE;
		%END;
%MEND	Set_DLDMGACTION;

[ChrisNZ]

Nice macro @jimbarbour . 

2 comments:

- I'd replace     OPTION NOSOURCE;      with       OPTION NOSOURCE PS=max;    to avoid page breaks in your message.

- I'd replace     OPTION SOURCE;         and restore option(s) as before the macro was called.

[jimbarbour]

Actually, those are both really good ideas.  I've done some of that (saving the option settings, changing the options to suit the needs of a particular section of code, and then resetting the options as they were prior that section), but I didn't think about PS=MAX.  

If you saw my macro calls to %Format_Dashes and %Format_Line, those are to write displays in the log that stand out a bit more.  In batch, our logs are monochrome, and sometimes it's hard to spot key statistics, control parameters, and such, so I wrote a little macro to put a sort of box around things. For example, the below is from a series of RSUBMITs of processes that run concurrently.  If you've ever seen how SAS conglomerates the log from concurrent processes, it's a real pain in the posterior to ferret out what you're looking for.  The formatted displays make it just a bit easier to spot key bits of information in the log.

It's a bit silly to got to the trouble of writing formatted log statements only to have them interrupted by pagination statements, so PS=MAX is a good suggestion indeed.

Jim

     
       +----------------------------------------------+
NOTE:  | Remote return code values are as follows:    |
       | CPT_Rela_RC = 0                              |
       | CR_Suspc_RC = 0                              |
       | DX_Relat_RC = 0                              |
       | Lab_Susp_RC = 0                              |
       | Manifest_RC = 0                              |
       | No_HCCMV_RC = 0                              |
       | Prv_Code_RC = 0                              |
       | RX_Suspc_RC = 0                              |
       |           2020/06/17  00:18:28.58            |
       +----------------------------------------------+
     

[jimbarbour]

OK, with that mod, the macro looks like the below and the log message follows that.  It's a bit verbose.  Actually NOSOURCE/2 aren't required inside a macro; they're really only needed in open code.  I mainly use them for testing purposes so that I can run snippets of code quickly without having to have the entire macro written.  So, the macro could be pared down a bit.  The PS=MAX idea is worth it's weight.  Thank you for that.

Jim

%MACRO	Set_DLDMGACTION(DMS_Opt=REPAIR, SEG_Opt=REPAIR, Batch_Opt=NOINDEX, Debug=NO, Width=40);
	%LOCAL	Macroname;
	%LOCAL	Save_Source;
	%LOCAL	Save_Source2;
	%LOCAL	Save_PS;

	%LET	Macroname				=	&Sysmacroname;

	%IF	%QUPCASE(&Debug)			=	YES		%THEN
		%DO;
			%LET	Save_Source		=	%SYSFUNC(GETOPTION(SOURCE));
			%LET	Save_Source2	=	%SYSFUNC(GETOPTION(SOURCE2));
			%LET	Save_PS			=	%SYSFUNC(GETOPTION(PS));
			OPTIONS	NOSOURCE		NOSOURCE2		PS=MAX;
			%PUT	&Nte2  ;
			%PUT	&Nte2  %Format_Dashes(&Width);
			%PUT	&Nte1  %Format_Line(Macro &Macroname, &Width);
			%PUT	&Nte2  %Format_Line(Before:  DLDMGACTION is %SYSFUNC(GETOPTION(DLDMGACTION))., &Width);
			OPTION	&Save_Source	&Save_Source2	PS=&Save_PS;
		%END;

	%IF	%Get_Process_Mode			=	DMS		%THEN
		%DO;
			OPTION	DLDMGACTION		=	&DMS_Opt;
		%END;
	%ELSE
	%IF	%Get_Process_Mode			=	SEG		%THEN
		%DO;
			OPTION	DLDMGACTION		=	&SEG_Opt;
		%END;
	%ELSE
		%DO;
			OPTION	DLDMGACTION		=	&Batch_Opt;	
		%END;

	%IF	%QUPCASE(&Debug)			=	YES		%THEN
		%DO;
			%LET	Save_Source		=	%SYSFUNC(GETOPTION(SOURCE));
			%LET	Save_Source2	=	%SYSFUNC(GETOPTION(SOURCE2));
			%LET	Save_PS			=	%SYSFUNC(GETOPTION(PS));
			OPTIONS	NOSOURCE		NOSOURCE2		PS=MAX;
			%PUT	&Nte2  %Format_Line(After:   DLDMGACTION is %SYSFUNC(GETOPTION(DLDMGACTION))., &Width);
			%PUT	&Nte2  %Format_Dashes(&Width);
			OPTION	&Save_Source	&Save_Source2	PS=&Save_PS;
		%END;
%MEND	Set_DLDMGACTION;

       +--------------------------------------+
NOTE:  | Macro SET_DLDMGACTION                |
       | Before:  DLDMGACTION is FAIL.        |
       | After:   DLDMGACTION is REPAIR.      |
       +--------------------------------------+

[ChrisNZ]

You could use:

%LET Save_Opt = %SYSFUNC(GETOPTION(SOURCE)) %SYSFUNC(GETOPTION(SOURCE2)) %SYSFUNC(GETOPTION(PS));

for more compacity.

Also you only need to save at the start and restore at the end.

[jimbarbour]

Ah!  Well, now, that's true isn't it?  There's no reason that I would have to save each option setting individually, now is there?  I can save all three in one macro variable.  Very good.  Thank you.

Jim