topic Re: Re-Create Index in Enterprise Guide in SAS Programming

Re-Create Index in Enterprise Guide

jimbarbour — Mon, 22 Jun 2020 00:36:25 GMT

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

Re: Re-Create Index in Enterprise Guide

ghosh — Mon, 22 Jun 2020 02:12:18 GMT

The code here will rebuild the index for you

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

Re: Re-Create Index in Enterprise Guide

ChrisNZ — Mon, 22 Jun 2020 02:30:01 GMT

Have you tried using proc dataset's repair statement?

Re: Re-Create Index in Enterprise Guide

ChrisNZ — Mon, 22 Jun 2020 03:24:19 GMT

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:

Re: Re-Create Index in Enterprise Guide

jimbarbour — Mon, 22 Jun 2020 23:17:19 GMT

@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

Re: Re-Create Index in Enterprise Guide

jimbarbour — Mon, 22 Jun 2020 23:25:10 GMT

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

Re: Re-Create Index in Enterprise Guide OPTION NOSOURCE;

ChrisNZ — Tue, 23 Jun 2020 00:37:43 GMT

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.

Re: Re-Create Index in Enterprise Guide OPTION NOSOURCE;

jimbarbour — Tue, 23 Jun 2020 03:59:26 GMT

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

Re: Re-Create Index in Enterprise Guide OPTION NOSOURCE;

jimbarbour — Tue, 23 Jun 2020 05:11:12 GMT

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.      |
       +--------------------------------------+

Re: Re-Create Index in Enterprise Guide OPTION NOSOURCE;

ChrisNZ — Tue, 23 Jun 2020 05:18:31 GMT

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.

Re: Re-Create Index in Enterprise Guide OPTION NOSOURCE;

jimbarbour — Tue, 23 Jun 2020 13:57:15 GMT

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