Date:         Mon, 27 Sep 2010 14:06:02 -0500
Reply-To:     "White, Svend A." <SvendW@HEALTH.OK.GOV>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "White, Svend A." <SvendW@HEALTH.OK.GOV>
Subject:      Re: updating a format using CNTLIN option
In-Reply-To:  <AANLkTi=iiS8Aey_2Yg7TUuMjCH_Q8zJNfWZFfuhgYNbW@mail.gmail.com>
Content-Type: text/plain; charset="US-ASCII"

Thanks, Joe. I sorted it out (no pun intended), and it turned out to be
something quite different. Thought there was logic in my code that saw
to it that old temporary datasets would be ignored, but when I happened
to clean out the old datasets previously created by this script and
re-ran it, suddenly all  was smooth sailing. Sorry about that.

 

Thanks.

 

 

 

From: Joe Matise [mailto:snoopy369@gmail.com] 
Sent: Monday, September 27, 2010 1:44 PM
To: White, Svend A.
Cc: SAS-L@listserv.uga.edu
Subject: Re: updating a format using CNTLIN option

 

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;