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

Hi, 

 

I am wondering if there is any way to sort variables by type? For example, all numeric variables are appear first, and then comes categorical variables. The only thing I think that MAY work is using proc sql, but I am not too familiar with that. Any help or suggestions would be greatly appreciated. Thank you!

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

If I correctly understand what you are trying to do, then the following would work:

 

proc sql;
  select name into :reorder separated by ' '
    from dictionary.columns
      where libname=upcase('sashelp') and
            memname=upcase('class')
        order by type descending
  ;
quit;

data want;
  retain &reorder.;
  set sashelp.class;
run;

Art, CEO, AnalystFinder.com

 

View solution in original post

15 REPLIES 15
Rick_SAS
SAS Super FREQ

there is a way, but to what purpose?  In most SAS procedures you can use syntax to run on all numeric (or character) variables, regardless of how they are ordered.  What is the ultimate problem that you are trying to solve?

nehalsanghvi
Pyrite | Level 9
Where do you want them sorted (by sorted, I am assuming you mean arranged)? In a certain dataset?
corkee
Calcite | Level 5

@Rick_SAS I am using arrays to help clean very large datasets, and from what I know, arrays in SAS can only hold variables of the same type. The macro we have right now requires us to "alternate" once there's a change in variable type. So, we're looking to see if it's at all possible to group variables of the same type together.

 

@nehalsanghvi Yup, just arranged by type.

Rick_SAS
SAS Super FREQ

I suggest you look into using the _NUMERIC_ and _CHARACTER_ keywords when you define the arrays.

For example

 

array NumerVars[*] _NUMERIC_;
array CharVars[*] _CHARACTER_;
corkee
Calcite | Level 5
@Rick_SAS The macros we have take a "range" of consecutive variables (e.g., firstvar--lastvar). Not sure if I understand how to use your logic here.
Rick_SAS
SAS Super FREQ

A basic principle in efficient data analysis is to not rearrange the data unless necessary. If you say more about what sort of "data cleaning" you are interested in, we can provide specific suggestions.

 

For example, if you want to drop observations for which any variable is missing and also want to drop observations for which any numerical variable has a negative value, you can say:

 

data Want;
set sashelp.Heart;
array NumerVars[*] _NUMERIC_;
array CharVars[*] _CHARACTER_;
if ^CMISS(of NumerVars[*], of CharVars[*]) &  /* omit any missing */
   min(of NumerVars[*]) > 0;                  /* omit any negative values */
run;

 

ballardw
Super User

@corkee wrote:
@Rick_SAS The macros we have take a "range" of consecutive variables (e.g., firstvar--lastvar). Not sure if I understand how to use your logic here.

You should provide some more details. Arrays can be defined with lists such that when the variables have the same stem you just indicate either the start and end: Thisvar1 - ThisVar10 or ThisVar:  with the : indicating to assign all variables whose names start with ThisVar to the array. Neither of these require the variables to be in any specific order/location in the data vector. If your macro is not using the array -- range construct (array somearray thisvar -- anothervarname;) explicitly then you may be misunderstanding the requirement.

 

If your macro requires adjacent variables by order in the data vector then 1) they are so fragile they need to be rewritten or 2) the concept behind the data structure needs to be addressed. What you are describing (need to re order variables in the data vector) seems to indicate that you have an existing data structure that keeps adding variables (at the end) of the data. Which is indicative of a poorly designed approach.

 

 

nehalsanghvi
Pyrite | Level 9

Take a look at metadata available in dictionary.columns. You can use this to pick up only the columns of a single datatype:

proc sql;
select name into :numvarlist separated by ' '
from dictionary.columns
where libname='WORK'
and memname='YOURTBL'
and type='num';
quit;
corkee
Calcite | Level 5
I will try this once I head to work in a bit. Will let you know how it goes--thank you so much.
rogerjdeangelis
Barite | Level 11
Rearranging the order of the PDV putting numerics before character variables

inspired by
https://goo.gl/B3yGv9
https://communities.sas.com/t5/Base-SAS-Programming/Sorting-variables-by-type/m-p/337829


HAVE
====

Up to 40 obs from sashelp.class total obs=19

Obs    NAME       SEX    AGE    HEIGHT    WEIGHT

  1    Alfred      M      14     69.0      112.5
  2    Alice       F      13     56.5       84.0
  3    Barbara     F      13     65.3       98.0
  4    Carol       F      14     62.8      102.5
  5    Henry       M      14     63.5      102.5
  6    James       M      12     57.3       83.0
...

WANT
====

Up to 40 obs from want total obs=19

Obs    AGE    HEIGHT    WEIGHT    NAME       SEX

  1     14     69.0      112.5    Alfred      M
  2     13     56.5       84.0    Alice       F
  3     13     65.3       98.0    Barbara     F
  4     14     62.8      102.5    Carol       F
  5     14     63.5      102.5    Henry       M
  6     12     57.3       83.0    James       M

WORKING CODE
============

    DOSUBL
      array num _numeric_;
      chrs=catx(' ',chrs,vname(chr[i]));
      call symputx("retnum",nums);

      retain &retnum;

FULL SOLUTION
=============

%symdel retnum; * just in case it exists;
data want;

 * get meta data;
 if _n_ =0 then do;

   %let rc=%sysfunc(dosubl('

    data _null_;
      set sashelp.class(obs=1);
      array num _numeric_;
      length nums $4096;
      do i=1 to dim(num);
         nums=catx(' ',nums,vname(num[i]));
      end;
      call symputx("retnum",nums);
    run;quit;

      '));
  end;

    retain &retnum;
    set sashelp.class;

  run;quit;

run;quit;

art297
Opal | Level 21

If I correctly understand what you are trying to do, then the following would work:

 

proc sql;
  select name into :reorder separated by ' '
    from dictionary.columns
      where libname=upcase('sashelp') and
            memname=upcase('class')
        order by type descending
  ;
quit;

data want;
  retain &reorder.;
  set sashelp.class;
run;

Art, CEO, AnalystFinder.com

 

corkee
Calcite | Level 5

@art297 This did the trick. However, is there a way to sort the variables, after sorting them by type, in alphabetical order? Thank you so much.

nehalsanghvi
Pyrite | Level 9
proc sql;
select name into :varlist separated by ' '
from dictionary.columns
where libname='WORK'
and memname='YOURTBL'
order by type, name;
quit;

Add the name column to the order by statement

 

corkee
Calcite | Level 5
Got it. I just played around and eventually figured it out. THANK YOU.

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 15 replies
  • 2626 views
  • 4 likes
  • 6 in conversation