summing up data into months

[rajat051984]

I have the following dataset.

data input;

input desc $2. tran_date date9. cnt 3. amt 3.;

format tran_date date9.;

datalines;

A 13Feb2015 2 10

A 10Jan2015 1 10

A 09Jan2015 1 20

B 05Feb2014 2 10

B 05Dec2014 2 20

B 02Dec2014 1 20

C 20Feb2015 1 10

C 31Jan2015 2 10

C 10Dec2014 2 20

C 10Dec2014 1 20

;

and the desired output is as follows -

The month sums that I need to take is from 201308 to current month which results in a big query, if I try to do it with sql.

Can I do it datastep and macros ?

I would like to have an option to drop Dec2014_Cnt and take only the Dec2014_Amt alon with 4 other fields. Kindly advice.

[ballardw]

Do you actually need a data set or a report in this layout?

[rajat051984]

Sorry, I got your question wrong earlier. I need a report. The key field is not Desc but 6 other fields. But the count, amount layout is the one I mentioned. As you can figure it ,the report length will increase by two columns every month,

Message was edited by: rajat panda

[Reeza]

You don't store data in this format but you report it using proc report in this format -- or proc tabulate.

If you absolutely do need to store it this way then calculate the sums using proc means and then proc transpose the dataset.

[Vish33 · Accepted solution]

try this code:

data inpt (drop=tran_date);
set input;
new_var=put(intnx('month',tran_date,0),monname3.)||put(year(tran_date),4.);
run;  

proc sort data=inpt ; by desc new_var ; run;

proc tabulate data=inpt ;

class desc new_var;

var cnt amt;

tables desc='',new_var='' *(cnt amt)*sum=''/Box=Desc;

run;

[rajat051984]

Thank  you!!