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

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

JonDickens1607 — Fri, 13 Jan 2017 14:08:20 GMT

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

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

RW9 — Fri, 13 Jan 2017 14:24:58 GMT

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;

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

gamotte — Fri, 13 Jan 2017 15:23:29 GMT

Hello,

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

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

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

JonDickens1607 — Fri, 13 Jan 2017 15:26:21 GMT

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

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

JonDickens1607 — Fri, 13 Jan 2017 15:27:29 GMT

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

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

gamotte — Fri, 13 Jan 2017 15:30:34 GMT

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

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

ballardw — Fri, 13 Jan 2017 16:08:39 GMT

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.

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

JonDickens1607 — Sat, 14 Jan 2017 00:18:59 GMT

THANK YOU TO ALL THE PEOPLE WHO RESPONDED TO MY QUESTION

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

art297 — Sat, 14 Jan 2017 23:29:10 GMT

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

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

JonDickens1607 — Sat, 14 Jan 2017 23:42:42 GMT

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

Regards

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

art297 — Sat, 14 Jan 2017 23:47:53 GMT

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

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

PraviinSNegii — Wed, 21 Mar 2018 08:39:42 GMT

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;