BookmarkSubscribeRSS Feed
BillJones
Calcite | Level 5

Hello,

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.

-Bill

12 REPLIES 12
Cynthia_sas
SAS Super FREQ

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.

cynthia

BillJones
Calcite | Level 5

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.

-Bill

SASKiwi
PROC Star

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.


Tom
Super User Tom
Super User

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

  select DISTINCT

         "CLASS" as FMTNAME

       , 'C' as TYPE

       , NAME as start

       , AGE as label

  from sashelp.class

  order by FMTNAME, NAME

;

quit;

proc format lib=work cntlin=formats;

run;

proc format lib=work fmtlib ;

select $CLASS ;

run;

jwillis
Quartz | Level 8

Bill,

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 = " ";
    FMTNAME='$pip';
    START=piplob;
    LABEL=product;
    OUTPUT;
    IF LASTREC THEN DO;
      START='OTHER';
      LABEL='N/A';
      OUTPUT;
      END;
run;


PROC FORMAT CNTLIN=pip;
run;

BillJones
Calcite | Level 5

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:

http://www.cpc.unc.edu/research/tools/data_analysis/proc_codebook/proc_codebook.sas

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.

-Bill

Tom
Super User Tom
Super User

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;

run;

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?

Reeza
Super User

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.

BillJones
Calcite | Level 5

Reeza,

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;

-Bill

Reeza
Super User

Does it do what you want?

BillJones
Calcite | Level 5

Reeza,

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!

-Bill

Reeza
Super User

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.

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 12 replies
  • 2901 views
  • 0 likes
  • 6 in conversation