topic proc format cntlin error in SAS Programming

proc format cntlin error

lrin00 — Fri, 14 Aug 2020 20:46:43 GMT

I'm trying to create a format by using an imported data set from the excel file. I kept receiving the error "ERROR: For format GAAPUPR, this range is repeated, or values overlap: .-.." when using PROC FORMAT cntlin for below code.

Data FMTGAAPUPR;
	Retain FMTNAME 'GAAPUPR';
	set GAAPUPR (RENAME=(KEY=START) RENAME=(unearned_gaap=LABEL));

run;

proc format	cntlin=FMTGAAPUPR; run;

I've attached the excle file that I imported to sas.

Thanks in advance for the help.

Re: proc format cntlin error

smantha — Fri, 14 Aug 2020 21:29:04 GMT

The reason you are getting an error is you are trying to assign a numeric format to a character variable in your case The key variable is character. Try assigning a character format. Or change the key value to numeric

Re: proc format cntlin error

ballardw — Fri, 14 Aug 2020 22:24:13 GMT

Not going to open an XLSX from unknown source for a number of reasons. And since it is NOT your SAS data set is a bit moot to some extent. Many users here don't want to download Excel files because of virus potential, others have such things blocked by security software. Also if you give us Excel we have to create a SAS data set and due to the non-existent constraints on Excel data cells the result we end up with may not have variables of the same type (numeric or character) and even values.

Run this code:

Proc freq data=gaapupr ;
   tables key*unearned_gaap / missing list;
run;

If Key appears with more than one value of unearned_gaap then that is the cause of your error.

Using data similar to what I expect would be the equivalent of this (incorrect) format code:

Proc format;
value $sometext
"abc" = "value 1"
"abc" = "value 2"
;
run;

The only time you can have the same value that has different labels assigned is when you use a multilabel format definition and that has another set of constraints.

Re: proc format cntlin error

Tom — Fri, 14 Aug 2020 23:38:28 GMT

You probably have empty cells in your excel file.

What happens if you exclude them ?

proc format cntlin=FMTGAAPUPR(where=(not missing(start)); 
run;