Write and run SAS programs in your web browser

Displaying the Columns of a dataset in alphabetical order

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 80
Accepted Solution

Displaying the Columns of a dataset in alphabetical order

I am new to SAS Studio (less than a month now),   What I want to do while viewing a dataset in SAS Studio is what I can do currently in the regular Display Manager.   That is, to open a dataset and the click on 'Data' and then click 'Hide/Unhidle' - send all the variables over to the hidden box and I can view all the variables in alphabetical order.  I can now scan down the list to view the one(s) I want and put them onto the Displayed side.  This helps so much because we can have hundreds of variables in a dataset.

 

Is there any way to do this same thing in SAS Studio?

 

Thanks -  Nancy


Accepted Solutions
Solution
2 weeks ago
SAS Employee
Posts: 263

Re: Displaying the Columns of a dataset in alphabetical order

This macro may be helpful to you.  It creates a view of the data with the columns alphabetically arranged:

 

%macro SortColumns(data=sashelp.class);

%local COLUMNS
       LIBRARY
       TABLE;

%*TODO: Add code to enforce valid 2-level name for DATA;

data _null_;
call symputx('LIBRARY', scan("&DATA", 1, '.'));
call symputx('TABLE',   scan("&DATA", 2, '.'));
run;

proc sql noprint;
  select name into: COLUMNS separated by ', '
  from sashelp.vcolumn
  where (libname eq upcase("&LIBRARY") and 
         memname eq upcase("&TABLE")   and 
         memtype eq 'DATA')
  order by upcase(name);
  
  create view work.&TABLE as
  select &COLUMNS
  from &DATA;
quit;

%mend SortColumns;

 

When you run it from SAS Studio the output is opened in the grid view:

 

%SORTCOLUMNS(data=sashelp.classfit)

 

I tried to get this code working within a SAS Studio Task, but limitations in the task model prevented the data from being automatically opened.

 

Vince DelGobbo

SAS R&D

View solution in original post


All Replies
SAS Employee
Posts: 263

Re: Displaying the Columns of a dataset in alphabetical order

Can you provide more details on how you are able to see the columns in alphabetical order in the ViewTable Hide/Unhide window?  I see them listed by their the logical position in the SAS data set.

 

For example, here is what I see for SASHELP.CLASS:

 

HideUnhide.png

 

Vince DelGobbo

SAS R&D

 

SAS Employee
Posts: 263

Re: Displaying the Columns of a dataset in alphabetical order

I think I see how you got the alphabetical list; move all columns to hidden and then they are displayed alphabetically:

 

HideUnhide2.png

 

Vince DelGobbo

SAS R&D

Grand Advisor
Posts: 9,748

Re: Displaying the Columns of a dataset in alphabetical order


Vince_SAS wrote:

I think I see how you got the alphabetical list; move all columns to hidden and then they are displayed alphabetically:

 

HideUnhide2.png

 

Vince DelGobbo

SAS R&D


I would swear I made a variable selection tool like this with SAS/AF back around 1995 only my users had a need to select varaibles from multiple data sets as well...

Frequent Contributor
Posts: 80

Re: Displaying the Columns of a dataset in alphabetical order

If you click the double (left) arrow, they all go over to the Hidden side and are in alphabetical order now. 

Now i can scan down the list and put back what I want to be displayed.

 

 

 

Frequent Contributor
Posts: 80

Re: Displaying the Columns of a dataset in alphabetical order

Yes - Is there any way to do that in SAS Studio in the Columns area, when you have dataset displayed?  Myself and others could really use this feature.

 

Thanks

SAS Employee
Posts: 263

Re: Displaying the Columns of a dataset in alphabetical order

I do not see an easy way to do this but I am checking into it.  You can drag-and-drop columns in both the navigation tree and the grid, but that is only useful if you don't have too many columns.

 

Vince DelGobbo

SAS R&D 

Frequent Contributor
Posts: 80

Re: Displaying the Columns of a dataset in alphabetical order

Well this option would be a great enhancement to the work area of SAS Studio.  If I open a dataset, it shows me the Columns and all the rows of the dataset.  In the View Columns section if there was something next to the word 'Columns' that said 'Alpha' (for alphabetize) that would be a great help.  I did notice that when I click on the Table Properties icon and then click on the Columns tab, I can click the 'Column Name' header and it will alphabetize the columns.  That is what I need on the 'Select all' section under Columns. 

 

Cheers!

SAS Employee
Posts: 28

Re: Displaying the Columns of a dataset in alphabetical order

The ability to do this is on the roadmap for a future release of SAS Studio. Attached is a sneak peek at the preliminary design.

Attachment
Frequent Contributor
Posts: 80

Re: Displaying the Columns of a dataset in alphabetical order

Awesome!
Solution
2 weeks ago
SAS Employee
Posts: 263

Re: Displaying the Columns of a dataset in alphabetical order

This macro may be helpful to you.  It creates a view of the data with the columns alphabetically arranged:

 

%macro SortColumns(data=sashelp.class);

%local COLUMNS
       LIBRARY
       TABLE;

%*TODO: Add code to enforce valid 2-level name for DATA;

data _null_;
call symputx('LIBRARY', scan("&DATA", 1, '.'));
call symputx('TABLE',   scan("&DATA", 2, '.'));
run;

proc sql noprint;
  select name into: COLUMNS separated by ', '
  from sashelp.vcolumn
  where (libname eq upcase("&LIBRARY") and 
         memname eq upcase("&TABLE")   and 
         memtype eq 'DATA')
  order by upcase(name);
  
  create view work.&TABLE as
  select &COLUMNS
  from &DATA;
quit;

%mend SortColumns;

 

When you run it from SAS Studio the output is opened in the grid view:

 

%SORTCOLUMNS(data=sashelp.classfit)

 

I tried to get this code working within a SAS Studio Task, but limitations in the task model prevented the data from being automatically opened.

 

Vince DelGobbo

SAS R&D

Frequent Contributor
Posts: 80

Re: Displaying the Columns of a dataset in alphabetical order

Vince,

 

Thank you very much.  That works perfectly for what we need.  We have saved this macro to our drive and can now alphabetize any dataset in SAS Studio. 

 

But for a future release of SAS Studio, if there is that option in the Column layout to just click a button to alphabetize the columns so the search is easier - that would be great. 

 

SAS is the best! 

 

Cheers,

Nancy

Post a Question
Discussion Stats
  • 11 replies
  • 323 views
  • 8 likes
  • 4 in conversation