DATA Step, Macro, Functions and more

summing up data into months

Accepted Solution Solved
Reply
Contributor
Posts: 36
Accepted Solution

summing up data into months

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 -

sas.png


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.


Accepted Solutions
Solution
‎03-24-2015 07:57 AM
Frequent Contributor
Posts: 117

Re: summing up data into months

Posted in reply to rajat051984

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;

View solution in original post


All Replies
Super User
Posts: 11,343

Re: summing up data into months

Posted in reply to rajat051984

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

Contributor
Posts: 36

Re: summing up data into months

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

Super User
Posts: 19,770

Re: summing up data into months

Posted in reply to rajat051984

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.

Solution
‎03-24-2015 07:57 AM
Frequent Contributor
Posts: 117

Re: summing up data into months

Posted in reply to rajat051984

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;

Contributor
Posts: 36

Re: summing up data into months

Thank  you!!

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 242 views
  • 3 likes
  • 4 in conversation