07-02-2014 01:20 PM
I have a data set where I want to take the current format info and output it to a format library. Specifically, I'm interested in the following variables: fmtname type start end label hlo eexcl sexcl. My apologies if this is a really basic question.
Here's the code that I currently working with:
proc format cntlin = in.&filename library = library; run;
However, whenever I run this, I get the following error:
ERROR: Missing FMTNAME variable.
ERROR: Missing START variable.
ERROR: Missing LABEL variable.
Any suggestions would be greatly appreciated. Thanks very much.
07-02-2014 02:03 PM
Hi: Take a look at this Tech Support note 25054 - Adding an OTHER category to a format using CNTLIN in PROC FORMAT the dataset that you use with CNTLIN must use certain variable names as shown in the code. Your error messages indicates that some of the required variable names like FMTNAME and START and LABEL are not in your input dataset. Your PROC FORMAT will NOT work until you have the variable names that are expected.
07-02-2014 03:15 PM
Thanks very much for the response. Is there code to automatically generate the required variables? In the example you referenced, there's a data step where the required variables for proc format are created. I have probably 30 tables where I want to create libraries that capture the existing format information.
07-02-2014 03:34 PM
You could construct a SAS macro to do this, but if you are not familiar with SAS macro then it would be easier to just hard-code the necessary DATA steps.
If you want to go down the macro path then it would need to have parameters for fmtname, start, label, input dataset, whether you want an OTHER or MISSING category.
07-02-2014 03:35 PM
What is it that you want to do? To convert a DATASET to a FORMAT only makes sense if the data set in question consists of two columns. Basically a set of code/decode or value/label pairs.
For example you could use this code to create a format from the NAME and AGE variables in the sample dataset SASHELP.CLASS.
proc sql noprint ;
create table formats as
"CLASS" as FMTNAME
, 'C' as TYPE
, NAME as start
, AGE as label
order by FMTNAME, NAME
proc format lib=work cntlin=formats;
proc format lib=work fmtlib ;
select $CLASS ;
07-02-2014 03:36 PM
Below is code that worked for me. In my situation I did not need an "END=" line. The PIPLOB was automatically also assigned as the END= value and that was fine for what I needed. I recommend adding the LASTREC, "OTHER" statement.
Essentially the code writes .......... value $pip "value of piplob" = "value of product"..........other = 'N/A'.......
proc sql; drop table pip; quit;
DATA pip(KEEP=FMTNAME START LABEL) ;
LENGTH FMTNAME $ 8 START $ 5 LABEL $6;
set piplob END=LASTREC;
where product not = " ";
IF LASTREC THEN DO;
PROC FORMAT CNTLIN=pip;
07-02-2014 04:56 PM
THANK YOU VERY MUCH everyone for the responses. I really appreciate the help.
SASKiwi, I definitely want to create a macro solution. Like I mentioned above, I have approximately 30 tables where I want to take the existing format information and output it to a library.
Tom, What I'm trying to do is a create a "2-level name of format library", so I can run proc_codebook, which is macro program that gives useful information on a given data set. I was hoping to run it on a large number of tables that recently got handed over to me. However, when I try to create the format library necessary to run the code, I get an error.
Link to proc_codebook:
I guess a broader question would be is there a more recent "codebook" available online? Perhaps, I can just use that instead?
jwillis, Tweaked your code a bit to see if I could get it to work for one of the data files that I have. Still tweaking. I'll probably have to something like proc contents to list out all the variables?
Thanks again everyone for your thoughts.
07-02-2014 05:47 PM
I am sorry but you seem to be talking about something completely different that I thought before.
Are you asking how get the variable names, types, lengths, formats and labels for all tables in a library? Just run PROC CONTENTS.
proc contents data=mylib._all_ noprint out=contents; run;
proc print data=contents;
by memname ;
var name type length format label;
If you want to do more extensive information like what it looks like the SAS program you posted is trying to generate then why not just run that one?
Is there something about it that you do not understand?
07-02-2014 06:02 PM
Are you by chance looking for cntlout rather than cntlin? CNTLOUT creates the format tables from an old library and then you can use CNTLIN to put them to a new library.
07-02-2014 06:20 PM
Thanks very much for the response. Do you mean something like:
proc format cntlout = in.&filename; run;
proc format cntlin = in.&filename library=library; run;
07-02-2014 06:35 PM
Sort of. I want a library which shows for each variable the following format info: fmtname type start end label hlo eexcl sexcl. The code above gives me a file with fmtname type start end label hlo eexcl sexcl, but not for each variable.
How can I get the format info by variable? Is there a by statement that I can employ within proc format? Or do I need to write a macro?
Thanks very much!
07-02-2014 06:51 PM
I don't think you need a macro. Look at SASHELP.VCOLUMN table to extract the format applied for each variable, and then merge the two tables.