topic Re: Summing values in a column dependent on dates and conditions in other columns in SAS Enterprise Guide

Summing values in a column dependent on dates and conditions in other columns

BarneyC77 — Mon, 07 Jan 2019 16:10:50 GMT

Hi,

I am new to SAS EG, and think in Excel terms. I have three columns: (1) a reference; (2) a score; and (3) a date. The task I am trying to perform is equivalent to a sumifs function in excel.

I want to sum the total score of all entries in the score column for each reference, whose dates fall within defined ranges. I intend to create columns for each month so that I know what the total scores are for each reference for each month. Can I build a query and assign conditional variables, including specifying date periods?

Ie I have list of thousands of scores with a reference and a date, and I want to know the total score of all entries for each reference that falls within each particular month.

Help needed!

Thanks in advance all.

Best,

Barney

Re: Summing values in a column dependent on dates and conditions in other columns

PeterClemmensen — Mon, 07 Jan 2019 16:18:01 GMT

Can you post an example of what your data looks like and your desired result?

Re: Summing values in a column dependent on dates and conditions in other columns

BarneyC77 — Mon, 07 Jan 2019 16:50:15 GMT

Re: Summing values in a column dependent on dates and conditions in other columns

BarneyC77 — Mon, 07 Jan 2019 16:52:32 GMT

Hi,

See attached. I've included an 'as is' and a 'to be' in separate tabs

Thanks,

Barney

Re: Summing values in a column dependent on dates and conditions in other columns

Reeza — Mon, 07 Jan 2019 16:57:45 GMT

That's a TRANSPOSE task as shown, you aren't summarizing information in any manner. Use the GUI and check the code and then you can use that in your program.

Re: Summing values in a column dependent on dates and conditions in other columns

BarneyC77 — Mon, 07 Jan 2019 17:07:41 GMT

I'm summing the data as well - there are more than one instances of scores for each reference. It's not just s straight transpose

Re: Summing values in a column dependent on dates and conditions in other columns

Reeza — Mon, 07 Jan 2019 17:13:07 GMT

Then it's a proc means and a transpose.
Untested, if you post data as a data step as previously indicated it's easier to work with.

proc means data=have;
class reference date;
format date monyy7.;
var lifetimecostScore;
output out = totals sum=MonthlyScore;
run;

proc transpose data=totals out=want prefix=Date_;
by reference;
id date;
idlabel date;
var MonthlyScore;
run;

For a GUI it would be a summarize and transpose task or you could do it in a single step with a report builder, but I don't know if that would give you a report or table.

Re: Summing values in a column dependent on dates and conditions in other columns

novinosrin — Mon, 07 Jan 2019 17:33:27 GMT


