Correct way to Use PRELOADFMT

Reply
Contributor
Posts: 51

Correct way to Use PRELOADFMT

Sure have found the PRELOADFMT to be a useful mode of displaying all the data criteria even if no data does exist in a class, however, unfortunately found the same to be limited to some specific PROCs like Summary, Report etc.

While I am using the following piece of PROC FORMAT + PROC SQL can't find a successful way to display all the 14 Buckets for all the 10 Products, in other words a dataset of 140 Rows presenting Sums of POS while representing 0 wherever a null (.) is found. Please see if any of you experts could help in modifying the following code to cater the said need:

LIBNAME FAQ 'E:\Risk\Risk_Analytics\SAS Prog New';

%LET YR = 2007;

%LET MTH = 01;

PROC FORMAT;

value Buckets

low -< 1 = 'Bucket 1'

1 -< 30 = 'Bucket 2'

30 -< 60 = 'Bucket 3'

60 -< 90 = 'Bucket 4'

90 -< 120 = 'Bucket 5'

120 -< 150 = 'Bucket 6'

150 -< 180 = 'Bucket 7'

180 -< 210 = 'Bucket 8'

210 -< 240 = 'Bucket 9'

240 -< 270 = 'Bucket 10'

270 -< 300 = 'Bucket 11'

300 -< 330 = 'Bucket 12'

330 -< 360 = 'Bucket 13'

360 - High = 'Bucket 14'

;

run;

PROC SQL;

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

quit;

Thanx in advance.

Super Contributor
Posts: 644

Re: Correct way to Use PRELOADFMT

Posted in reply to FarazA_Qureshi

Since your count is in effect a 3 way table you could use Proc Freq with the List and Sparse options, though even then a bucket that is not represented anywhere in your data will probably not be listed.

Proc SQL cannot count values that do not exist in the data.  All you can do is to left join the counts back to a list of all possible label values.  To get the list you can use proc format with the cntlout= option (it can be the same proc format that creates the format):

PROC FORMAT cntlout = allbuckets (keep = label) ;

value Buckets

... etc

quit ;

Proc SQL ;

CREATE TABLE Work.Temp1 as ...

;

Create table want as

     Select list.label as DPDBucket

          ,     t1.count

     From allbuckets list

           left join

               temp1 t1

          on t1.DPDBucket = list.label

               ;

Quit ;

It is of course possible to combine the first query as a subquery in the join but I kept the steps separate for clarity.

Richard

Occasional Contributor
Posts: 14

Re: Correct way to Use PRELOADFMT

Posted in reply to RichardinOz

Great solution Richard!  Would you mind providing your contact information so that I may give you credit for this solution when I reference it is a paper for WUSS 2014?

Cheers,

Mary F. O. Rosenbloom

http://www.sascommunity.org/wiki/UserSmiley Surprisedtterm1

Super Contributor
Posts: 644

Re: Correct way to Use PRELOADFMT

Mary

You are welcome to use this solution in your paper, and please send me a copy.

But note that I wrote the solution to use an existing format.  If I were developing from scratch I would create the format control table and use it to derive the format as well as make the SQL join:

data allbuckets ;

  retain start 'LOW ' end '   1' ;

  length label $12 ;

  retain type 'N' fmtname 'Buckets' hlo 'L' eexl 'Y' ;

  drop k;

  Do k = 1 to 14 ;

       label = Cat('Bucket ', put(k, 2.)) ;

       output ;

       hlo = ' ' ;

       start = end ;

       end = put(k * 30, 4.) ;

       if k > 12 then

            do ;

                 end = 'HIGH' ;

                 hlo = 'H' ;

                 eexl = 'N' ;

            end ;

  end ;

run ;

proc format cntlin = allbuckets ; run ;

Richard C Carson

Senior SAS Designer

Melbourne, Australia

Ask a Question
Discussion stats
  • 3 replies
  • 338 views
  • 0 likes
  • 3 in conversation