proc format cntlin error

[lrin00]

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.

[smantha]

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

[ballardw]

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.

[Tom]

You probably have empty cells in your excel file.

What happens if you exclude them ?

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