BookmarkSubscribeRSS Feed
JonDickens1607
Obsidian | Level 7

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

11 REPLIES 11
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Obsidian | Level 7
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
Rhodochrosite | Level 12

Hello,

 

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

 

data want_num(keep=_NUMERIC_) want_char(keep=_CHARACTER_);
    set have;
run;
JonDickens1607
Obsidian | Level 7

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
Rhodochrosite | Level 12
Can you provide SAS code that generates an example of have and want datasets ?
ballardw
Super User

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
Obsidian | Level 7

THANK YOU TO ALL THE PEOPLE WHO RESPONDED TO MY QUESTION

art297
Opal | Level 21

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
Obsidian | Level 7

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

 

Regards

art297
Opal | Level 21

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
Calcite | Level 5

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;

 

 

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
  • 11 replies
  • 15640 views
  • 3 likes
  • 6 in conversation