limit of 4096 formats in one single datastep has been exceeded

[deleted_user]

Hi, I've got a dataset with around 5000 variables, that need formats assigned. I have two sas programs. The first one is defining the formats and saving them into a SAS catalog file:

LIBNAME dataset 'path';

proc format library=dataset;

/* Wave 1 formats */
value STATEID /* State/Territory */
1 = 'ACT'
2 = 'NSW'
3 = 'VIC'
4 = 'QLD'
5 = 'SA'
6 = 'WA'
7 = 'TAS'
8 = 'NT' ;
value SECTOR /* School sector */
1 = 'Government'
2 = 'Catholic'
3 = 'Independent' ;
....
RUN;

The second program is assigning the formats:
LIBNAME dataset 'path';
OPTION fmtsearch=(dataset);

proc datasets library = dataset ;
modify nameofdataset;
FORMAT
/* Wave 1 fixed formats */
STATEID STATEID.
SECTOR SECTOR.
....
;
QUIT;

I have a couple of proc datasets in this program, so that not all 5000 formats get assinged at once, which creates problems. The program works fine and assigns all formats (based on the log info). If I want to continue working with this file in a datastep, it gives me the following error message:"limit of 4096 formats in one single datastep has been exceeded" and if I have a look in the log, all formats after the 4096th create problems "format not found or could not be loaded". Also, if I want to view the dataset in the sas viewer it gives me an error message that table selected can't be opened...However, if I use STAT Transfer to export the dataset (incl. formats) to Stata, and open it in Stata, the dataset looks fine, including all formats. I had to increase memory in Stata, so I'm wondering whether there is something similar in SAS, so that I can keep working with this dataset in SAS.
Thanks for any help/tips/advice 🙂

[PaulHomes]

Hi,

You might be interested in looking at a recent SAS Global Forum paper on this very topic by Philip A. Wright "Eliminating Redundant Custom Formats (or How to Really Take Advantage of PROC SQL, PROC CATALOG, and the DATA Step)" http://support.sas.com/resources/papers/proceedings10/010-2010.pdf

Cheers
Paul

[PaulHomes]

... and there's also an old-ish usage note about it too: http://support.sas.com/kb/12/621.html

I must confess this limit was news to me. In my 20 odd years of SAS programming I have never encountered it, but I guess I've never had to deal with such wide tables requiring so many formats 🙂

Do you have any redundant/repeated formats you can consolidate to get yourself under the 4096 limit?

[deleted_user]

Hi, yes there are redundant formats, however I shouldn't change them. I'm preparing this data for archiving, and it was deposited this way, therefore I would prefer not to change the formats. It's just strange that stata has a way to deal with it and sas not?!?!

[Peter_C]

impressive collection of information in 5,000 columns!
Please could you describe any reports or analyses which need more than (say) 1000 of these columns?
(I expect we might have fundamental solutions to the restriction on the number of formats - but it depends on how you actually use - or plan to use - all those columns)

hth
peterC

[deleted_user]

Ok, here is a bit of background information: This is a longitudinal dataset, that consists of 7 waves. This particular dataset includes all waves and I prepare it for archiving (I cannot drop any columns at this stage and I cannot seperate the waves). Obviously I can assign all formats, however if I then run a data step afterwards to eliminate columns that I don't need, it won't execute, showing the error message I mentioned above. So basically, if someone else requests this dataset for secondary analysis, they won't be able to even run one data step to select the columns they want.