Can You Use Limits Declared In PROC FORMAT?

Accepted Solution Solved
Reply
Contributor
Posts: 51
Accepted Solution

Can You Use Limits Declared In PROC FORMAT?

Can the Limits Declared In PROC FORMAT be used in some Values?

In the following piece of code Can I achieve the desired DataSet in the end?


Code:

PROC FORMAT;

value Buckets

low -< 1 = 'Bucket " & [Some Variable declaring the boundaries in this category]

1 -< 30 = 'Bucket " & [Some Variable declaring the boundaries in this category]

...

...

360 - High = 'Bucket 14' & [Some Variable declaring the boundaries in this category]

;

run;

PROC SQL;

CREATE TABLE Work.Temp3 as SELECT sum(AMT) AS POS, PRODUCT, put(DPD, buckets.) as Bucket from FAQ.Data_2007_01 GROUP by PRODUCT, DPD;

quit;

Desired Result:

POS     PRODUCT     Bucket

123     Product X     Bucket (From 0 Upto 1)

456     Product X     Bucket (From 1 Upto 30)

...

...

779     Product V     Bucket (From 300 Upto 330)

896     Product Y     Bucket (From 330 Upto 360)

...

etc.

Sure do appreciate your help!

Thanx again!


Accepted Solutions
Solution
‎09-14-2013 06:20 PM
Super Contributor
Posts: 644

Re: Can You Use Limits Declared In PROC FORMAT?

Apologies

At the moment I do not have access to a SAS installation for testing code, and as I usually use this method for exclusive ranges (which do not require these columns) I 'misremembered' the allowed values for SEXCL and EEXCL, which should be Y and N

Richard

Base SAS(R) 9.2 Procedures Guide

View solution in original post


All Replies
Super Contributor
Posts: 644

Re: Can You Use Limits Declared In PROC FORMAT?

If I understand your requirement you want the format label to include the limits.  If that is the case I suggest you create the lookup table in Excel (peace to you all Excel haters!) and import it into SAS.  You must use these column names:

STARTENDSEXCLEEXCLHLOFMTNAMETYPEBUCKETLABEL
LOW101LBUCKETN1Bucket 1 (LOW Up to 1)
13001BUCKETN2Bucket 2 (1 Up to 30)
306001BUCKETN3Bucket 3 (30 Up to 60)
609001BUCKETN4Bucket 4 (60 Up to 90)
9012001BUCKETN5Bucket 5 (90 Up to 120)
12015001BUCKETN6Bucket 6 (120 Up to 150)
15018001BUCKETN7Bucket 7 (150 Up to 180)
18021001BUCKETN8Bucket 8 (180 Up to 210)
21024001BUCKETN9Bucket 9 (210 Up to 240)
24027001BUCKETN10Bucket 10 (240 Up to 270)
27030001BUCKETN11Bucket 11 (270 Up to 300)
30033001BUCKETN12Bucket 12 (300 Up to 330)
33036001BUCKETN13Bucket 13 (330 Up to 360)
360HIGH00HBUCKETN14Bucket 14 (360 Up to HIGH)

START and END are the limits of the ranges

SEXCL and EEXCL specify whether or not the limits are excluded from the range

HLO identifies the labels to use for HIGH, LOW and OTHER values

FMTNAME is the name of the format and TYPE specifies character or numeric, format or informat

BUCKET will be ignored, it is just there to assist with creating the label

LABEL is the formatted display for a value in the range

I used the following Excel formula to create the label values:

="Bucket "&H3&" ("&A3&" Up to "&B3&")"

You can modify the first and last to suit.

Import into SAS as table Bucket_Format

Then

Proc Format Cntlin =  Bucket_Format ;

Quit ;

will create the format, this can be applied to the column you want to bucket:


In Proc Means include the statement


Format numeric_value BUCKET. ;


Richard


Message was edited by: Richard Carson

Contributor
Posts: 51

Re: Can You Use Limits Declared In PROC FORMAT?

Thanx Richard!

Sure did find your solution to be interesting! However, the piece of code presented ahead leads to the log returning:

ERROR: Type mismatch for variable SEXCL.

ERROR: Type mismatch for variable EEXCL.

ERROR: Terminating due to type mismatches.

...

ERROR: The format BUCKET was not found or could not be loaded.

The code being used as:

libname FAQ "E:\Risk\Risk_Analytics\SAS Prog New";

%LET YR = 2007;

%LET MTH = 01;

%LET PROD = PIL;

