BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Mirisage
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
mkeintz
PROC Star

Make is

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

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------

View solution in original post

10 REPLIES 10
mkeintz
PROC Star

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;

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
Linlin
Lapis Lazuli | Level 10

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;

Astounding
PROC Star

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.


Mirisage
Obsidian | Level 7
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

Astounding
PROC Star

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.

Mirisage
Obsidian | Level 7

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

mkeintz
PROC Star

Make is

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

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
Mirisage
Obsidian | Level 7
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

Astounding
PROC Star

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.

Mirisage
Obsidian | Level 7

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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