Exploring, predicting and reporting with SAS Visual Analytics and SAS Visual Statistics

stymied on line chart

Accepted Solution Solved
Reply
Contributor
Posts: 66
Accepted Solution

stymied on line chart

[ Edited ]

Hi, I'm trying to reproduce a client graph from Excel to VA 7.3. 

 

Y axis is numeric measure, fine. X axis is character data representing week ranges for example row 1 is “Jul 6 – Jul 12” Row 2 is “Jul 13- Jul 19”, etc. There are 82 weeks like this. The current line chart in Excel has the X axis in the order the rows occur in the spreadsheet. When I bring the data into VA it sorts alphabetically by name which isn't appropriate.

 

I wrote a data step to add a new character column that is W01 for week 1, W02 for week 2, etc. This creates the appropriate graph line but doesn’t give any indication that W02 is Jul 13-Jul 19. Any suggestions, apart from creating a stored process, to be able to use the fully spelled out week name on the X axis?  I’m essentially looking for something similar to the order=data option in proc freq. Other suggestions though would be fine. 

 

ETA: Tried user-defined formats, but decided that won't work as the format will get outdated as rows are added or deleted. Tried custom sort, that was pretty cumbersome and only allows for 20-some-odd sort values. 

 

The users are not programmers and will have to do this themselves exclusively in VA. Thanks!

Michelle

Accepted Solutions
Solution
‎02-21-2017 01:01 PM
Contributor
Posts: 66

Re: stymied on line chart

With the help of tech support we finally got it working. The key is to create a NUMERIC variable 1 thru 82, then create and apply a user-defined format that re-codes 1 to 'Aug 1- Aug 6', etc. Then put that format library in a place where the server can see it, in my case Lev1\SASApp\SASEnvirnment\SASFormats.  I was lazy and named the catalog "formats" since that's what the SAS Config file was already looking for. 

 

When this numeric variable is brought into VA it is automatically treated as a Category and sorted on by the formatted value. 

Michelle

View solution in original post


All Replies
Occasional Contributor
Posts: 13

Re: stymied on line chart

Posted in reply to mbuchecker

You could procedurally generate your format based on the actual data. 

Contributor
Posts: 66

Re: stymied on line chart

How though? I'm not sure how to use the equivilant of an autoexec.sas in VA to run a dynamic PROC FORMAT with a CTRLIN option that only runs if this one particular data set is loaded.

Michelle
Occasional Contributor
Posts: 13

Re: stymied on line chart

Posted in reply to mbuchecker

How are you loading the data at present -- is it autoloaded from the Excel file?

Contributor
Posts: 66

Re: stymied on line chart

Autoloaded as a SAS data set. But you did make me realize I have a bigger issue as that data set is created by a program that reads an Excel file, does some processing and spits out the data set. I've simplified it currently where I have manually created a structured Excel spreadsheet from the original unstructured one, but that won't be a good long-term solution. The customer may be able to give me the data in  a SAS data set, so if they do, then my problem goes back to dynamically putting the format on there.

Michelle
Occasional Contributor
Posts: 13

Re: stymied on line chart

Posted in reply to mbuchecker

There are a few ways you could go about it. If you can get to the code that's doing the import, you can create the format as part of the import process. If you don't mind getting your hands dirty, you could do that to the autoload routine (it's just SAS code that runs on a schedule), but this is prone to be overwritten if/when SAS ever updates that code in a patch. I would probably move the data file out of the autoload directory on the server and write a custom program to load it (and create the format) that runs however frequently you need.

 

Actually creating the format should not be too difficult. Just select out the distinct values of your column that contains the "Jul 13- Jul 19” type values, then chop up those strings and convert them to your "W01" values. Once you have a dataset that has the mapping for your format, you can use a "proc format cntlin" to convert that dataset into a format. I have some code around here somewhere that I can share if it will be helpful. 

 

You could also just run your format-creating code every 15 minutes or so, and have it fail nicely if the data set is not loaded (you can determine this from the _T_TABLEMEMORY table on your LASR server). You might even come up with some way to test to see if your format is outdated, rather than just rebuilding it every time...

Contributor
Posts: 66

Re: stymied on line chart

So according to Tech Support, Character columns in VA will always be sorted by the formatted value if a format exists. I cannot create a variable whose value is W01, W02...W82, and use a format of Jun 6 - Jun 13, Jun 14 - Jun 20, etc. as it will still then sort alphabetically by formatted value, not data value. 

 

Thanks for your help though Mike2468. 

Michelle
Super User
Posts: 3,252

Re: stymied on line chart

Posted in reply to mbuchecker

If you prefixed your format with W01, W02 etc that would sort in the right order but would look ugly...

Solution
‎02-21-2017 01:01 PM
Contributor
Posts: 66

Re: stymied on line chart

With the help of tech support we finally got it working. The key is to create a NUMERIC variable 1 thru 82, then create and apply a user-defined format that re-codes 1 to 'Aug 1- Aug 6', etc. Then put that format library in a place where the server can see it, in my case Lev1\SASApp\SASEnvirnment\SASFormats.  I was lazy and named the catalog "formats" since that's what the SAS Config file was already looking for. 

 

When this numeric variable is brought into VA it is automatically treated as a Category and sorted on by the formatted value. 

Michelle
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 301 views
  • 1 like
  • 3 in conversation