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

[BarneyC77]

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

[Reeza]

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

---

[BarneyC77]

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

[Reeza]

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

---

[novinosrin]

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;