Help using Base SAS procedures

Proc summary question?

Accepted Solution Solved
Reply
Super Contributor
Posts: 338
Accepted Solution

Proc summary question?

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=_Smiley Happy

    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


Accepted Solutions
Solution
‎08-27-2012 03:57 PM
Trusted Advisor
Posts: 1,022

Re: Proc summary question?

Make is

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

View solution in original post


All Replies
Trusted Advisor
Posts: 1,022

Re: Proc summary question?

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;

Super Contributor
Posts: 1,636

Re: Proc summary question?

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;

Super User
Posts: 5,516

Re: Proc summary question?

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.


Super Contributor
Posts: 338

Re: Proc summary question?

Posted in reply to Astounding
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=_Smiley Happy

    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

Super User
Posts: 5,516

Re: Proc summary question?

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.

Super Contributor
Posts: 338

Re: Proc summary question?

Posted in reply to Astounding

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

Solution
‎08-27-2012 03:57 PM
Trusted Advisor
Posts: 1,022

Re: Proc summary question?

Make is

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

Super Contributor
Posts: 338

Re: Proc summary question?

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=_Smiley Happy

    sum=;

run;

Mirisage

Super User
Posts: 5,516

Re: Proc summary question?

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.

Super Contributor
Posts: 338

Re: Proc summary question?

Posted in reply to Astounding

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

🔒 This topic is solved and locked.

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

Discussion stats
  • 10 replies
  • 420 views
  • 3 likes
  • 4 in conversation