BookmarkSubscribeRSS Feed
MahRaz
Fluorite | Level 6

Hi all,

 

We woulde like to count the number of days a certain constrain is met. The table has three columns which specify the primary key.

 

Example:

 

INPUT:

Date              | Category 1 | Category 2 | Measure

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

01-01-2016   | Dog            | Orange       | 10

01-01-2016   | Cat             | Apple          | 12

01-01-2016   | Dog            | Apple          | 7

02-01-2016   | Cat             | Orange       | 13

02-01-2016   | Dog            | Orange       | 6

02-01-2016   | Cat             | Apple          | 19

03-01-2016   | Cat             | Apple          | 5

03-01-2016   | Dog            | Orange       | 10

03-01-2016   | Dog            | Apple          | 11

 

Constrain: the number of days per year the sum of the measure is higher then 31.

 

For 01-01-2016 the sum is equal to 29 (=10+12+7),  for 02-01-2016 the sum is equal to 38 (=13+6+9) and for 03-01-2016 is equal to 26 (=5+10+11). So only day 02-01-2016 is more than 30 and met the constrain. And as an output I would like to see the number of days which met the condition which is is in this example.

 

Do you have any idea how we can calculate this in SAS VA.

 

Thank you!

 

 

2 REPLIES 2
AndrewHowell
Moderator

Hi MahRaz,

 

I don't have VA handy at the moment, but here is an SQL statement you could use in your expression:

 

select date, sum(measure) as total
  from &SYSLAST
  group by date
  having total gt 31;

 

When you say "met this condition", do you mean "passed" the constraint, or "failed" the constraint?

 

Hope this helps.

 

Regards,

Andrew.

MahRaz
Fluorite | Level 6
Hi Andrew,

Thank you for your reply. I should have been more specific that I need to make this calculation in Report Designer. And there I have access only to a limitted number of functions which select is not one of them. Neither I can use Grouby Date in my calculations! By ''meet the condition'' I mean passing the condition of being greater than 30 in this example.

Regards!
Mahraz

sas-innovate-2024.png

📢

ANNOUNCEMENT

The early bird rate has been extended! Register by March 18 for just $695 - $100 off the standard rate.

 

Check out the agenda and get ready for a jam-packed event featuring workshops, super demos, breakout sessions, roundtables, inspiring keynotes and incredible networking events. 

 

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