Help using Base SAS procedures

REG:PROC REPORT AND ODS STATEMENT

Reply
Occasional Contributor
Posts: 14

REG:PROC REPORT AND ODS STATEMENT

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)

Super User
Posts: 9,687

REG:PROC REPORT AND ODS STATEMENT

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

SAS Super FREQ
Posts: 8,743

Re: REG:PROC REPORT AND ODS STATEMENT

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;


no_of_visits.jpg
Ask a Question
Discussion stats
  • 2 replies
  • 525 views
  • 0 likes
  • 3 in conversation