topic REG:PROC REPORT AND ODS STATEMENT in SAS Procedures

REG:PROC REPORT AND ODS STATEMENT

sasemp999 — Mon, 05 Mar 2012 05:08:14 GMT

Hi All,

please help me out for this problem.

input dataset

id name no_of_times_repeted
1 a 3
1 b 1
1 c 1
2 h 1
3 f 1
4 a 1
4 a 1

i am exporting this output to excel file by using ods statement.and i have used proc report also.

i want to get the output in excel like

1 a 3
1 b 1
1 c 1
-----------
no of visits -5(sum of no_of_times_repeted)
-----------
2 h 1
---------
no of visits -1(sum of no_of_times_repeted)
---------
3 f 1
---------
no of visits -1(sum of no_of_times_repeted)
---------
4 a 1
4 a 1
--------
no of visits -2(sum of no_of_times_repeted)
--------

Note: i have to get output by using proc report and ods statement.
IN LINE Statement i want to get no of visits -2(sum of no_of_times_repeted)

REG:PROC REPORT AND ODS STATEMENT

Ksharp — Mon, 05 Mar 2012 10:28:09 GMT

How about:

data x;
input id name $ no_of_times_repeted ;
cards;
1 a 3
1 b 1
1 c 1
2 h 1
3 f 1
4 a 1
4 a 1
;
run;
ods tagsets.excelxp file='c:\x.xls' style=sasweb;
proc report data=x nowd;
column id name  no_of_times_repeted ;
define id/order;
compute before id;
 sum=no_of_times_repeted.sum;
endcomp;
compute after id;
line '------------------';
line 'sum:' sum best8.;
line '-----------------';
endcomp;
run;
ods tagsets.excelxp close;

Ksharp

Re: REG:PROC REPORT AND ODS STATEMENT

Cynthia_sas — Mon, 05 Mar 2012 20:02:48 GMT

Hi:

Ksharp has shown you one approach with the LINE statement. Depending on your ODS DESTINATION (ODS CSV, ODS HTML or ODS TAGSETS.EXCELXP), you may or may not like the look of the dashes when your output file from ODS is rendered in Excel. I don't understand why you even need the LINE statement, when the BREAK AFTER capability combined with the COMPUTE statement will give you what you describe.

I wonder whether you are also asking for the value of ID and the value of NAME to be displayed on each report row. If you have a usage of ORDER for ID, then the ID will only appear on the first row for the group.

Alternate code is shown below, which produced the attached output (see screenshot).

cynthia

data example;

length id $20 name $20;

infile datalines;

input id $ name $ no_of_times_repeated ;

** make a copy of ID for ordering;

order_id = id;

id = right(id);

return;

datalines;

1 a 3

1 b 1

1 c 1

2 h 1

3 f 1

4 a 1

;

run;

ods csv file='c:\temp\report_comma_dlm.csv';

ods msoffice2k file='c:\temp\report_html.xls' style=sasweb;

ods tagsets.excelxp file='c:\temp\report.xml' style=sasweb;

proc report data=example nowd;

column order_id id name no_of_times_repeated ;

define order_id / order noprint;

define id/display;

define name / display;

define no_of_times_repeated / sum;

break after order_id / summarize;

compute id;

if upcase(_break_) = 'ORDER_ID' then

id='No of Visits';

endcomp;

compute after order_id;

line ' ';

endcomp;

run;

ods _all_ close;