topic Re: SAS Dataset Options in SAS Procedures

SAS Dataset Options

OS2Rules — Wed, 10 Aug 2011 14:36:59 GMT

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.

SAS Dataset Options

data_null__ — Wed, 10 Aug 2011 15:20:47 GMT

Is this easy enough?

proc fsview data=sashelp.class;

id age;

var height--weight _char_;

run;

SAS Dataset Options

art297 — Wed, 10 Aug 2011 15:29:54 GMT

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;

SAS Dataset Options

OS2Rules — Wed, 10 Aug 2011 15:30:46 GMT

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.

SAS Dataset Options

data_null__ — Wed, 10 Aug 2011 15:37:57 GMT

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.

Re: SAS Dataset Options

SASKiwi — Wed, 10 Aug 2011 22:50:32 GMT

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.

Re: SAS Dataset Options

art297 — Wed, 10 Aug 2011 23:43:28 GMT

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.

Re: SAS Dataset Options

ChrisNZ — Thu, 11 Aug 2011 00:12:57 GMT

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!

Re: SAS Dataset Options

OS2Rules — Thu, 11 Aug 2011 12:49:37 GMT

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

Re: SAS Dataset Options

art297 — Thu, 11 Aug 2011 12:58:15 GMT

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.

Re: SAS Dataset Options

OS2Rules — Thu, 11 Aug 2011 13:05:49 GMT

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.

Re: SAS Dataset Options

art297 — Thu, 11 Aug 2011 19:40:18 GMT

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?

Re: SAS Dataset Options

ChrisNZ — Thu, 11 Aug 2011 21:38:21 GMT

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

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

Re: SAS Dataset Options

art297 — Fri, 12 Aug 2011 00:02:43 GMT

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.

Re: SAS Dataset Options

OS2Rules — Fri, 12 Aug 2011 13:01:45 GMT

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.

Re: SAS Dataset Options

art297 — Fri, 12 Aug 2011 13:57:39 GMT

I'm not sure if there is a way of specifying linguistic as the sort order for proc sql but, given what you want, couldn't you just change the sql order statement to: order by upcase(name)

Re: SAS Dataset Options

art297 — Mon, 15 Aug 2011 22:56:56 GMT

The OP informed me, offline, that the statement didn't work on his/her system. As such, I suggested another statement that didn't use dictionary.tables. I still don't know if that worked for the OP, but thought I'd post it here in case anyone else was trying out the method.

I should admit, up front, that any SAS instructors I've ever had would cringe at my code. Since one can only include 255 characters in an abbreviation, I had to remove as many characters as possible that would still allow the code to run as designed. In short, please, PLEASE, DON'T start writing your own code as shown below. The following is one long continuous line:

gsubmit "dat x;d='%8b'||'.'||'%32b';leng n$32;do s=open(d,'I')whil(s!0);do i=1to attrn(s,'NVARS');n=upcase(varname(s,i));outp;en;s=close(s);en;pro sql nopri;sele n into:n separa by' 'fro x order n;qui;pro delete dat=x;ru;pro fsvie dat=%8b.%32b;va &n;ru;";

Of course, setting the above code as an action command will produce a number of warnings in the log but, as it was only intended to bring up fsview, I thought that might still be acceptable.

Re: SAS Dataset Options

OS2Rules — Tue, 16 Aug 2011 12:13:04 GMT

Art:

I don't know why you are so worried - I writ my code like tht all the tim.

I've been in this business about 30 years, man and boy, and I have yet to meet anyone who can touch-type!

I sent you a private reply with the results of the code you provided (for the record - it didn't work quite as expected).

Thanks again for all the help - above and beyond.

Re: SAS Dataset Options

art297 — Wed, 17 Aug 2011 12:59:08 GMT

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.