BookmarkSubscribeRSS Feed
jlr337
Fluorite | Level 6

 

 I am trying to create a new, derived field to use in tables and graphs. I have followed the steps outline in many pages/tutorials but the field is not calculating as expected. I want to create a line chart noting the volume (frequency) and mean by month. (adding UCL and LCL fields as well but am having same issue with those fields, presumably because they use the mean in their calculations) Screenshots attached.  I don't understand why the mean is not calculating but only displaying a 1.00 per month (category). I attempted to create a new calculated field but the frequency measure does not appear and I have no other field that is numeric.  I see how to add a reference line, but I don't want to have to recalculate it each time.  I am using SAS VA 7.3. Thanks in advance for your assistance.

 

 

6 REPLIES 6
FredrikE
Rhodochrosite | Level 12

This is how I see it...:)

Since frequency is 1 on every row in the data the average can never be anything else than 1, regardless how you group the data.

//Fredrik

jlr337
Fluorite | Level 6

Thank you for the response FriderikE.  That's what I was afraid of.  Can you recommend how I could get it to work?  I need a mean value of the total volume by month.  When I calculate the mean, UCL, LCL, and Stdev in Excel, it looks like this (see attached).   As new data is entered, monthly rows would be added and these values would update accordingly, ideally, without having to add/update anything manually.  I am attempting to create a chart in VA with these data, then pulling into Excel using SAS add-in so end user can update as needed just by refreshing.  My back up plan is to create tab in excel to calculate and create the charts, but I was hoping to do this in VA so that it can be as  'automated' as possible.

 (if you see an issue with my math, please let me know.  I know there is a way to calculate the mean/ucl/lcl that adjusts but I don't know how to do that, so I'm keeping it simple- but it still may not be correct)

Thank you for the response FriderikE. That's what I was afraid of. Can you recommend how I could get it to work? I need a mean value of the total volume by month. When I calculate the mean, UCL, LCL, and Stdev in Excel, it looks like this (below). As new data is entered, monthly rows would be added and these values would update accordingly, ideally, without having to add/update anything manually. I am attempting to create a chart in VA with these data, then pulling into Excel using SAS add-in so end user can update as needed just by refreshing. My back up plan is to create tab in excel to calculate and create the charts, but I was hoping to do this in VA so that it can be as 'automated' as possible. (if you see an issue with my math, please let me know. I know there is a way to calculate the mean/ucl/lcl that adjusts but I don't know how to do that, so I'm keeping it simple- but it still may not be correct) mean=avg($B$1:$B$17), stdev=stdev.p($B$1:$B$17), ucl=C1+(3*D1), lcl=C1-(3*D1) mon(A) total(B) mean(C) stdev(D) ucl(E) lcl(F) 

Thanks for your help.

FredrikE
Rhodochrosite | Level 12

I have tried some ideas but I don't manage to get the total sum of periods. The total of frequency works fine.

 

Sorry, but I think the problem is that it is not possible to do aggregations on aggregations....

//Fredrik

jlr337
Fluorite | Level 6

Gotcha.  That's too bad, but I really appreciate your help with this.  Thanks! Jenefer

FredrikE
Rhodochrosite | Level 12

Can't let this go yet.....:)

 

Maybe this can work if you look at one year at the time....:

 

First create a aggregated variable with the total sum of frequency:

avg_total_1.png

Then create a second aggregated variable that counts the distinct periods:

avg_total_2.png

Finally create a aggregated variable that do the division var1 / var2.

 

I think this might work ?

//Fredrik

jlr337
Fluorite | Level 6

Thanks for sticking with me 🙂 Your suggestion makes sense logically, but I'm not sure I did it correctly because I'm still seeing just 1.00.  However, I found another suggestion for how to get around this which is to add the mean variable to the sas dataset from which the VA chart is build.  I am going to try that next.  Thanks again, Jenefer.

SAS Innovate 2025: Call for Content

Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!

Submit your idea!

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
  • 1898 views
  • 2 likes
  • 2 in conversation