Help using Base SAS procedures

SAS Dataset Options

Accepted Solution Solved
Reply
Super Contributor
Posts: 358
Accepted Solution

SAS Dataset Options

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.


Accepted Solutions
Solution
‎08-17-2011 08:59 AM
PROC Star
Posts: 7,474

Re: SAS Dataset Options

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


All Replies
Respected Advisor
Posts: 3,799

SAS Dataset Options

Is this easy enough?

proc fsview data=sashelp.class;

   id age;

   var height--weight _char_;

   run;

PROC Star
Posts: 7,474

SAS Dataset Options

Posted in reply to data_null__

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;

Super Contributor
Posts: 358

SAS Dataset Options

Posted in reply to data_null__

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.

Respected Advisor
Posts: 3,799

SAS Dataset Options

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.

Super User
Posts: 3,254

Re: SAS Dataset Options

Posted in reply to data_null__

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.

PROC Star
Posts: 7,474

Re: SAS Dataset Options

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.

PROC Star
Posts: 1,760

Re: SAS Dataset Options

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

Super Contributor
Posts: 358

Re: SAS Dataset Options

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

PROC Star
Posts: 7,474

Re: SAS Dataset Options

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.

Super Contributor
Posts: 358

Re: SAS Dataset Options

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.

PROC Star
Posts: 7,474

Re: SAS Dataset Options

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?

PROC Star
Posts: 1,760

Re: SAS Dataset Options

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

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

PROC Star
Posts: 7,474

Re: SAS Dataset Options

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.

Super Contributor
Posts: 358

Re: SAS Dataset Options

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. 

🔒 This topic is solved and locked.

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

Discussion stats
  • 18 replies
  • 1271 views
  • 0 likes
  • 5 in conversation