BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Atte51
Fluorite | Level 6

Long-time fluxer, first-time poster

Hi all! I desperately need advice with the following issue:

I have a problem with DataFlux Data Management Studio related to SAS Base-based tables. I would like to be able to filter data in the same way as with SQL queries, but I have no idea how to achieve this using the EEL language.

Simply put, my dataset includes data from different reporting months, and I would like to be able to filter the data based on various conditions, for example, according to different report dt. Additionally, the information should be retrieved from each row, not just a single max report dt from the table. 

Here's an example  of dataflux SQL query where we get the max report dt and the next largest report dt (-1) (just to give you an idea of what I'm looking for):

example.REPORT_DT = (Select MAX(REPORT_DT) from example)
example.REPORT_DT >= DATEADD(day, -1, CAST(GETDATE() as date))

All ideas and suggestions are welcome 🙂

Br,

 

Atte

1 ACCEPTED SOLUTION

Accepted Solutions
audrey
SAS Employee

I don't think there's anything pre-build to calculate the max of a list of records. The max function exists in EEL but will take values as arguments, so not what you need.

 

You can try to use some simple code in the Expression node 

//Pre-Expression 
//Setting variables
boolean isMaxVal;
hidden integer prevMaxVal;
isMaxVal= false;
prevMaxVal=0;

//Expression
if `Frequency` >= prevMaxVal
begin
   isMaxVal = true;
   prevMaxVal=`Frequency`;
end
else 
isMaxVal=false;
This might require your data to be sorted though.
The same logic could apply to the latest value. 
 
Does this help?
www.sas.comsupport.sas.com
SAS®... THE POWER TO KNOW®

View solution in original post

6 REPLIES 6
audrey
SAS Employee

Hi,

 

I am not sure I completely understand what you're trying to achieve. If you can give an example of the data you have in input and the output you expect, I can take a look at what can be done with the Expression node.

 

Audrey

 

www.sas.comsupport.sas.com
SAS®... THE POWER TO KNOW®
Atte51
Fluorite | Level 6

Hello Audrey!

Certainly! So, as you can see in the picture, I have daily data on a table, and I would like to be able to retrieve the maximum value and possibly the next value from these maximum values.

I am creating data quality rules that require examining only the latest values. In the data on the table, there is no variable that indicates a "valid" value. The only way to retrieve the latest values is the newest row in the table based on the report_dt. I also have a need to occasionally compare the differences between values for different dates, so I would like to be able to retrieve the next highest value (-1) from the data. 

audrey
SAS Employee

I don't think there's anything pre-build to calculate the max of a list of records. The max function exists in EEL but will take values as arguments, so not what you need.

 

You can try to use some simple code in the Expression node 

//Pre-Expression 
//Setting variables
boolean isMaxVal;
hidden integer prevMaxVal;
isMaxVal= false;
prevMaxVal=0;

//Expression
if `Frequency` >= prevMaxVal
begin
   isMaxVal = true;
   prevMaxVal=`Frequency`;
end
else 
isMaxVal=false;
This might require your data to be sorted though.
The same logic could apply to the latest value. 
 
Does this help?
www.sas.comsupport.sas.com
SAS®... THE POWER TO KNOW®
Atte51
Fluorite | Level 6

Hi Audrey,

 

This worked really nicely, thanks very much!

 

-Atte

VincentRejany
SAS Employee

Hi 

In the SQL Query Node, you can enter SQL that is specific to the data source that is selected in the Data Source field above

If you selected a SAS table that is accessed with a SAS Federation Server connection or a SAS Data Set Connection, you can enter Federation Server FedSQL, which is described in the SAS Federation Server FedSQL Reference Guide. If you selected a database table that is accessed with an ODBC connection, you could enter database-specific syntax. For more information, see the ODBC help in DataFlux Data Management Studio and also the documentation for the database.

 

I tested successfully these queries

select a.* from SAS.ORDERS a where delivery_date = (select max(b.delivery_date) from SAS.ORDERS b)

select * from SAS.ORDERS where delivery_date >= today() - 1

 

Atte51
Fluorite | Level 6

Hi VincentRejany!

To my knowledge, the SQL node cannot be used with SAS base tables. At least, that has been my understanding. This solution solved the problem immediately, but as far as I know, I have to use the data source node with SAS base tables, where there is no SQL capability.

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 6 replies
  • 1266 views
  • 3 likes
  • 3 in conversation