Creating an ordered format through cntlin

[sasiscool]

I have a data set of counts of products that I set up in SAS.

label         start           fmtname       etc.   freq.

apple       apple           $prodfmt              40

cherry      cherry         $prodfmt               37

pen           pen          $prodfmt                32

I have the type set to "C" as well.  However, I want to keep the products to keep this descending order.  I am building a chart of products by year and want the products to keep the same order of appearance. However, when I apply this format I have created to a proc report, (applied to the product variable) it doesn't maintain the descending order. I am aware of assigning a value for HLO field, but  I haven't figured out how to use it yet.

So, in similar fashion to how one can make a notsorted proc format, how can I do the same when creating a format from a sas data set using cntlin?

THANK YOUUUUUUU!!!!!!!!

[Reeza]

You can include both variables, the formatted/unformatted and specify the unformatted as ORDER to force the correct order. You can use NODISPLAY to ensure its not shown on the report. 

But, if you're applying a format, it should sort using the underlying data, not the formatted value. 

[sasiscool]

I am already aware of those tricks, but it still doesn't work. Another question:

if the table is product by year and the table looks like

                   Year 1     Year2        Year3

Apple             90          45           80

cherry           90            44           82

cake              78            60           75

pen               12             50           12

If each of the years have a different descending order, since some products sell at different rates per year, and I want to force the row order to appear according to the descending order of the last year, how do I dot that with my sas dataset format

[Reeza]

AFAIK there isn't a built in mechanism within PROC REPORT that will do this. 

Especially if you're using YEAR as an ACROSS variable. 

Instead you can create a data set in the order you want and then use PROC REPORT to display it as desired.

[sasiscool]

So i can't just create a dataset with the most recent year descending, save it as a format with cntlin. And apply that to the proc report?

i have to create a dataset with each year? and don't create a format?

thank you for responding

[Reeza]

---

@sasiscool wrote:

So i can't just create a dataset with the most recent year descending, save it as a format with cntlin. And apply that to the proc report?

---

You can do this, but that wasn't clear to me when I first read your question. 

You would need to add in an order though, ie Apple = 1, Cherry = 2. Your current example has start=label which doesn't really create a format you want. So, you could create that and apply it to your data using PROC REPORT.

Here's an idea - untested:

data myformat;

set have;

label=put(_n_, z2.);;
start=start;
type = 'C';
fmtname = 'myFmt.';''

run;

proc format cntlin=myFormat;
run;

proc report data=myReportData nowd;
column ...... ;

define fruit  / f=$myFmt. order=formatted

run;;

[ballardw]

Time to show 1) some data and 2) the result for that data.

---

@sasiscool wrote:

So i can't just create a dataset with the most recent year descending, save it as a format with cntlin. And apply that to the proc report?

i have to create a dataset with each year? and don't create a format?

thank you for responding

---

It sounds like you are attempting to have a different sort order for a second variable for every year. If that is the case then you likely can't use Year as an across variable. If you absolutely must have a different order for every year than you really need to show what you expect that result to look like as you obviously cannot have a single row label involved.

For your first question about maintaining order you might try using the PRELOADFMT option in define block for the variable involved, which will require the use of the Order=data or Exclusive option. The format may require building with the NOTSORTED option. You also should show your proc report code as you may have been setting some option(s) that affect order of appearance that you haven't mentioned in this thread yet.