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

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

deleted_user — Mon, 21 Feb 2011 03:49:51 GMT

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.

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

Robert_Bardos — Mon, 21 Feb 2011 07:35:10 GMT

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.

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

deleted_user — Mon, 21 Feb 2011 20:52:50 GMT

Of course! That's a great solution. Thanks for suggesting it.

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

Peter_C — Wed, 23 Feb 2011 17:09:32 GMT

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