stymied on line chart

[mbuchecker]

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!

[mike2468]

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

[mbuchecker]

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.

[mike2468]

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

[mbuchecker]

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.

[mike2468]

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...

[mbuchecker]

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. 

[SASKiwi]

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

[mbuchecker · Accepted solution]

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.