data have;
input Reference :$20.	LifetimeCostScore :comma10.	Date :mmddyy10.;
format date mmddyy10. 	LifetimeCostScore comma10.;
cards;
Reference1	17,235	12/2/2018
Reference2	17,435	12/2/2018
Reference3	17,635	12/2/2018
Reference4	17,835	12/2/2018
Reference5	18,035	12/2/2018
Reference6	18,235	12/2/2018
Reference7	18,435	12/2/2018
Reference8	18,635	12/2/2018
Reference9	18,835	12/2/2018
Reference10	19,035	12/2/2018
Reference11	19,235	12/2/2018
Reference12	19,435	12/2/2018
Reference13	19,635	12/2/2018
Reference14	19,835	12/2/2018
Reference15	20,035	12/2/2018
Reference16	20,235	12/2/2018
Reference17	20,435	12/2/2018
Reference1	20,635	1/3/2019
Reference2	20,835	1/3/2019
Reference3	21,035	1/3/2019
Reference4	21,235	1/3/2019
Reference5	21,435	1/3/2019
Reference6	21,635	1/3/2019
Reference7	21,835	1/3/2019
Reference8	22,035	1/3/2019
Reference9	22,235	1/3/2019
Reference10	22,435	1/3/2019
Reference11	22,635	1/3/2019
Reference12	22,835	1/3/2019
Reference13	23,035	1/3/2019
Reference14	23,235	1/3/2019
Reference15	23,435	1/3/2019
Reference16	23,635	1/3/2019
Reference17	23,835	1/3/2019
Reference1	12,657	2/4/2019
Reference2	12,857	2/4/2019
Reference3	13,057	2/4/2019
Reference4	13,257	2/4/2019
Reference5	13,457	2/4/2019
Reference6	13,657	2/4/2019
Reference7	13,857	2/4/2019
Reference8	14,057	2/4/2019
Reference9	14,257	2/4/2019
Reference10	14,457	2/4/2019
Reference11	14,657	2/4/2019
Reference12	14,857	2/4/2019
Reference13	15,057	2/4/2019
Reference14	15,257	2/4/2019
Reference15	15,457	2/4/2019
Reference16	15,657	2/4/2019
Reference17	15,857	2/4/2019
Reference1	16,057	3/5/2019
Reference2	13,456	3/5/2019
Reference3	13,656	3/5/2019
Reference4	13,856	3/5/2019
Reference5	14,056	3/5/2019
Reference6	14,256	3/5/2019
Reference7	14,456	3/5/2019
Reference8	14,656	3/5/2019
Reference9	14,856	3/5/2019
Reference10	15,056	3/5/2019
Reference11	15,256	3/5/2019
Reference12	15,456	3/5/2019
Reference13	15,656	3/5/2019
Reference14	15,856	3/5/2019
Reference15	16,056	3/5/2019
Reference16	16,256	3/5/2019
Reference17	16,456	3/5/2019
Reference1	16,656	4/6/2019
Reference2	13,456	4/6/2019
Reference3	13,656	4/6/2019
Reference4	13,856	4/6/2019
Reference5	14,056	4/6/2019
Reference6	14,256	4/6/2019
Reference7	14,456	4/6/2019
Reference8	14,656	4/6/2019
Reference9	14,856	4/6/2019
Reference10	15,056	4/6/2019
Reference11	15,256	4/6/2019
Reference12	15,456	4/6/2019
Reference13	15,656	4/6/2019
Reference14	15,856	4/6/2019
Reference15	16,056	4/6/2019
Reference16	16,256	4/6/2019
Reference17	16,456	4/6/2019
;


data _have;
set have;
char_date=put(date,monyy7.);
t=input(compress(reference,,'kd'),best.);
drop date;
run;
proc sort data=_have out=temp(drop=t);
by t  ;
run;
proc transpose data=temp out=want;
by reference notsorted;
id char_date;
var LifetimeCostScore;
run;

Re: Summing values in a column dependent on dates and conditions in other columns

BarneyC77 — Mon, 07 Jan 2019 17:49:30 GMT

Thanks Reeza. I'm getting a syntax error after the transpose step

proc transpose Completion_Date=totals out=want prefix=Date_;

_______________

ERROR 22-322: Syntax error, expecting one of the following: ;, DATA, DELIM, DELIMITER, LABEL, LET, NAME, OUT, PREFIX, SUFFIX.

ERROR 76-322: Syntax error, statement will be ignored.

Re: Summing values in a column dependent on dates and conditions in other columns

Reeza — Mon, 07 Jan 2019 18:09:03 GMT

You're not using the correct syntax. Note that the syntax is DATA = which denotes the input data set. Run the code exactly as posted and then start making changes.

Re: Summing values in a column dependent on dates and conditions in other columns

BarneyC77 — Mon, 07 Jan 2019 18:47:28 GMT

Thanks - nearly there I think - there's another error message copied below. Do I need to sort it between steps?

ERROR: Data set WORK.TOTALS is not sorted in ascending sequence. The current BY group has Reference = CS-YES-ECO215-SWP and the next BY

group has Reference= CS-ACWhyte-ECO210-SWP.

Re: Summing values in a column dependent on dates and conditions in other columns

Reeza — Mon, 07 Jan 2019 19:02:00 GMT

You shouldn't have to, because PROC MEANS should export a sorted data set. But otherwise, I suppose adding it in, should not cause any issues. If the data is sorted SAS will not 're-sort' it.

Re: Summing values in a column dependent on dates and conditions in other columns

Reeza — Mon, 07 Jan 2019 19:05:28 GMT

Add the NWAY option to PROC MEANS

