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
I want the output as shown below please let me know how can I get this using List data.
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
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.
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.