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)
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
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
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;
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.