Help using Base SAS procedures

How to select observation based on a column's number format (PROC SQL)

Reply
N/A
Posts: 0

How to select observation based on a column's number format (PROC SQL)

In the data set, there are unique observations, identified by a number in the ID column. Some observations are subsets of a main one, and they are designated with a decimal. (The column format is BEST12. and the length is 8).

A schematic of the data set looks like:

ID Data1 Data2 Data3
1
2
3
3.1
4
5
6
6.1
6.2
6.3
7
8
9
10
10.1
10.2

I want to run a PROC SQL statement to count certain observations, but I don't want to include any of the "child" observations (i.e. I only want to select those in the column that are whole numbers; none that have decimal places).

I'm not sure how to write a general statement to do this. How do I indicate to not include anything from the ID column that has a decimal point (or a number after the decimal)? I can't call for only integers, given that it's a decimal column, right?

Any insight would be greatly appreciated.
Frequent Contributor
Posts: 106

Re: How to select observation based on a column's number format (PROC SQL)

Please specify: would you drop an observation having a value of 5.0 ?
If not then just compare the value to its integer value (using function int() for example) and drop if unequal.
N/A
Posts: 0

Re: How to select observation based on a column's number format (PROC SQL)

Of course! That's a great solution. Thanks for suggesting it.
Valued Guide
Posts: 2,168

Re: How to select observation based on a column's number format (PROC SQL)

are you able to filter like
where mod( ID,1 ) = 0
the MOD() function provides the "remainder" after "dividing" ID by the second parameter, so will return 0 when ID is an integer.
Here is a test on the syntax [pre]650 proc sql FEEDBACK ;
651 create table ints as select * from sashelp.class where mod(age,1) = 0;
NOTE: Statement transforms to:

select CLASS.Name, CLASS.Sex, CLASS.Age, CLASS.Height, CLASS.Weight
from SASHELP.CLASS
where MOD(CLASS.Age, 1) = 0;

NOTE: Table WORK.INTS created, with 19 rows and 5 columns.

652 create table nint as select * from sashelp.class where mod(age,1) ne 0;
NOTE: Statement transforms to:

select CLASS.Name, CLASS.Sex, CLASS.Age, CLASS.Height, CLASS.Weight
from SASHELP.CLASS
where MOD(CLASS.Age, 1) not = 0;

NOTE: Table WORK.NINT created, with 0 rows and 5 columns.

653 quit;
NOTE: PROCEDURE SQL used[/pre]
looks like what you need
Ask a Question
Discussion stats
  • 3 replies
  • 75 views
  • 0 likes
  • 3 in conversation