BookmarkSubscribeRSS Feed
mshparber
Calcite | Level 5


Hello friends,

Is there a way to implement a following task in VA

In my table I have debts records:

CustomerID, Amount, DebtStartDate, PaymentDate

I would like to show for EACH DAY over a specific period the total of debts amount (which is the sum of all records that DebtStartDate<=EACH DAY<=PaymentDate)

For those of you that also work with Microsoft's DAX - it's similar to a disconnected Time Dimension

Thank you

Michael

6 REPLIES 6
TejaSurapaneni
Lapis Lazuli | Level 10

Hi Michael,

Can you explain more (with example) for better understanding.

Thanks & Regards,

Teja Surapaneni

mshparber
Calcite | Level 5

Hi Teja,

Attached is Excel sample.

https://drive.google.com/file/d/0BxwE8Gt6ONVteDJDZWdMY2VXMWs/view?usp=sharing

My table only contains the debt START and END dates, as well as Amount and customer number

What I would like to see - is time line chart that shows me all my OPEN DEBTS on each date in any timerange that I choose

That's it.

Thanks a lot!


Michael Shparber

Sam_SAS
SAS Employee

Hi Michael,

I asked around about this, and I don't think VA can generate or infer the date values based on your start and end dates.

I think you will need to create a DATE column as part of your data preparation.

A do loop like the following was suggested:

proc sort data=debt out=debt_sorted;
by customer_id date_start date_end;
run;

data debt_new;
   set debt_sorted;
   by customer_id;
  format _date_ date.;
   label _date_ = "user inserted";

   if(customer_id.first) then do;
        _date_ = date_start;
        debt_amount = debt_amount;
        date_start = date_start;
        date_end = date_end;
        customer_id = customer_id;
       output;
   end;
   else do;
       label:         _date_ = _date_ + 1;
                     if(_date_ GT end_date ) {
                                goto label 2;
                      }
                      debt_amount = debt_amount;
                     date_start = date_start;
                     date_end = date_end;
                     customer_id = customer_id;
                    output;
                goto label;
   end;
   label2:

   retain _date_;
run;

I hope that will help,

Sam

mshparber
Calcite | Level 5

Thanks Sam,

As I understand, you propose for each customer to create all records in between START and END dates...

Imagine just 100,000 customers having debt with average age of one year.

Creating a record for each day for each customer would create 36.5 million records

It is not that large of a database but it's much more rows without any new information at all

Now imagine that debt history contains 1.5 million customers. Now we're talking about dealing with half of billion rows instead of just 1.5 million.

Microsoft has a very elegant solution for this using Measures and a Disconnected Time Dimension in their Tabular SQL Server Technology

It would be so useful to have a similar feature in SAS VA

I am sure that lots of Data Analysts will find it useful in many fields such as Debts, Inventory, etc.

Whom can I talk to in SAS R&D to explain the need and the solution in detail?

Thanks,

Michael

LinusH
Tourmaline | Level 20

Yes, as you already notices, this technique could "explode" your data.

A starting point is to create an idea in this forum, which is one open way to communicate desired developments to the product. You'll have my vote!

In the mean time, you could either try to aggregate your data (if possible), or create an application outside VA.

Data never sleeps
Sam_SAS
SAS Employee

Hi Michael,

As Linus recommends, you should create an "Idea" in the Visual Analytics community. All ideas are reviewed by product management, which should mean a good chance that the feature will be considered for a future release.

Thanks,
Sam

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!

Tips for filtering data sources in SAS Visual Analytics

See how to use one filter for multiple data sources by mapping your data from SAS’ Alexandria McCall.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 6 replies
  • 1026 views
  • 3 likes
  • 4 in conversation