BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
OS2Rules
Obsidian | Level 7

Hi All:

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.

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

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 ***;

data _forview;

length name $32;

do dsid=open("&dsn",'I') while(dsid ne 0);

   do i=1 to attrn(dsid,'NVARS');

     name=upcase(varname(dsid,i));

     output;

   end;

   dsid=close(dsid);

end;

run;

proc sql noprint;

select name into :names

   separated by ' '

     from _forview

       order by name

;

quit;

proc delete data=_forview;

run;

proc fsview data=&dsn;

var &names.;

run;

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.

View solution in original post

18 REPLIES 18
data_null__
Jade | Level 19

Is this easy enough?

proc fsview data=sashelp.class;

   id age;

   var height--weight _char_;

   run;

art297
Opal | Level 21

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

      from dictionary.columns

        where libname="SASHELP" and

          memname="CLASS"

        order by name

;

quit;

proc fsview data=sashelp.class;

  var &vars.;

run;

OS2Rules
Obsidian | Level 7

data_null_:

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:

     FSVIEW tablename

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.

data_null__
Jade | Level 19

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.

SASKiwi
PROC Star

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.

art297
Opal | Level 21

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.

ChrisNZ
Tourmaline | Level 20

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

> end                  

> fsview sashelp.class work.t

Now the formula work.t.class.formula is used and age doesn't show.

PS Good avatar!  Smiley Happy

OS2Rules
Obsidian | Level 7

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

art297
Opal | Level 21

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.

OS2Rules
Obsidian | Level 7

Art:

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.

art297
Opal | Level 21

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?

ChrisNZ
Tourmaline | Level 20

I know you dislike viewtable, but it does an alphabetic reorder in 5 clicks.

Data > Hide/Unhide > All left > All right > OK.

art297
Opal | Level 21

Chris,

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.

OS2Rules
Obsidian | Level 7

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. 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 18 replies
  • 3423 views
  • 1 like
  • 5 in conversation