Date:         Mon, 27 Sep 2010 13:44:10 -0500
Reply-To:     Joe Matise <snoopy369@GMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Joe Matise <snoopy369@GMAIL.COM>
Subject:      Re: updating a format using CNTLIN option
Comments: To: "White, Svend A." <SvendW@health.ok.gov>
In-Reply-To:  <54E01EE17F635448A5CE0C2253E23FF902942155@CONSTANCE.DOH.OK>
Content-Type: text/plain; charset=ISO-8859-1

The range '1-1' overlaps, meaning there are two different rows with Start=1
end=1(or no end) for that format.  If it's not a MLF, it's not allowed to
have two rows with the same format value.  You probably have an old value
for that which you need to remove (or UPDATE) rather than INSERT.  You can
always do a PROC SORT NODUPKEY by {fmtname start} in order to find the
offending value.

-Joe

On Mon, Sep 27, 2010 at 1:36 PM, White, Svend A. <SvendW@health.ok.gov>wrote:

> I'm trying to add a new value to the existing format and I'm getting
> this error message:
> > ERROR: This range is repeated, or values overlap: 1-1.
>
> Sounds like a straight-forward bug, but I haven't been able to find any
> issues in the 800 or so insert statements (which I generated in another
> tool; they weren't hand-coded).
>
> Does the "1-1" refer to the first INSERT INTO statement below? If so,
> what doesn't it like? That SQL successfully updates the dataset output
> by the FORMAT CNTLOUT option. Does the dataset need to be parsed somehow
> on the way back into format?
>
> Thanks.
>
> Svend
>
>
>
> LOG OUPUT:
> -----------------------------------------
> 3365  proc format library=Inpatien cntlin=Work.newformat;
> NOTE: Format ADMST is already on the library.
> NOTE: Format ADMST has been written to INPATIEN.FORMATS.
> [...]
> NOTE: Format RACE is already on the library.
> NOTE: Format RACE has been written to INPATIEN.FORMATS.
> ERROR: This range is repeated, or values overlap: 1-1.
> 3366  run;
>
> SAS CODE
> ------------------------------------------
>
> proc format library=Inpatien cntlout=Work.newformat;
> run;
>
> addnewvaltoformat;
>
> proc format library= Inpatien cntlin=Work.newformat;
> run;
>
> %macro addnewvaltoformat;
>        proc sql;
>        %local c;%let c=0; /* This will count the inserts.*/
>        /* ADD VALUES TO DATASET */
>        INSERT INTO newformat (FMTNAME, START, END, LABEL) VALUES
> ('DRG2008f','1','1','Heart transplant or implant of heart assist system
> w MCC');%let c=%eval(&c+1);
>        INSERT INTO newformat (FMTNAME, START, END, LABEL) VALUES
> ('DRG2008f','2','2','Heart transplant or implant of heart assist system
> w/o MCC');%let c=%eval(&c+1);
>        [...]
>        INSERT INTO newformat (FMTNAME, START, END, LABEL) VALUES
> ('DRG2008f','998','998','Principal diagnosis invalid as discharge
> diagnosis');%let c=%eval(&c+1);
>        INSERT INTO newformat (FMTNAME, START, END, LABEL) VALUES
> ('DRG2008f','999','999','Ungroupable');%let c=%eval(&c+1);
>        %put The SQL return code is %sqlrc;
>        /* Roll BACK ON ERROR.*/
>        %if &sqlrc GT 0 %then
>                %do;
>                        %put ***SOMETHING WENT WRONG! UNDOING
> INSERTS.***;
>                        delete from newformat where FMTNAME= 'DRG2008f';
>
>                %end;
>        %else;
>                %do;
>                        %put ***DONE. &c ROWS WERE INSERTED.***;
>                        /* ADD OTHER STATIC FIELDS THAT ARE REQUIRED. */
>                        UPDATE  newformat
>                                SET  MIN=1, MAX=131, DEFAULT=131,
> LENGTH=131, FUZZ=1E-12, MULT=0, NOEDIT=0, TYPE='N', SEXCL='N', EEXCL='N'
>                                WHERE FMTNAME= 'DRG2008f';
>                %end;
>        quit;
>        run;
> %mend;
>