Hi SAS Forum,
I have used the following “Proc summary” code to consolidate/summarize a SAS data set named “temp” generated by a set of previous code pieces (below code is a part of an on-going code).
proc summary data=temp nway missing;
class Current_Date;
var Current One_to_30 Thirty_to_60 Sixty_to_90 Ninety_plus NPNA write_off;
output out=new_table(drop=_:)
sum=;
run;
This code outputted the following table
Current_date | Current | One_to_30 | Thirty_to_60 | Sixty_to_90 | Ninety_plus | NPNA | write_off |
28-Feb-10 | 15 | 7 | 100 | 50 | 33 | 44 | 10 |
5-Mar-10 | 0 | 0 | 0 | 0 | 0 | . | 7 |
11-Mar-10 | 0 | 0 | 0 | 0 | 0 | . | 3 |
31-Mar-10 | 2 | 3 | 6 | 10 | 27 | 6 | 5 |
30-Apr-10 | 33 | 44 | 55 | 88 | 100 | 200 | 10 |
12-May-10 | 0 | 0 | 0 | 0 | 0 | . | 25 |
14-May-10 | 0 | 0 | 0 | 0 | 0 | . | 25 |
31-May-10 | 100 | 200 | 250 | 125 | 55 | 50 | 0 |
………………………….
…………………………
…………………….
The rows of the above output table goes on until 31-Jan-12, but I presented only a part.
Question:
What I need is really a table like below (where I need just monthly sums, by month end, and not by each days).
All variables except “write_off” are already in “monthly sum” s (which is what I need).
Is there any way that I can include a code piece that would sum up “write_off” too.
Thank you for your help
Mirisage
Current_date | Current | One_to_30 | Thirty_to_60 | Sixty_to_90 | Ninety_plus | NPNA | write_off |
28-Feb-10 | 15 | 7 | 100 | 50 | 33 | 44 | 10 |
31-Mar-10 | 2 | 3 | 6 | 10 | 27 | 6 | 15 |
30-Apr-10 | 33 | 44 | 55 | 88 | 100 | 200 | 10 |
31-May-10 | 100 | 200 | 250 | 125 | 55 | 50 | 50 |
30-Jun-10 | I didn’t fill this | I didn’t fill this | I didn’t fill this | I didn’t fill this | I didn’t fill this | I didn’t fill this | I didn’t fill this |
31-Jul-10 | I didn’t fill this | I didn’t fill this | I didn’t fill this | I didn’t fill this | I didn’t fill this | I didn’t fill this | I didn’t fill this |
31-Aug-10 | I didn’t fill this | I didn’t fill this | I didn’t fill this | I didn’t fill this | I didn’t fill this | I didn’t fill this | I didn’t fill this |
30-Sep-10 | I didn’t fill this | I didn’t fill this | I didn’t fill this | I didn’t fill this | I didn’t fill this | I didn’t fill this | I didn’t fill this |
31-Oct-10 | I didn’t fill this | I didn’t fill this | I didn’t fill this | I didn’t fill this | I didn’t fill this | I didn’t fill this | I didn’t fill this |
30-Nov-10 | I didn’t fill this | I didn’t fill this | I didn’t fill this | I didn’t fill this | I didn’t fill this | I didn’t fill this | I didn’t fill this |
31-Dec-10 | I didn’t fill this | I didn’t fill this | I didn’t fill this | I didn’t fill this | I didn’t fill this | I didn’t fill this | I didn’t fill this |
31-Jan-11 | I didn’t fill this | I didn’t fill this | I didn’t fill this | I didn’t fill this | I didn’t fill this | I didn’t fill this | I didn’t fill this |
28-Feb-11 | I didn’t fill this | I didn’t fill this | I didn’t fill this | I didn’t fill this | I didn’t fill this | I didn’t fill this | I didn’t fill this |
31-Mar-11 | I didn’t fill this | I didn’t fill this | I didn’t fill this | I didn’t fill this | I didn’t fill this | I didn’t fill this | I didn’t fill this |
30-Apr-11 | I didn’t fill this | I didn’t fill this | I didn’t fill this | I didn’t fill this | I didn’t fill this | I didn’t fill this | I didn’t fill this |
31-May-11 | I didn’t fill this | I didn’t fill this | I didn’t fill this | I didn’t fill this | I didn’t fill this | I didn’t fill this | I didn’t fill this |
30-Jun-11 | I didn’t fill this | I didn’t fill this | I didn’t fill this | I didn’t fill this | I didn’t fill this | I didn’t fill this | I didn’t fill this |
31-Jul-11 | I didn’t fill this | I didn’t fill this | I didn’t fill this | I didn’t fill this | I didn’t fill this | I didn’t fill this | I didn’t fill this |
31-Aug-11 | I didn’t fill this | I didn’t fill this | I didn’t fill this | I didn’t fill this | I didn’t fill this | I didn’t fill this | I didn’t fill this |
30-Sep-11 | I didn’t fill this | I didn’t fill this | I didn’t fill this | I didn’t fill this | I didn’t fill this | I didn’t fill this | I didn’t fill this |
31-Oct-11 | I didn’t fill this | I didn’t fill this | I didn’t fill this | I didn’t fill this | I didn’t fill this | I didn’t fill this | I didn’t fill this |
30-Nov-11 | I didn’t fill this | I didn’t fill this | I didn’t fill this | I didn’t fill this | I didn’t fill this | I didn’t fill this | I didn’t fill this |
31-Dec-11 | I didn’t fill this | I didn’t fill this | I didn’t fill this | I didn’t fill this | I didn’t fill this | I didn’t fill this | I didn’t fill this |
31-Jan-12 | I didn’t fill this | I didn’t fill this | I didn’t fill this | I didn’t fill this | I didn’t fill this | I didn’t fill this | I didn’t fill this |
Make is
date=intnx('month',current_date,0,'end');
The simplest way is to;
data vtemp /view=vtemp;
set temp;
date=intnx('month',date,0,'end');
run;
proc sumary data=vtemp .....;
class date;
run;
one way is to create a new variable to replace current_date:
data have;
input date mmddyy10.;
newdate=intnx('month',date,0,'e');
format date newdate date9.;
cards;
03/21/2012
03/30/2012
02/20/2012
;
proc print;run;
If you really must have the Day as part of the date, then you already have some good suggestions.
If you find that including the Day is misleading because it actually reflects many separate days during the month, you could try using your original code and your original data, but add one line to the PROC SUMMARY:
format current_date monyy7.;
Try it, and see what you prefer.
Good luck.
Thank everyone of you for this help.
Sorry, I have confused you all, I think.
My following code generate the first table I have shown.
proc summary data=temp nway missing;
class Current_Date;
var Current One_to_30 Thirty_to_60 Sixty_to_90 Ninety_plus NPNA write_off;
output out=new_table(drop=_:)
sum=;
run;
All what I need is to further collapse the many separate days of "write_off" variable into a single month end date as shown in first 4 rows of table 2. The sums of different variables are correct in that table.
Thank you
Basically, all of the answers are valid in that case. You just need to run the suggested program using new_table as the input data set, instead of temp.
Good luck.
Hi Astounding,
I have run this.
data vtemp /view=vtemp;
set new_table;
date=intnx('month',date,0,'end');
run;
A new variable named "date" was created but all with missing values.
This may be beucause my original variable "current_date" was actually in this form.
28FEB2010
05MAR2010
11MAR2010
23MAR2010
My tables presented above shows it in the form "28-Feb-10" because it accidently happned when I tranferred the SAS data into Excel to make the posting in the forum.
In this case how should I change the yellow highlighted part below?.
Also,
data vtemp /view=vtemp;
set new_table;
date=intnx('month',date,0,'end');
run;
After created vtemp data set, will the following code ok to get the final table I want?
proc summary data=vtemp nway missing;
class date;
output out=Table_I_want_finally;
run;
Thank you for your time
Make is
date=intnx('month',current_date,0,'end');
Yeh, finally it worked.
A big thank to every one of you!
data vtemp /view=vtemp;
set want;
date=intnx('month',current_date,0,'end');
run;
proc summary data=vtemp nway missing;
format date date9.;
class date;
var Current One_to_30 Thirty_to_60 Sixty_to_90 Ninety_plus NPNA write_off;
output out=Table_I_want_finally(drop=_:)
sum=;
run;
Mirisage
Mirisage,
The first part of the answer to your question is this. You will have to study the details of PROC SUMMARY. In the longer term, you will need to understand that procedure including common options and statements that are available.
Regarding your new program, it is not enough. You left out key pieces of the original program, such as the NWAY and MISSING options on the PROC statement, the entire VAR statement, and SUM= to help define the output.
It is unclear from your post whether CURRENT_DATE is a numeric variable on SAS's date scale, or whether it is a character string. Looking at the report in your original post, it appears to be a SAS date because the dates come out in order. If it were a character variable, they would appear in alphabetical order. Do you know how to check to see whether CURRENT_DATE is numeric or character, and whether it has a default format?
Try taking your original program as is, and making three changes. First, change DATA= so it says DATA=NEW_TABLE instead of DATA=TEMP. (That means that this new step will have to be part of the same program, following the first PROC SUMMARY that you have already coded.) Second, change OUT= so it says OUT=TABLE_I_WANT_FINALLY. Third, add the FORMAT statement that I suggested: FORMAT CURRENT_DATE MONYY7.; If those results aren't what you need, we can revisit the other approaches that use the INTNX function.
Good luck.
Hi Astounding,
Thank you indeed for taking your valuble time to explain the issue in such a detail.
I have a lot of things to learn from your response.
Thanks again!
Regards
Mirisage
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 the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.