topic Re: Creating an ordered format through cntlin in ODS and Base Reporting

Creating an ordered format through cntlin

sasiscool — Wed, 01 Nov 2017 17:43:18 GMT

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!!!!!!!!

Re: Creating an ordered format through cntlin

Reeza — Wed, 01 Nov 2017 18:01:12 GMT

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.

Re: Creating an ordered format through cntlin

sasiscool — Wed, 01 Nov 2017 19:12:45 GMT

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

Re: Creating an ordered format through cntlin

Reeza — Wed, 01 Nov 2017 19:14:42 GMT

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.

Re: Creating an ordered format through cntlin

sasiscool — Wed, 01 Nov 2017 19:17:19 GMT

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

Re: Creating an ordered format through cntlin

Reeza — Wed, 01 Nov 2017 19:23:57 GMT

@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;;

Re: Creating an ordered format through cntlin

ballardw — Wed, 01 Nov 2017 19:26:58 GMT

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.