BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
DavidBess
Calcite | Level 5

Greetings,

I am getting an error which I have not been able to troubleshoot with google or by searching these forums.

I get the following error when trying to open a dataset outputted by a proc freq.

ERROR: Format MISS not found or couldn't be loaded for variable

First, I applied a format to the variables in my data set, then I do a proc freq to find missing vs non-missing values.

The proc freq is outputting as expected, when I add the out= option, I do not get an error, but when I try and open the data set It fails to open. The
log shows.

ERROR: Format MISS not found or couldn't be loaded for variable ....

And a message box reads "Unable to open the table, would you like to select another table to open. Here is my
code.

format;

value $miss " "="missing"

"nomissing";

run;

format;

miss .="missing"

"nomissing";

run;

proc contentsdata=Sandbox.&Basicnoprint out=Sandbox.Basic_name(keep=name format formatl);

run;

data Sandbox.Basic_Chars;

set Sandbox.Basic_name;

if format = "$";

run;

data Sandbox.Basic_Nums;

set Sandbox.Basic_name;

if format = ""then format = "NUMERIC";

if format = "NUMERIC";

run;

data Sandbox.Basic_Dates;

set Sandbox.Basic_Name;

if format = "DATETIME";

run;

proc sql noprint;

select trim(left(name)),
count(*)

into :basic_char_list
separated
by ' ', :nlist /*monitor the number of names in this list*/

from Sandbox.Basic_Chars;

quit;

proc sql noprint;

select trim(left(name)),
count(*)
into :basic_num_list
separated
by ' ', :nlist /*monitor the number of names in this list*/

from Sandbox.Basic_Nums;

quit;

proc sql noprint;

select trim(left(name)),
count(*)
into :basic_date_list
separated
by ' ', :nlist /*monitor the number of names inthis list*/

from Sandbox.Basic_Dates;

quit;

%put &basic_char_list.;

%put &basic_num_list.;

%put &basic_date_list.;

proc freq data=Sandbox.&Basic;

tables &basic_char_list. &basic_num_list. &basic_date_list. / missingout=sandbox.basic_freq;

format &basic_char_list. $miss. &basic_num_list. &basic_date_list.miss.;

run;

I get no errors until I try and open the outputted data
file, then it reads.

ERROR: Format MISS not found or couldn't
be loaded for variable transaction_entry_date.

This was one of the variables
formatted as datetime. 

Any ideas would be greatly appreciated. 

Thanks,

David Bess

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

Ok...so you submit the formats to the server and they're created on the server, but then try and open it on local, but you haven't created the formats on local so SAS can't reference them.


Run the formats on your local drive and then try and open it.

View solution in original post

7 REPLIES 7
Reeza
Super User

Are you by chance running part of this on a server?

Fugue
Quartz | Level 8

Your proc format statements appear to be missing some pieces (like the VALUE keyword). Perhaps you could re-paste the code that creates the formats?

Here is an example from SAS documentation.

proc format;

     value $ missfmt ' '="Missing"

                   other="Not Missing"

     ;

     value  nmissfmt  . ="Missing"

                   other="Not Missing"

     ;

run;

DavidBess
Calcite | Level 5

@Reeze - Yes all of these statements are run in a remote submit sandwitch.  The sandbox Library is also on the remote server.

@Fugue - I do not know why I am having trouble pasting properly to this forum.  Here is the corrected code.

Proc format;

value $miss " "="missing"

other="nomissing";

run;

proc format;

value miss .="missing"

other="nomissing";

run;

Fugue
Quartz | Level 8

What happens if you specify the nofmterr option.

Reeza
Super User

Ok...so you submit the formats to the server and they're created on the server, but then try and open it on local, but you haven't created the formats on local so SAS can't reference them.


Run the formats on your local drive and then try and open it.

DavidBess
Calcite | Level 5

The problem indeed did lie with the fact that I had not taught the format the local machine.

Thanks!

Unfortionately, now that I have working output, I relaize that this is not the result I was after. This output option for proc freq only writes the last item from the tables statement to a data set.  I am writting an array right now to get everything into one data set.

Reeza
Super User

Capture the ODS table instead.

There's an easier way to do this by the way. If you google on here I have a macro that does it very neatly.

For example _character_ and _numeric_ are shortcuts for all numeric and character variables and all dates are numbers so this would create your variable lists.

EDIT:

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 7272 views
  • 3 likes
  • 3 in conversation