How do I generate multiple lines from PROC REPORT?

[deleted_user]

I have a data file with 45 columns of data. I have been assigend a project which requires all columns of data to be reported. How do I get my data to wrap, so that the first line shows 10 columns, the next line shows the next 10 columns and so on?

[Cynthia_sas]

Are you generating your output to ODS destinations (like RTF, PDF or HTML) or to the LISTING window? This makes a difference because for ODS HTML, for example, the table that shows your detail rows can be as wide as you want (because HTML has no horizontal limitations on how wide the table can be.)

Depending on exactly what you mean by "the first line shows 10 columns, the next line shows the next 10 columns", you might have to switch over to DATA _NULL_ and FILE PRINT for LISTING or some other technique for ODS.

A lot also depends on whether your variables are all character or all numeric. It is possible, with a custom table template to stack variables in columns, but the variables must be of the same type for this to work best.

Contacting Tech Support with this question might be your best bet for a response that fits your data and your output destination. To contact Tech Support, refer to: http://support.sas.com/techsup/contact/index.html

cynthia

[deleted_user]

Thanks for replying. I am new to the organization I workfor, and to SAS. It turns out that I am supposed to use my stored process from within Web Report Studio. That should be fun, since I've only had a 1-day class in Studio, plus I haven't had a test folder set up yet, nor do I have appropriate permissions!

Again, thanks for responding...it's appreciated.

[Cynthia_sas]

Hi! That's a tall order -- to learn SAS programming and then to generate a stored process for use with SAS Web Report Studio. But it IS do-able!

One thing you might try, is to investigate SAS Enterprise Guide tasks to generate output -- the nice thing about Enterprise Guide is that it acts as a code generator, while you use a GUI window to make your selections. So you do not have quite as steep a learning curve. The task that you are looking for is "List Data" inside EG.

You should not have to worry about the placement of the data on the line or wrapping when you generate output that will be viewed in SAS Web Report Studio, so making a Stored Process (SP) from your List Data task should be sufficient for your output, without worrying about how the "line" looks -- Web Report Studio takes the output from your stored process, renders it and displays it inside the Web Report Studio interface, so it's really best to just use the List Data task without worrying about wrapping, etc in the viewer.

EG will take your task and create an SP appropriate for Web Report Studio if you use the Stored Process Wizard by right mouse clicking on the task when it's the way you want it and then selecting "Create Stored Process."

SAS Tech Support will really be able to help you a lot once your administrators have finished setting up the permissions and the data access for you.

cynthia

[deleted_user]

And again, thanks for replying...I appreciate it! Sorry for the delay in replying back.

I already made my stored process, with input parameters and appropriate grouping, etc., which wasn't too difficult since I've had previous SQL / SQL Server experience, as well as having previous coding experience.

It's just the semantics of SAS that I am new to, which is killing me, plus the servers we have in house (both production and test) are currently giving us fits. It's like a day of up time followed by a day of down time. That's really frustrating!!

Anyway, once the servers appear to stabilize, I plan on giving your suggestions a try and see what develops. I just didn't think it would be that difficult to have 45 columns on a report, with say 10 columns on line one, then having the next 10 columns underneath that on line 2 and so on.

Thanks again for replying....have a good day!

[deleted_user]

Cynthia,

I just tried using List Data, but did not see an option for choosing Oracle database tables, which is what we use. Is there something I should check for or do in relation to using list data?

Thanks!

[Cynthia_sas]

Hi!
This is the point at which I really need to refer you to Tech Support because there's one way of accessing Oracle tables "outside" of the Enterprise Intelligence Platform (using the LIBNAME statement and options -- and SAS/Access for Oracle) and another way of accessing Oracle tables "inside" the Enterprise Intelligence platform (using the Metadata Library definitions and SAS/Access for Oracle) and setups in SAS Management Console and the metadata for your tables.

Once all your definitions and configurations are set up, either method should make the tables visible to you in EG; however, it would be irresponsible of me to just toss you a LIBNAME statement knowing that you are developing a stored process and must eventually go through the Metadata Server to get to the data. So, Tech Support really should be the ones to help you figure out how to do the setup, if you have the authority to define the tables in the SAS Management Console. OR, they can tell you how to access them once somebody else sets them up for you if you do NOT have the authority to set them up yourself.

Because you said that you wanted to surface your data in a particular way in Web Report Studio and because you said you were new to SAS, I did not go into a very detailed explanation about why I recommended EG or PROC PRINT (or PROC REPORT) for your report...but here goes:

1) Inside the context of the Enterprise Intelligence Platform, if you did a simple LIST DATA task on your data and turned that task into a stored process and then surfaced that stored process in Web Report Studio, you would see all the variables spread out and you would have to scroll right and left in the browser window to see from one side of the table to the other. (I tested a Stored Process created from a LIST DATA task, so the "vanilla" task is probably NOT what you want if you want to avoid scrolling.)

