topic Re: Proc summary question? in SAS Procedures

Proc summary question?

Mirisage — Mon, 27 Aug 2012 17:45:19 GMT

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

Re: Proc summary question?

mkeintz — Mon, 27 Aug 2012 18:12:52 GMT

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;

Re: Proc summary question?

Linlin — Mon, 27 Aug 2012 18:16:10 GMT

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;

Re: Proc summary question?

Astounding — Mon, 27 Aug 2012 18:29:44 GMT

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.

Re: Proc summary question?

Mirisage — Mon, 27 Aug 2012 19:02:20 GMT

Hi mkeintz, Linlin and Astounding,

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

Re: Proc summary question?

Astounding — Mon, 27 Aug 2012 19:06:24 GMT

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.

Re: Proc summary question?

Mirisage — Mon, 27 Aug 2012 19:55:05 GMT

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

Re: Proc summary question?

mkeintz — Mon, 27 Aug 2012 19:57:27 GMT

Make is

date=intnx('month',current_date,0,'end');

Re: Proc summary question?

Mirisage — Mon, 27 Aug 2012 20:19:27 GMT

Hi mkeintz, Astounding and Linlin,

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

Re: Proc summary question?

Astounding — Mon, 27 Aug 2012 20:24:00 GMT

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.

Re: Proc summary question?

Mirisage — Tue, 28 Aug 2012 02:13:46 GMT

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