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

[BarneyC77]

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

[PeterClemmensen]

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

[BarneyC77]

Hi,

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

Thanks,

Barney

[Reeza]

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.

[BarneyC77]

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

[Reeza]

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.

[BarneyC77]

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

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

                          _______________

                          22

                          76

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.

[Reeza]

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.

[BarneyC77]

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.

[Reeza]

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.

[Reeza]

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;

[BarneyC77]

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

[Reeza]

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.

[BarneyC77]

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;