CODEWITHSUNDEEP
sascode-generationbinning
Violatic
Violatic

Reputation: 384

Generating many tables from a single table in SAS

I have a table in SAS which contains the format information I want. I want to bin this data into the categories given.

What I don't know how to do is create either an xform or a format file from the data.

An example table looks like this:

     TxtLabel  Type FmtName label   Hlo count
         .      I   FAC1f    0      O    1
        1996    I   FAC1f    1           2
        1997    I   FAC1f    2           3

I want to date all years in a different data set as after 1997 OR before 1996.

The problem is that I know how to do this by hard coding it, but these files changes the numbers each time so I'm hoping to use the information in the table to generate the bins rather than hard code them.

How do I go about binning by data using a column from another dataset for my categorization?

Edit

I have two data sets, one which looks like the one I have included and one which has a column titled "YEAR". I want to bin the second data set using the categories from the first. In this case there are two available years in TxtLabel. There are multiple tables like this, I'm looking at how to generate PROC Format code from the table, rather than hard coding the values.

Upvotes: 0

Views: 136

Answers (1)

Richard
Richard

Reputation: 27508

This should run to create the desired format

Proc FORMAT CNTLIN=MyCustomFormatControlData;
run;

You can then use it in a DATA Step, or apply it to a column in a data set.

Binning the data might be construed as 'data set splitting' but your question does not make it clear if that is so. Generic arbitrary splitting is often done with one of these techniques:

  • wall paper source code resolved from macro variables populated from information garnered in a Proc SQL or Proc FREQ step
  • dynamic data splitting using hash object for grouping records in memory, and saved to a data set with an .output() call.

Sample code for explicit binning

data want0 want1 want2 want3 want4 want5 wantOther;
  set have;
  * explicit wall paper;
  select (put(year,FAC1f.));
    when ('0') output want0;
    when ('1') output want1;
    when ('2') output want2;
    when ('3') output want3;
    when ('4') output want4;
    when ('5') output want5;
    otherwise output wantOther;
run;

This is the construct that source code generated by macro can produce, and requires

  • one pass to determine the when/output lines that are to be generated
  • a second pass to apply the lines of code that were generated.

If this is the data processing that you are attempting:

  • do some research (plenty of info out there)
  • write some code
  • make a new question if you get errors you can't resolve

Proc FORMAT

Proc FORMAT has a CNTLIN option for specifying a data set containing the format information. The structure and values expected of the Input Control Data Set (that CNTLIN) is described in the Output Control Data Set documentation. Some of the important control data columns are:

FMTNAME
specifies a character variable whose value is the format or informat name.

LABEL
specifies a character variable whose value is associated with a format or an informat.

START
specifies a character variable that gives the range's starting value.

END
specifies a character variable that gives the range's ending value.

As the requirements of the custom format to be created get more sophisticated you will need to have more information variables in the input control data set.

Upvotes: 2

Related Questions