How can I use PROC SQL to Select all the NUMERIC Columns in a Table

[JonDickens1607]

Using SAS 9.4

I need to split my data set into two subsets

a. All Numeric Variables

b. All Character variables

How can I use PROC SQL to Select all the NUMERIC Columns in a Table?

Alternatively how would I do this in Base SAS?

Thanks

[RW9]

Its an odd question.  Why would you want to do this?  Even if there is a reason surely you would want some sort of identifiers common to both?

Anyways, you can do it, I don't sugest for a moment you should however:

proc sql noprint;
  select  distinct NAME 
  into    :NUM_LIST separated by ","
  from    DICTIONARY.COLUMNS
  where   LIBNAME="SASHELP"
    and   MEMNAME="CLASS"
    and   TYPE="num";
  select  distinct NAME 
  into    :CHAR_LIST separated by ","
  from    DICTIONARY.COLUMNS
  where   LIBNAME="SASHELP"
    and   MEMNAME="CLASS"
    and   TYPE ne "num";
  create table WANT_NUM as
  select  &NUM_LIST.
  from    SASHELP.CLASS;
  create table WANT_CHAR as
  select  &CHAR_LIST.
  from    SASHELP.CLASS;
quit;

[JonDickens1607]

How can I split a data set into two subsets:

a. ID_Variable, All Numeric Variables

b. ID_Variable, All Character Variables

Thanks for your help.

Regards


##- Please type your reply above this line. Simple formatting, no
attachments. -##

[gamotte]

Hello,

For the "Alternatively..." part of your message

data want_num(keep=_NUMERIC_) want_char(keep=_CHARACTER_);
    set have;
run;

[JonDickens1607]

Thanks

My question should have been as follows:

How can I split a data set into two subsets:

a. ID_Variable, All Numeric Variables

b. ID_Variable, All Character Variables

Thanks for your help.

Regards

[gamotte]

Can you provide SAS code that generates an example of have and want datasets ?

[ballardw]

Is your ID_variable numeric or character? Assuming character then

Data character;

   set have (keep=_character_);

run;

Data numeric;

   set have (keep= Id_variable, _numeric_);

run;

would be easiest as the Id variable would be automatically selected with the character variables.

If numeric move the id variable.

[JonDickens1607]

THANK YOU TO ALL THE PEOPLE WHO RESPONDED TO MY QUESTION

[art297]

RW9 provided a SQL method in response to your original question, here is one for your revised problem:

proc sql;
  create table char as
    select *
      from sashelp.class (keep=_char_)
  ;

  create table num as
    select *
      from sashelp.class (keep=name _numeric_)
  ;
quit;

HTH,

Art, CEO, AnalystFinder.com

[JonDickens1607]

Thank you, there seems to be  a close relationship between the simple SQL solution and the Data Step solution.

Regards

[art297]

Of course there is! They're both using SAS. However, you asked for a solution using proc sql which is why I suggested it. Fortunately, with proc sql in SAS, one can use data set options when specifying a file in the "from" clause.

Art, CEO, AnalystFinder.com

[PraviinSNegii]

proc sql;

select type into :col separated by ' ' from sashelp.vcolumn

 /* here libname is library, WORK is the library, name is name of variable created, and, memname is name of dataset */

where libname = 'WORK' and type = 'char' and memname = 'TEST';

run;