topic Re: How to insert a total row at the end of data, but keep dates in SAS Enterprise Guide

How to insert a total row at the end of data, but keep dates

AustinHarris — Tue, 10 Jan 2023 20:33:32 GMT

Hello all I have used to following code to add a row at the end of my data that totals the columns of data

/*creating total row at bottom of data*/
proc means noprint data=c_&i.;

output out=summary_&i. sum=;

run;

data want_&i.;

set c_&i. summary_&i. (in=in2);

if in2 then program_description='Overall';

run;

The following article/question is where i found this solution: https://communities.sas.com/t5/SAS-Programming/Insert-a-Row-at-the-end-of-the-data-with-totals/td-p/136539

The only issue I am having is that one of my columns is a date (in date 8 format) and i want to maintain this same date in the total "Overall" row. currently it is totaling the dates and providing that value. is there a way to do this? if so what would be the most efficient way?

Re: How to insert a total row at the end of data, but keep dates

PaigeMiller — Tue, 10 Jan 2023 21:02:52 GMT

The only issue I am having is that one of my columns is a date (in date 8 format) and i want to maintain this same date in the total "Overall" row.

I don't really know what "maintain" means in this sentence.

Re: How to insert a total row at the end of data, but keep dates

AustinHarris — Tue, 10 Jan 2023 21:07:43 GMT

for instance, in the picture below, I want the date to remain 01/03/202. As opposed to being a sum of the dates.

Re: How to insert a total row at the end of data, but keep dates

yabwon — Tue, 10 Jan 2023 21:15:53 GMT

Like this?

data have;
input a b ;
date = today() - _N_;
format date yymmdd10.;
cards;
1 4
1 5
1 6
2 5
2 9
2 10
;
run;

proc print data=have;
run;

proc means noprint data=have;
  output out=summary sum(a b)= max(date)=;
run;

data want;
set have summary (in=in2);
if in2 then program_description='Overall';
run;


proc print data=want;
run;

Bart

Re: How to insert a total row at the end of data, but keep dates

PaigeMiller — Tue, 10 Jan 2023 21:18:56 GMT

proc means noprint data=c_&i.;
    output out=summary_&i. sum= max(scoring_sample_date)=max_scoring_sample_date;
run;

data want_&i.;
    set c_&i. summary_&i. (in=in2);
    if in2 then do;
        program_description='Overall';
        scoring_sample_date=max_scoring_sample_date
    end;
    drop max_scoring_sample_date;
run;

Re: How to insert a total row at the end of data, but keep dates

AustinHarris — Fri, 13 Jan 2023 19:46:32 GMT

I am receiving a syntax error.

Re: How to insert a total row at the end of data, but keep dates

SASKiwi — Fri, 13 Jan 2023 21:22:08 GMT

Posting partial log screenshots isn't helpful as we can't see what has happened before. Please post your complete log using copy and paste and the </> icon, not a screenshot.

Re: How to insert a total row at the end of data, but keep dates

Reeza — Fri, 13 Jan 2023 21:33:24 GMT

It's missing a semicolon before the END statement.

Re: How to insert a total row at the end of data, but keep dates

Kurt_Bremser — Fri, 13 Jan 2023 21:33:28 GMT

Appending a "total" row to a dataset makes no sense. The dataset will become mostly unusable for further analysis, and reporting procedures can create such rows anyway.

Please post usable example data (in a data step with datalines), and the report you want to create from it.

Re: How to insert a total row at the end of data, but keep dates

Patrick — Sat, 14 Jan 2023 02:57:58 GMT

@AustinHarris Short answer is: DON'T!

It's a bad idea to add total rows to data (tables). That's what you're doing in reports and many of the SAS procedures creating such reports allow for creation of totals (=collapsing categorial data). Same is true for any VA reporting. You want all your source data in your table to be on the same detail level as else any coding will become harder.