csv files handling in sas

[jdk123]

here is my csv file which is expected final results:
====start the file====
case 1
the forecasted amounts

type 2001 2002 2003 2004 2005 2006 01/06 02/06 03/06 04/06

a 200 300 400 500 600 7000 1500 1200 1300 1400
b 23 50 45 56 67 67 56 67 67 56

case2
the forecasted amounts

type 2001 2002 2003 2004 2005 2006 01/06 02/06 03/06 04/06

a 200 300 400 500 600 7000 1500 1200 1300 1400
b 23 50 45 56 67 67 56 67 67 56

===end of the file

I have a input csv file to generate the above results:

Question:

1. is possible for sas to generate the above results;
2. how sas can handle the heading in the final results: such as case1 case2, the year. as far as i know the sas dataset can not have the Hierarchy columns?

please advise.

Thanks in advance.

[Cynthia_sas]

Hi:
I'm confused. First you said that "here is my csv file which is expected final results", however what you showed did not have an comma separated values (csv) -- so I'm wondering whether you showed an INPUT file or an OUTPUT report. Then, later you said that you have "a input csv file to generate the above results" -- so it's not clear to me what you were showing.

It almost sound to me as though you want your final report results to be in CSV form??? Or maybe you just want to open the results of some SAS process or procedure in Excel.

You asked whether SAS could generate the above results -- but did not say whether SAS was merely going to read a file and write a file or whether SAS was going to read a CSV file into a SAS dataset, perform some analysis (perhaps using some statistical procedure??) and then create a report.

At any rate, the answer is 'it depends'. The program below uses a SAS dataset called SASHELP.PRICEDATA -- since I didn't know how you were going to generate your case 1 and case 2 "forecast" information, I used Region for the Case 1 example and then I used Product Line for the Case 2 example. For both reports, I just show the sum of PRICE (the Unit Price), because it wasn't really explained how you were going to get forecast amounts.I used the ODS CSVALL destination to create CSV report results from PROC TABULATE (just for example purposes) and then used the SAS TITLE statement to put the Case 1 and Case 2 titles into the output. As a comparison, I use the ODS MSOFFICE2K destination to create an HTML file (Microsoft "flavor" of HTML file that Excel can open and render into a worksheet). This will allow you to see 2 different ways of creating results -- either as CSV or as HTML -- and Excel will open either file.

But now you must figure out what kind of analysis needs to be done and what SAS procedure you will use to generate your final report. And, of course, you will have to figure out how to read your "input csv" file into a SAS dataset in order to perform your analysis (generally folks use PROC IMPORT or a DATA step program to read a CSV file into SAS format).

If you were using a different procedure or had different data (such as time series or clinical study data), then your procedure of choice might not be PROC TABULATE. In fact, if you are doing your forecasts with SAS, then I am fairly certain that you will not be using PROC TABULATE.

cynthia
[pre]
ods listing close;

title;
ods msoffice2k file='c:\temp\case1_case2_mso.xls' style=sasweb;
ods csvall file='c:\temp\case1_case2.csv';

title 'case 1';
title2 'The amounts (the sum of PRICE)';

proc tabulate data=sashelp.pricedata;
class date region;
var price;
table region=' ',
date=' '*price=' '*sum=' '
/box=region;
format date year4.;
run;

title 'case 2';
title2 'The amounts (the sum of PRICE)';

proc tabulate data=sashelp.pricedata;
class date productline;
var price;
table productline=' ',
date=' '*price=' '*sum=' '
/ box = productline;
format date year4.;
run;

ods _all_ close;
[/pre]

[jdk123]

Thanks Cynthia, and I apologized for my confusing you.

if you could would you apply the proc report in your post. and repost your result here. That will be perfect. I am new to this area.

Thanks so lot

[Cynthia_sas]

Hi:
I'm not sure what you mean by "apply the proc report" in my post. The "Case 1" and "Case 2" titles are SAS TITLE statements and would work whether you used PROC TABULATE, PROC REPORT, PROC GLM, PROC REG or PROC WHATEVER.

Can you explain a bit more about your data and how you plan to generate the results and why you think you need PROC REPORT for your report output??

cynthia

[jdk123]

creating the file now update

Message was edited by: jdk123

[jdk123]

input file from excel looks like the following:

case1 Jan-06 Feb-06 Mar-06 Apr-06
item1 £23 £78 £78 £88
item2 £21 £76 £77 £888
item3 £67 £66 £88 £88

case2 Jan-06 Feb-06 Mar-06 Apr-06
item4 56 55 5 5
item5 34 55 5 5
item6 45 5 5 5


questions:

1. what is a best way to read in the file to a dataset? it is a good practice to read in the titles for both case 1 and case2?
2. how can i add in two additional columns for both case 1 and case2 and reproduce the output by using the same format as above; wih the final report how to have the user to view the formulas set in the original spreadsheet file?

3. is possible to add additional columns with group values to the output file.
for instance:

additional columns name: myValue
12345 34567 1%
thanks for your time. Message was edited by: jdk123

[Cynthia_sas]

HI:
First -- are you doing this in a Stored Process or using the SAS Add-in for Microsoft Office??? If you are NOT doing this in the context of a Stored Process then you should move or repost a new version of this question probably to the ODS and Base SAS Reporting forum (assuming you want to generate a report that you can open in Excel). If you are using the SAS Add-in for Microsoft Office and stored processes, then I would really suggest that you move your question to Tech Support.

There seems to be some discrepancies between your original post and this post. In your original post, you said you had a CSV file. Now, in this post, you say you have an Excel file. Does this mean that you have 1 workbook with 2 worksheets (case1 and case2)?? Or do you have 2 separate workbooks -- a case1 workbook or a case 2 workbook??? An "Excel file" is not the same as a CSV file -- unless you are saving the Excel file as a CSV file and you are showing your "version" of what's in the Excel file. If you indeed have a CSV file, I would prefer to see that posted. As you will see, the answer to your questions really depend on accuracy of information about the input file and the type of output you want to create.

Your questions:
1. what is a best way to read in the file to a dataset? it is a good practice to read in the titles for both case 1 and case2?
-- If you have a CSV file, you can read the CSV file with either a DATA step program or with PROC IMPORT
--If you have an Excel workbook, you can read the Excel workbook with either PROC IMPORT or the SAS Excel LIBNAME engine (or the ODBC engine or the OLE-DB engine)
SAS expects your input data to be rectangular in structure -- rows and columns with no embedded graphs, for example. By default, if your Excel sheet has titles or formulas, SAS will not retain those in the SAS dataset.

2. how can i add in two additional columns for both case 1 and case2 and reproduce the output by using the same format as above; wih the final report how to have the user to view the formulas set in the original spreadsheet file?
--As I explained above, once you read the data into a SAS dataset, there is no visibility of the formulas from the original workbook/worksheet. SAS could create the 2 additional columns you want in a variety of different ways. Once you have your data in a SAS dataset, you could use PROC SQL or a DATA step program to add the new columns to the data. If you want to add columns onto a report, then the best way to do that is with PROC REPORT.

3. is possible to add additional columns with group values to the output file.
--I have a hard time understanding what you mean by addition COLUMNS, since what you used as an example looks like 3 columns under one header called 'MyValue'. I suspect that this issue is the least of your worries -- if you were generating a report, as I said, there are ways to use a COMPUTE block with PROC REPORT to generate extra columns and ways with PROC REPORT to generate extra summary rows.

But until you resolve #1 and clarify what your input data is and how you will get it into SAS; and resolve #2 - -the issue with somehow retaining the Excel formulas -- #3 is almost irrelevant until #1 and #2 are solved.

cynthia