DATA Step, Macro, Functions and more

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

Reply
Contributor
Posts: 59

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

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

Super User
Super User
Posts: 7,942

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

Posted in reply to JonDickens1607

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;
Contributor
Posts: 59

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

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. -##
Regular Contributor
Posts: 233

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

Posted in reply to JonDickens1607

Hello,

 

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

 

data want_num(keep=_NUMERIC_) want_char(keep=_CHARACTER_);
    set have;
run;
Contributor
Posts: 59

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

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
Regular Contributor
Posts: 233

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

Posted in reply to JonDickens1607
Can you provide SAS code that generates an example of have and want datasets ?
Super User
Posts: 11,343

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

Posted in reply to JonDickens1607

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.

 

 

 

Contributor
Posts: 59

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

Posted in reply to JonDickens1607

THANK YOU TO ALL THE PEOPLE WHO RESPONDED TO MY QUESTION

PROC Star
Posts: 7,468

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

Posted in reply to JonDickens1607

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

 

Contributor
Posts: 59

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

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

 

Regards

PROC Star
Posts: 7,468

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

Posted in reply to JonDickens1607

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

 

Ask a Question
Discussion stats
  • 10 replies
  • 1396 views
  • 1 like
  • 5 in conversation