2) In the regular SAS LISTING window, there is a way, using FILE and PUT statments to write 2 output lines for every single observation. However, this technique is designed for the LISTING window and does not translate well from the LISTING window to the type of SAS Report XML that is generated for Web Report Studio. So that FILE PRINT technique is not really available to you as a stored process, although it would work very well for LISTING output.

3) The way that I would do this, if I had my data available would be to would be to make a unique ID number or row number for every observation. Then, I would "split" my data, so where the original data file might have had just var1-var40; the new dataset has 2 rows for every row in the original file: [pre]
cntr var1 var2 var3 var4 ...var20
1 0101 0202 0303 0404 ...2020
2 2121 2222 2323 2424 ...4040
[/pre]
Once I had my data structured this way -- a fairly simple transformation program -- THEN I would do a Proc Print (List Data task) with grouping by the CNTR variable or I would do Proc Report (in a code node) with grouping by the CNTR variable. I can assign labels to my variables, so that the label for VAR1 reads as "Var1 and Var 21" for the 1st column, "Var 2 and Var 22" for the 2nd column and so forth.

3) Next, I would turn that task into a Stored Process for surfacing in Web Report Studio. Depending on whether I had started with a task or a code node and whether I have access to SAS Management Console, I have 2 choices for how to create my stored process. However, in order to execute in Web Report Studio, the stored process must be written in a certain folder in the Metadata and so you must make sure that you have been given access to that folder or else you will not be able to save your stored process where it needs to be saved. Until this step is done, you can still test your stored process inside EG.

4) Once steps 1,2, and 3 are done, you could test your stored process in Web Report Studio.

Each one of these steps, taken alone, requires a more lengthy explanation. Step 1 & 3 might require the intervention of an administrative person at your site to make the data accessible to you or the repository folder accessible to you. Each step could potentially have an effect on the next step and how you accomplish what you want to do. Tech Support can guide you through the complete process, which is far better than getting piecemeal and/or incomplete answers through the forum -- because you ask me about connecting to Oracle data and my answer really is -- it depends. And we could go back and forth in numerous postings without a definitive answer and in the end, I'd still tell you that you needed to contact Tech Support.

To learn how to contact Tech Support, refer to: http://support.sas.com/techsup/contact/index.html

Good luck!
cynthia
p.s. Once you have everything set up, stored processes are way cool! If you read all the way down here and if you would like to see an example of a program that splits one observation of 40 variables into two observations of 20 variables, then send an email to me at: cynthia.zender@sas.com and I will send you the code sample.

[Petersi]

Hi Cynthia:

Actually, I have a similar question, and even though HTML will allow very wide reports, I need to wrap or stack columns so that the total width of the report does not extend beyond the width of the screen. I would also like to control where the wrap point would be. I have tried this using multiple COLUMN statements, but that doesn't seem to work. Do you have any suggestions?

Thanks,

Peter.

[Cynthia_sas]

Hi:
I would manually "split" the data before PROC REPORT got it -- in a DATA step program. The way that I would do this, to make a unique ID number or row number for every observation. Then, I would "split" my data, so where the original data file might have had just var1-var40; the new dataset has 2 rows for every row in the original file: [pre]
obs cntr var1 var2 var3 var4 ...var20
1 1 0101 0202 0303 0404 ...2020
1 2 2121 2222 2323 2424 ...4040
[/pre]

Once I had my data structured this way -- a fairly simple transformation program -- THEN I would do a Proc Print (List Data task) with grouping by the OBS and CNTR variable or I would do Proc Report (in a code node) with grouping by the OBS and CNTR variable. I can assign labels to my variables, so that the label for VAR1 reads as "Var1 and Var 21" for the 1st column, "Var 2 and Var 22" for the 2nd column and so forth.

The issue is that if you have ALL character data or ALL numeric, this is a fairly simple program. Where it gets complicated is when you have mixed character and numeric data. The data in each column has to be of the same type, so you'd need a LOT of PUT statements to format each column appropriately.

cynthia

[Petersi]

Thanks Cynthia:
Alternatively, can PROC REPORT perform a transpose of the output? I only have 3 rows but many columns, and the end user wants the rows to appear as columns. In order to do this, I am creating an output dataset from PROC REPORT, transposing that with PROC TRANSPOSE and then running a second PROC REPORT to display the summarized data as requested. This is a quick solution, but it seems like there must be a simpler way.

Peter.

[deleted_user]

Wow!! Quite lengthy, but quite good! It'll take me a few minutes to digest, but that'll be good for me....more for me to digest! And, I'll e-mail you also.

Thank you!