Create SAS VA 7.3 filter in Data Explorer using last date value

[BLE]

My dataset has over 500,000 rows of data.

One category field in my data is called cat1, the Model Type is discrete, and the Format is $4.  

There are 117 levels (distinct values) for cat1.

Another category field in my data is report_time, the Model Type is Continous, and the Format is Date, Time.  There is no duplication in cat1*report_time.

I need to create a filter in SAS VA 7.3 Data Exploration so that I am only pulling data from the record associated with the last submitted date/time.

Then I will create a bar chart of a numeric measure with aggregation of sum.

My goal is to have the bar chart add one value per level of cat1, and that one value will have a variety of report_time.  There is no reference time and I can not use a time period for the pull.  It must simply be the most recent record for each distinct level of cat1.

The last report_time could be different for each level of cat1 so I can not use the regular boolean operators.  

[TriciaAanderud]

I see two possible solutions - both of these are untested.

• I would update my data to have a variabled called "last_record_flag" that would allow me to filter. The variables would simply have a 1 if it was the last record and otherwise be 0 or missing.
  
  It's not exactly clear to me how you are going to build this chart to use that record.
  
  
• In the Aggregate measures there is a function called LAST that gets the last item.  
  I have not used this function so I'm really just making you aware it exists as opposed to telling you how to use it.
  
  Here's user guide reference.

Hope this gives you an exploration trail.

[BLE]

BLE: Thanks for the suggestions, but neither is functional for me at the moment.



Suggestion: I would update my data to have a variable called "last_record_flag" that would allow me to filter. The variables would simply have a 1 if it was the last record and otherwise be 0 or missing.
BLE: I don't currently have permission to modify the original dataset structure, so I can't add variables to the original data. I am working on this and will likely end up taking this type of action, but was hoping to be able to get the job done in SAS VA with the data I currently have.



Suggestion: In the Aggregate measures there is a function called LAST that gets the last item. I have not used this function so I'm really just making you aware it exists as opposed to telling you how to use it.
BLE: The aggregate measure Last requires you to choose a numeric measure in addition to the time category. I was hoping to use the last time across all of the measures, but decided to test this approach by creating the aggregate measure of last time for one of my numeric measures and using that as my filter when reviewing the chosen numeric measure. That failed because you can't use an aggregate measure as a filter.



I then asked SAS tech help for a way to integrate proc sql syntax in a filter being used in SAS VA Data Explorer since I have code that pulls the desired data. I was told there is no way to integrate code into Data Explorer, I would have to use Data Preparation if I wanted to introduce my own code. The problem with that is again permissions - I do not currently have permission to modify data so I am blocked from doing anything in Data Preparation.



If someone has an on-the-fly solution for applying an aggregate filter in Data Explorer I would be thrilled, but I am in the process of applying for additional access so that I can work in Data Preparation and/or modify the table structure of the original dataset.



Thanks,

Bonnie

[TriciaAanderud]

Using the TreatAs function you can convert a Date value into a numeric value.  Then you could get it's MAX value.