PROC IMPORT OUT= FAQ.Bucket_Format

            DATAFILE= "C:\Users\FARAZ.QURESHI\Desktop\CURRENT WORKINGS\Bucket_Format.csv"

            DBMS=CSV REPLACE;

     GETNAMES=YES;

     DATAROW=2;

RUN;

Proc Format Cntlin =  FAQ.Bucket_Format;

Quit;

PROC SQL;

CREATE TABLE Work.Temp1 as SELECT sum(POS) AS POS, PRODUCT, LOAN_STATUS, put(DPD, bucket.) as Bucket from FAQ.Data_&YR._&MTH. GROUP by PRODUCT, LOAN_STATUS, DPD;

Quit;

Solution
‎09-14-2013 06:20 PM
Super Contributor
Posts: 644

Re: Can You Use Limits Declared In PROC FORMAT?

Apologies

At the moment I do not have access to a SAS installation for testing code, and as I usually use this method for exclusive ranges (which do not require these columns) I 'misremembered' the allowed values for SEXCL and EEXCL, which should be Y and N

Richard

Base SAS(R) 9.2 Procedures Guide

Contributor
Posts: 51

Re: Can You Use Limits Declared In PROC FORMAT?

That's it buddy!
U did it Rich!

Thanx again for the superb solution!

Valued Guide
Posts: 3,208

Re: Can You Use Limits Declared In PROC FORMAT?

You can define an inputtable as richardinOz proposed by using SAS possible SAS-macros for the repeating values in the datastep.

Base SAS(R) 9.4 Procedures Guide ( proc format - example 7)

you could use a own fucntion while defnining a format - other example but also..

41917 - Date format to print date as the quarter of the year in the form of Q1, Q2, Q3, Q4

---->-- ja karman --<-----
Contributor
Posts: 51

Re: Can You Use Limits Declared In PROC FORMAT?

Wow Jaap!
XClent reference of source of information and sample!

Super User
Super User
Posts: 6,500

Re: Can You Use Limits Declared In PROC FORMAT?

If you want the limits displayed in the label then code the label that way when you define the format.

If you already have a format defined then you can output it to a dataset using CNTLOUT option of PROC FORMAT and use a data step to define a new (or modify the existing) format that you can create using the CNTLIN option.

Here is a simple example.  A more complete solution might check the SEXCL and EEXCL variables to add the '<' symbols and check if START=END to eliminate the hyphen.

PROC FORMAT;

value Buckets

   low -< 1   = 'Bucket 1'

     1 -< 30  = 'Bucket 2'

   360 - High = 'Bucket 14'

;

run;


proc format cntlout=formats ;

  select buckets;

run;


data new ;

  length label $200 ;

  set formats ;

  fmtname = 'C'||fmtname;

  label = catx(' ',label,cats('(',catx('-',start,end),')'));

run;


proc format cntlin=new;

run;


proc format fmtlib ;

  select buckets cbuckets;

run;


----------------------------------------------------------------------------

|       FORMAT NAME: BUCKETS  LENGTH:    9   NUMBER OF VALUES:    3        |

|   MIN LENGTH:   1  MAX LENGTH:  40  DEFAULT LENGTH   9  FUZZ: STD        |

|--------------------------------------------------------------------------|

|START           |END             |LABEL  (VER. V7|V8   14SEP2013:11:51:31)|

|----------------+----------------+----------------------------------------|

|LOW             |               1<Bucket 1                                |

|               1|              30<Bucket 2                                |

|             360|HIGH            |Bucket 14                               |

----------------------------------------------------------------------------

----------------------------------------------------------------------------

|       FORMAT NAME: CBUCKETS LENGTH:   20   NUMBER OF VALUES:    3        |

|   MIN LENGTH:   1  MAX LENGTH:  40  DEFAULT LENGTH   9  FUZZ: STD        |

|--------------------------------------------------------------------------|

|START           |END             |LABEL  (VER. V7|V8   14SEP2013:11:51:31)|

|----------------+----------------+----------------------------------------|

|LOW             |               1<Bucket 1 (LOW-1)                        |

|               1|              30<Bucket 2 (1-30)                         |

|             360|HIGH            |Bucket 14 (360-HIGH)                    |

----------------------------------------------------------------------------

Contributor
Posts: 51

Re: Can You Use Limits Declared In PROC FORMAT?

Fantastic suggestion Tom!
You guys sure have turned out 2 b ideal ones 4 help!
I really oblige your guidance!
Sure have led me to hate usual slow worksheet softwares!

Smiley Wink

Thanx again buddies!

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 455 views
  • 8 likes
  • 4 in conversation