data have;
input Reference :$20.	LifetimeCostScore :comma10.	Date :mmddyy10.;
format date mmddyy10. 	LifetimeCostScore comma10.;
cards;
Reference1	17,235	12/2/2018
Reference2	17,435	12/2/2018
Reference3	17,635	12/2/2018
Reference4	17,835	12/2/2018
Reference5	18,035	12/2/2018
Reference6	18,235	12/2/2018
Reference7	18,435	12/2/2018
Reference8	18,635	12/2/2018
Reference9	18,835	12/2/2018
Reference10	19,035	12/2/2018
Reference11	19,235	12/2/2018
Reference12	19,435	12/2/2018
Reference13	19,635	12/2/2018
Reference14	19,835	12/2/2018
Reference15	20,035	12/2/2018
Reference16	20,235	12/2/2018
Reference17	20,435	12/2/2018
Reference1	20,635	1/3/2019
Reference2	20,835	1/3/2019
Reference3	21,035	1/3/2019
Reference4	21,235	1/3/2019
Reference5	21,435	1/3/2019
Reference6	21,635	1/3/2019
Reference7	21,835	1/3/2019
Reference8	22,035	1/3/2019
Reference9	22,235	1/3/2019
Reference10	22,435	1/3/2019
Reference11	22,635	1/3/2019
Reference12	22,835	1/3/2019
Reference13	23,035	1/3/2019
Reference14	23,235	1/3/2019
Reference15	23,435	1/3/2019
Reference16	23,635	1/3/2019
Reference17	23,835	1/3/2019
Reference1	12,657	2/4/2019
Reference2	12,857	2/4/2019
Reference3	13,057	2/4/2019
Reference4	13,257	2/4/2019
Reference5	13,457	2/4/2019
Reference6	13,657	2/4/2019
Reference7	13,857	2/4/2019
Reference8	14,057	2/4/2019
Reference9	14,257	2/4/2019
Reference10	14,457	2/4/2019
Reference11	14,657	2/4/2019
Reference12	14,857	2/4/2019
Reference13	15,057	2/4/2019
Reference14	15,257	2/4/2019
Reference15	15,457	2/4/2019
Reference16	15,657	2/4/2019
Reference17	15,857	2/4/2019
Reference1	16,057	3/5/2019
Reference2	13,456	3/5/2019
Reference3	13,656	3/5/2019
Reference4	13,856	3/5/2019
Reference5	14,056	3/5/2019
Reference6	14,256	3/5/2019
Reference7	14,456	3/5/2019
Reference8	14,656	3/5/2019
Reference9	14,856	3/5/2019
Reference10	15,056	3/5/2019
Reference11	15,256	3/5/2019
Reference12	15,456	3/5/2019
Reference13	15,656	3/5/2019
Reference14	15,856	3/5/2019
Reference15	16,056	3/5/2019
Reference16	16,256	3/5/2019
Reference17	16,456	3/5/2019
Reference1	16,656	4/6/2019
Reference2	13,456	4/6/2019
Reference3	13,656	4/6/2019
Reference4	13,856	4/6/2019
Reference5	14,056	4/6/2019
Reference6	14,256	4/6/2019
Reference7	14,456	4/6/2019
Reference8	14,656	4/6/2019
Reference9	14,856	4/6/2019
Reference10	15,056	4/6/2019
Reference11	15,256	4/6/2019
Reference12	15,456	4/6/2019
Reference13	15,656	4/6/2019
Reference14	15,856	4/6/2019
Reference15	16,056	4/6/2019
Reference16	16,256	4/6/2019
Reference17	16,456	4/6/2019
;


proc means data=have nway;
class reference date;
format date monyy7.;
var lifetimecostScore;
output out = totals sum=MonthlyScore;
run;

proc transpose data=totals out=want prefix=Date_;
by reference;
id date;
idlabel date;
var MonthlyScore;
run;

Re: Summing values in a column dependent on dates and conditions in other columns

BarneyC77 — Mon, 07 Jan 2019 19:14:14 GMT

Thanks so much for your help, I really appreciate it. There are no more errors and it runs. However the output hasn;t transposed correctly - ie the Dates are still coming p as row labels rather than column headers. Also, I only need the one result (ie not N Obs, N, Std Dev, Minimum, Maximum). See attached pdf for output

