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.
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;
Do you actually need a data set or a report in this layout?
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
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.
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;
Thank you!!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.