ERROR 48-59: The format $Z was not found or could not be loaded

[KAS25]

Trying to figure what happen all of a sudden.  The maybe the formatting in the excel file change some kind of way.  It's trying to put the leading zero's in front of (ex. 66.50) so in SAS it would look like 0006650 after the script executes successfully.

223 Loan_Amount z7.
224 Record_Type $4.
225 Last_Name $17.
WARNING: Variable Record_Type has already been defined as numeric.
226 First_Name $12.
227 Middle $12.
228 DB z2.
---
48
ERROR 48-59: The format $Z was not found or could not be loaded.

229 ;
230 Loan_Amount=compress(put(Loan_Amount,z7.2),'.');
231 run;

NOTE: Character values have been converted to numeric values at the places given by: (Line):(Column).
230:17
NOTE: The SAS System stopped processing this step because of errors.

Any help is appreciated.

[ballardw]

You are having issues with not knowing what type of values you have.

DB is character. So SAS expects any character variables to reference a Format starting with $.

The reason I say you have issues because you have a similar issue with Record_type trying to use a Character format with a numeric value. Since the format $n. is a SAS supplied format it exists and does not throw an error. But you have not defined, and likely shouldn't, a format $Z

224 Record_Type $4.

WARNING: Variable Record_Type has already been defined as numeric.

And repeat the confusion with compressing a value(which creates a character value) and assigning it to a Numeric variable. If you want to shift the decimal value for loan_amount then multiple it by 100 and then truncate the result if needed: 

loan_amount = floor(loan_amount*100);

[KAS25]

I am missing something. I'm assuming the error is on the loan amount. The excel column for LOAN amount is custom to 00000.00. That was the way I was able to get it to come into SAS as a correct dollar amount. In the SAS table it has ex. 66.50
Then I need it to go back to 0006650. This was working and not sure what changed. Any other information needed let me know.

[Shmuel · Accepted solution]

Pay attention, your code Record_Type $4. result with warning:

WARNING: Variable Record_Type has already been defined as numeric

reading DB z2. result with error message: 

ERROR 48-59: The format $Z was not found or could not be loaded

both cases are result of importing excel data. Sas guess which variables are numeric and which are

alphanumeric according to first rows. You can change that option in proc import or better way,

copy the generated code from proc import and refine it to read with proper variable type.

in order to add leading zeroes variable must be numeric and leading zeroes will be added while 

printing or converting to char type by format zd. as you used in put(Loan_Amount,z7.2)

[KAS25]

I am missing something.  I'm assuming the error is on the loan amount. The excel column for LOAN amount is custom to 00000.00.  That was the way I was able to get it to come into SAS as a correct dollar amount.  In the SAS table it has ex. 66.50

Then I need it to go back to 0006650.  This was working and not sure what changed.   Any other information needed let me know.

[ballardw]

---

@KAS25 wrote:

I am missing something.  I'm assuming the error is on the loan amount. The excel column for LOAN amount is custom to 00000.00.  That was the way I was able to get it to come into SAS as a correct dollar amount.  In the SAS table it has ex. 66.50

Then I need it to go back to 0006650.  This was working and not sure what changed.   Any other information needed let me know.

SAS numeric values do not store leading zeroes. They can be created for output/ display with the Zw.d numeric

\format. However the variable must be numeric to apply the Z format. Not the same but similar to the Excel display settings.

Unfortunately because of the very large number of people that routinely use spreadsheets they typically do not enforce any rules on anything. The first row of a column can have numeric, the second a formulat and the third a character value. And depending on how carefully you look at your spread sheet you may not even notice.

Proc Import makes guesses every time a spreadsheet is read. So even if a spreadsheet is supposed to be "the same" as another you can get different results based on what was actually entered into cells. And if the spreadsheet is manually updated it gets more unreliable depending on what people actually do.

If your process involves Proc Import you have to verify every single time that the variables created have the properties you need/expect, like numeric/ character, length of character variables and if values are actually SAS Date, time or datetime values or something character or numeric that you think looks date like.