topic Re: List Data problem...Cant understand this task in SAS Enterprise Guide

List Data problem...Cant understand this task

SanjayM — Mon, 27 Aug 2007 11:37:30 GMT

Hello

I just can’t understand List Data task in EG. Its not very clear about when to use Group, Totals, Sub totals, Identifying Labels etc.

I have the following columns in my data set
Supplier Code
Supplier Name
Company Code
Company Name
Invoice Type
Invoice Number
Invoice Quantity
Invoice Value

I want the output as shown below please let me know how can I get this using List data.

Report title

SUPPIER: A1234 XYZ Supplier
Company Code Comp Name Invoice Type Invoice Number Invoice Qty Invoice Value
A0001xxxxxxxxCompany abc E INV001 10 1000
xxxxxxxxxxxxxxxxxxxxxxxxxxxINV002 20 2000
xxxxxxxxxxxxxxxxxxxxxxxxxxxINV003 30 3000
xxxxxxxxxxxxxxxxxxxxxxxxxxxx-------------- -----------------
A0001 Company abcxxxxxxxxxxxxxx60 6000

A0002xxxxxxxxCompany pqr E INV003 30 3000
xxxxxxxxxxxxxxxxxxxxxxxxxxxINV004 40 4000
xxxxxxxxxxxxxxxxxxxxxxxxxxxINV005 50 5000
xxxxxxxxxxxxxxxxxxxxxxxxxxxx-------------- -----------------
A0002 Company pqrxxxxxxxxxxxxxx210 21000

Total for SUPPIER: A1234 XYZ Supplier 270 27000

Please ignore xxxxxx consider them as blank Message was edited by: Grasshopper in SAS

Re: List Data problem...Cant understand this task

Cynthia_sas — Mon, 27 Aug 2007 16:09:03 GMT

Hi, Grasshopper!
When you are using the List Data task, if you place your cursor on a choice like "Subtotal of", if you look below the selection pane, in a gray rectangle, you should see this text:

Prints a subtotal whenever the value of the specified variable changes or when the next BY group begins. The variable that you assign to this role must also be a variable in the Group analysis by role. You can assign a maximum of one variable to this role.

You can do this for every role to see what each one does. Otherwise, you can also look up the documentation for PROC PRINT in the SAS on-line documentation, since the LIST Data task is generating PROC PRINT code behind the scenes.

So, for your output, I would suggest that you
1) put Company Code (or Company Name) in the Subtotal Of role. (You should notice that when you put Company Code in the Subtotal Of role, then that choice makes it also appear in the Group By role.)
2) put Supplier Code (or Supplier Name) in the Group By role.
3) put Invoice Quantity and Invoice Value in the Total Of role
4) put the rest of the variables you want to see in the List Variables role (put them in the order you want to see them on the screen)

I would not worry about Identifying Label role right now until after you see whether these role choices will give you the kind of output you want. I suspect, that if you want to customize the break lines for subtotals and totals, you might want to consider moving to the use of PROC REPORT, which allows you to put customized text on the break lines and generally gives you greater control on which variables to use at the break.

Anyway, get your List Data task working first -- then you can play with Identifying Label. Also, if you put Supplier Code and Supplier Name in the Group By Role, then you will get one summary line for Supplier Code and a second summary line for Supplier Name. This is probably NOT what you want.

For more help with the List Data task or with using PROC REPORT as an alternative, you might consider contacting Tech Support.

cynthia

Re: List Data problem...Cant understand this task

SanjayM — Mon, 27 Aug 2007 18:29:02 GMT

Thanks Cynth

I did few more things

Concatenated the Supplier Code and Supplier Name and made it one column. This is because I want the Supplier Code and Supplier Name to appear together. Otherwise it would only appear after Company. Company is already concatenated with code. Hence not a problem.

So now, Group contains
Supplier and Company

Subtotal of Company

Total of Invoice Quantity and Invoice Value

Its works fine, thanks.

I have also tried Identifying Labels. It supresses repeating group values if I am not wrong.

Re: List Data problem...Cant understand this task

Cynthia_sas — Mon, 27 Aug 2007 19:03:31 GMT

Glad you got it working! That is generally what folks do to get around the multiple BY variable issue (as with Supplier Code and Supplier Name). Good job figuring that out!

Identifying Labels does what you noted. It also has the extra side effect of suppressing the BY line (because the by line info is in the table).

cynthia