BookmarkSubscribeRSS Feed
sasemp999
Calcite | Level 5

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)

2 REPLIES 2
Ksharp
Super User

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

Cynthia_sas
SAS Super FREQ

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

sas-innovate-2024.png

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.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 1090 views
  • 0 likes
  • 3 in conversation