Re: Summing values in a column dependent on dates and conditions in other columns

Reeza — Mon, 07 Jan 2019 19:18:37 GMT

That means you're not running the code provided but doing something else. Unless you show me what you've done I can't really tell you how to fix it.

The code above works as shown and solves your issue. Compare it to your code and make the necessary fixes.

Re: Summing values in a column dependent on dates and conditions in other columns

BarneyC77 — Mon, 07 Jan 2019 19:23:15 GMT

I've just been replacing want with the data reference. The full code I've been using is below.

proc means data=WORK.SASExample nway;

class reference date;

format date monyy7.;

var lifetimecostScore;

output out = totals sum=MonthlyScore;

run;

proc transpose data=totals out=want prefix=Date_;

by reference;

id date;

idlabel date;

var MonthlyScore;

run;

Re: Summing values in a column dependent on dates and conditions in other columns

BarneyC77 — Mon, 07 Jan 2019 19:24:58 GMT

I wanted it to refer to the data that I've extracted as the source data may well change, and I would like the programme to run over the dynamic data

Re: Summing values in a column dependent on dates and conditions in other columns

Reeza — Mon, 07 Jan 2019 19:38:23 GMT

I think you're checking the wrong output. The output is in the WANT data set.

You can add the NOPRINT option to the PROC MEANS statement to avoid having it displayed if you don't want it.

@BarneyC77 wrote:

I wanted it to refer to the data that I've extracted as the source data may well change, and I would like the programme to run over the dynamic data

Re: Summing values in a column dependent on dates and conditions in other columns

BarneyC77 — Tue, 08 Jan 2019 08:37:59 GMT

Hi Reeza,

You're probably right, I'm obviously doing something wrong. I am very new to this. I'm gutted as it's nearly there, but not quite, so my incompetence is clearly causing problems, even given your patience. The entirety of the code I'm using is below and it's generating the attached pdf without any errors, but the months are still appearing as row headers and not column headers, and I'm not sure where/how to insert the noprint to remove the extraneous columns. I've only amended the 'Have' reference to refer to the data source 'WORK.SASExample'.

proc means data= WORK.SASExample nway;

class reference date;

format date monyy7.;

var lifetimecostScore;

output out = totals sum=MonthlyScore;

run;

proc transpose data=totals out=want prefix=Date_;

by reference;

id date;

idlabel date;

var MonthlyScore;

run;

I'm posting again the probably vain hope that you haven't totally lost patience with my incompetence and might still be willing to help, as I'm doing some analysis for work.

Thanks,

Barney

Re: Summing values in a column dependent on dates and conditions in other columns

Reeza — Tue, 08 Jan 2019 15:49:46 GMT

Same answer as before, your output is in the data set want, not in the PDF. You're seeing the output from PROC MEANS in the PDF.

Try opening the WANT data set or printing it at the end. I've made the changes I suggested before to your code below and added the PROC PRINT.

proc means data= WORK.SASExample nway NOPRINT;

class reference date;

format date monyy7.;

var lifetimecostScore;

output out = totals sum=MonthlyScore;

run;

proc transpose data=totals out=want prefix=Date_;

by reference;

id date;

idlabel date;

var MonthlyScore;

run;

proc print data=want;run;

@BarneyC77 wrote:

Hi Reeza,

You're probably right, I'm obviously doing something wrong. I am very new to this. I'm gutted as it's nearly there, but not quite, so my incompetence is clearly causing problems, even given your patience. The entirety of the code I'm using is below and it's generating the attached pdf without any errors, but the months are still appearing as row headers and not column headers, and I'm not sure where/how to insert the noprint to remove the extraneous columns. I've only amended the 'Have' reference to refer to the data source 'WORK.SASExample'.

proc means data= WORK.SASExample nway;

class reference date;

format date monyy7.;

var lifetimecostScore;

output out = totals sum=MonthlyScore;

run;

proc transpose data=totals out=want prefix=Date_;

by reference;

id date;

idlabel date;

var MonthlyScore;

run;

I'm posting again the probably vain hope that you haven't totally lost patience with my incompetence and might still be willing to help, as I'm doing some analysis for work.

Thanks,

Barney