BookmarkSubscribeRSS Feed
masterLearner01
Calcite | Level 5

Hello all,

 

I've been working on a project for a couple weeks now and am unsure of where to go from here. This will be my first post, although I have been browsing this great community forum for so long, always learning.

 

I have a HUGE dataset currently that looks something like this:

YearMonthPmt
2017134.77685505
2017147.85238688
2017111.15835
2017244.31531843
201721.044526792
2017366.05520417
2017462.55252575
2017586.27913541
2017689.09913235
2017937.12795924
20171429.66068787
2018193.88662208
2018184.94683944
2018394.91144317

This is only a very very small subset of the data.

 

My goal is to: Create a triangle matrix with incremental data. It should basically look like this:

 Month
 36912151821242730333639
Year             
2017205.2026413237.930793537.12795924029.6606878700000000
2018273.74490470           

 

AGAIN, this is a much smaller portion of the dataset. The years range from 1940-2018, and the quarters range from 3-600.

 

What my method is right now:

1. I create more than 100 tables with incremental data. For example I will create table q0003 which is a table that looks like this:

2017 205.2026413 

2018 273.7449047

It just tells me incremental data from the 00th month to the 03th month for each year.

 

And then q0306 would be

2017  237.9307935

2018 0

 

2. I join all these >100 tables giving me an incremental triangle.

 

 

 

This process seems extremely long and very unintuitive. Also not really able to be replicated when dataset is updated. Is there any better method you could provide me with?

 

Any questions, just ask.

 

Thanks everyone. 

7 REPLIES 7
PaigeMiller
Diamond | Level 26

What is month 15, 18, 21, etc.??

 

How is the 205.2026413 for 2017 computed?

--
Paige Miller
masterLearner01
Calcite | Level 5

Month 15,18,21, etc. are the number of months after the year has started.

 

So Month 15 in the 2017 row would be any payments where the year = 2017 and the 12<Month<= 15.

 

The 205.2026413 for 2017 is computed by taking the summation of pmt where year = 2017 and 0< Month <= 3. Which would be the first 6 rows of Pmt.

PaigeMiller
Diamond | Level 26

So month 15 of 2017 is March 2018?

--
Paige Miller
masterLearner01
Calcite | Level 5

Yes, BUT it's only for payments that came in during Month 15, or March 2018, for invoices created in 2017.

So that's why the pmt <= 3 and Year=2018 are not included in the final table under month 15 of 2017.

 

Does this make more sense?

PaigeMiller
Diamond | Level 26

How would one know what year the invoice is, if it is not in your original data as shown?

--
Paige Miller
masterLearner01
Calcite | Level 5

The "Year" column in my original dataset (the first dataset) is the Year of the Invoice. The "Month" column is the month that the invoice is paid. 

 

For example, if we have

 

Year     Month    Pmt

2014        34         100

 

That is a $100 payment in October 2016 for an invoice created 2014.

PaigeMiller
Diamond | Level 26

Thanks for the explanation.

 

This is UNTESTED CODE

 

data have2;
    set have;
    month=floor((month-1)/3);
run;

proc report data=have2;
    columns year month,pmt;
    define year/group;
    define month/across;
    define pmt/analysis sum;
run;
--
Paige Miller

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!

How to Concatenate Values

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.

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
  • 7 replies
  • 820 views
  • 0 likes
  • 2 in conversation