Help using Base SAS procedures

PROC FORMAT: How to make a library from an existing data set?

Reply
Frequent Contributor
Posts: 92

PROC FORMAT: How to make a library from an existing data set?

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

SAS Super FREQ
Posts: 8,864

Re: PROC FORMAT: How to make a library from an existing data set?

Posted in reply to BillJones

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

Frequent Contributor
Posts: 92

Re: PROC FORMAT: How to make a library from an existing data set?

Posted in reply to Cynthia_sas

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

Super User
Posts: 3,252

Re: PROC FORMAT: How to make a library from an existing data set?

Posted in reply to BillJones

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.


Super User
Super User
Posts: 7,039

Re: PROC FORMAT: How to make a library from an existing data set?

Posted in reply to BillJones

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;

Regular Contributor
Posts: 217

Re: PROC FORMAT: How to make a library from an existing data set?

Posted in reply to BillJones

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;

Frequent Contributor
Posts: 92

Re: PROC FORMAT: How to make a library from an existing data set?

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

Super User
Super User
Posts: 7,039

Re: PROC FORMAT: How to make a library from an existing data set?

Posted in reply to BillJones

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?

Super User
Posts: 19,772

Re: PROC FORMAT: How to make a library from an existing data set?

Posted in reply to BillJones

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.

Frequent Contributor
Posts: 92

Re: PROC FORMAT: How to make a library from an existing data set?

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

Super User
Posts: 19,772

Re: PROC FORMAT: How to make a library from an existing data set?

Posted in reply to BillJones

Does it do what you want?

Frequent Contributor
Posts: 92

Re: PROC FORMAT: How to make a library from an existing data set?

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

Super User
Posts: 19,772

Re: PROC FORMAT: How to make a library from an existing data set?

Posted in reply to BillJones

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.

Ask a Question
Discussion stats
  • 12 replies
  • 780 views
  • 0 likes
  • 6 in conversation