08-10-2011 10:36 AM
Quick question -
I have a data table with about a bajillion variables (I exagerate a bit)....
I like to use FSVIEW to see the content but I would like to see the variables in some sort of order (alphabetical?) in the FSVIEW display.
Is there a way to do this? A simple easy way???
Thanks in advance.
08-17-2011 08:59 AM
With a LOT of help from Randy Herbison (I cross posted the question on SAS-L), a solution was found that solved the original problem. It used the same method as the one described in the original reference I posted (i.e., http://support.sas.com/resources/papers/proceedings10/046-2010.pdf ), but gets around the 255 character limitation by gsubmit-ing the code with a %include statement.
Thus, the main code was saved to a text file with a .SAS extension. I called mine c:\viewfs.sas :
*** c:\viewfs.sas ***;
length name $32;
do dsid=open("&dsn",'I') while(dsid ne 0);
do i=1 to attrn(dsid,'NVARS');
proc sql noprint;
select name into :names
separated by ' '
order by name
proc delete data=_forview;
proc fsview data=&dsn;
That code, in turn, was %include-ed via the following gsubmit as the action command:
gsubmit "%%let dsn=%8b.%32b; %%include 'c:\viewfs.sas';"
FYI, the %%let dsn=%8b.%32b; at the beginning of that command is simply creating a macro variable called dsn which will contain the libname and memname of the SAS datafile that one right clicks on from the SAS Explorer window.
08-10-2011 11:29 AM
Of course you could always preface the call with a proc sql to create a macro variable. e.g.:
proc sql noprint;
select name into :vars
separated by " "
where libname="SASHELP" and
order by name
proc fsview data=sashelp.class;
08-10-2011 11:30 AM
It may be easy, but not exactly what I was looking for.
I'm using the FSVIEW command in "interactive mode" on a Windoze box - so I just enter:
on the command line to see the table contents. (I also don't know the names of the variable until after I view the table.)
What I was looking for was something like:
FSVIEW tablename option
It is like "double clicking" on the table name in the EXPLORER pane to get a VIEWTABLE (which also has the varibles out of order). I have actually modified my regristy so that the doing this will give me a FSVIEW rather than the much hated VIEWTABLE.
08-10-2011 11:37 AM
The FSVIEW command does not support procedure options such as BROWSEONLY and NOADD. You must use the PROC FSVIEW statement rather than the FSVIEW command if you want to invoke the procedure with these options, or if you want to establish default procedure characteristics with the FORMAT, ID, INFORMAT, VAR, or WHERE statements.
08-10-2011 06:50 PM
Simple if you only want to re-arrange one or two variables. Once in the FSVIEW window click View, Arrange Variables, Move. Very tedious if you have lots of variables though.....which I guess is your point.
08-10-2011 07:43 PM
Data_null, a number of others, and I wrote an SGF paper a couple of years ago that showed how to automate tasks and either assign them to keywords or function keys (see, http://support.sas.com/resources/papers/proceedings10/046-2010.pdf ).
While the topic wasn't FSView, you could do the same thing with the kind of sql+proc fsView code I proposed earlier in this thread. I.e., you could assign a function key that would allow you to select the desired file, obtain a macro variable of ordered variables, and apply the variable to a proc fsView run.
08-10-2011 08:12 PM
Have you tried the show command?
First drop variables, then show them
You could also define a formula. Type:
> fsview sashelp.class work.t
> drop age
> fsview sashelp.class work.t
Now the formula work.t.class.formula is used and age doesn't show.
PS Good avatar!
08-11-2011 08:49 AM
Thanks to all for you replies.
I thought that this was going to be simple, something that I overlooked.
A little background - the file in question is produced by a SQL that is run against a bunch of DB2 and VSAM tables on z/OS mainframe and then downloaded to our server. It has 450,00+ records and over 350 variables. This is only one of about 50+ files that I deal with. Since the files are so big I would rather not have to run a program to rebuild them.
My real problem is ad-hoc requests for data when someone asks for all records with some specific value. Since these people are not technical the request is "all cash transaction in US dollars". So - i need to find the variables in the table that contain these fields and believe me, there is no naming standard.
I was hoping I could see the variables in alphabeic order (such as in PROC CONTENTS) with the data (so I can see "sample" data). I could just scroll across and fiind the variable with the required data and then build a program to filter the data once I know which variables to use.
I was just hoping there would be some way to re-organize the data in the table so the variables are in sequence, rather than in the order they were created (first referenced).
08-11-2011 08:58 AM
As data_null pointed out, you can't do it via call fsview from the command prompt. However, as I mentioned in my post, you can easily have your cake and be able to eat it as well along with simplifying the keystrokes needed to accomplish the task. The suggested method doesn't rebuild the files but, rather, only accesses the dictionary view of the metadata.
08-11-2011 09:05 AM
Thanks - I will look into it (I'm reading your SGF paper).
I was really hoping there would be a "zero programming" solution ie: a parm or option to reorder the table variable list.
See you at TASS next month.
08-11-2011 03:40 PM
Since I am going to see you at TASS next month, I must know you, but your name doesn't appear on this forum. If you don't mind, please let me know who you are. You can send it to me, privately, at art at 297 dot rogers dot com.
However, I created what I was talking about. One creates it by clicking somewhere in the SAS explorer window,
and then clicking on tools->options->explorer,
then clicking on members->table->edit
then clicking on add
then, on the top line (labeled action) I named it FSView
then, on the bottom line (labeled action command), I entered the following long string:
gsubmit "proc sql noprint; select name into :vars separated by ' ' from dictionary.columns where libname=upcase('%8b') and memname=upcase('%32b') order by name;quit; proc fsview data=%8b.%32b; var &vars.;run;";
then I clicked on OK->OK->OK to exit the menu.
Once I did that, I can find any file in the SAS explorer window, right click on it, click on FSView and, behold, FSView appears with all of the variables sorted alphabetically.
Would that serve your purpose?
08-11-2011 08:02 PM
Try the solution I recommended. I don't know if the OP will like it, but it does the requested task in ONE click. It can also easily be generalized so that virtually any proc can be turned into a one-click solution.
08-12-2011 09:01 AM
I just tried your code - it is very close, but I have both lower-case and upper-case variable names. It sorts the lower-case first and then the upper-case. This is better than the seeming random list I have now, but ... call me a perfectionist.
Just FYI - I learned to hate dislike VIEWTABLE in SAS V7 (yes, I had one) where the VIEWTABLE would take 45 seconds (!!!) to open any table no matter whether it had 1 row or 1 million. Changed the default to FSVIEW and never looked back.
Need further help from the community? Please ask a new question.