topic Re: subset a numeric variable in SAS Programming

subset a numeric variable

robertrao — Fri, 16 Nov 2012 01:56:11 GMT

Hi Team,

I have a numeric variable ID and have various codes. I want to pick specific codes. How can I do that???

I know if it is char variable I would have to use where ID in(......)

Please help me do that for numeric variable

ID(numeric)

101

102

103

104

105

106

107

108

109

I want to pick only 101, 104 and 106 from the ID variable above.....

Thanks

Re: subset a numeric variable

Reeza — Fri, 16 Nov 2012 02:34:13 GMT

YOu can use IN with numeric variables as well, just don't put it in quotes.

ie where id in (101, 104, 106)

or where id in (101 104 106)

Re: subset a numeric variable

robertrao — Fri, 16 Nov 2012 02:41:00 GMT

Hi Reeza ,

Thanks for the reply.

When i use like that I am getting observations with first mentioned ID. in our case 101.....i am not getting 104 and 106 at all,,,

lIKEWISE IF i REMOVE 101 FROM THE IF STMNT THEN I GET SOME OBSERVATIONS WHICH ARE 104 SINCE IT BECOMES FIRST OF THE LIST NOW!!!!1

Regards

Re: subset a numeric variable

joehinson — Fri, 16 Nov 2012 02:55:57 GMT

if (id=101) or (id=104) or (id=106);

Re: subset a numeric variable

Miracle — Fri, 16 Nov 2012 02:56:25 GMT

Hi Karun. Try the "if" command.

i.e. data want; set rawdata; if ID in(101,104,106); run;

Re: subset a numeric variable

robertrao — Fri, 16 Nov 2012 03:04:45 GMT

Hi,

I am very Sorry, I got it with Reezas Where statement.........

The only reason i dint judge properly was that the dataset has so many observations and felt that as far as i went there was only 101.

Later i did

where ID ne 101 for that dataset an dI found other ID,s as well

Great Help

Thanks

Re: subset a numeric variable

RichardinOz — Fri, 16 Nov 2012 06:18:49 GMT

Karun

Let's take the problem back a little. How do you get your initial list of IDs to search for? Maybe they are in a list or a table somewhere, or you have some interactive code that can create or update a list. Or perhaps the list comes out of some previous selection analysis. Assuming you have this list in a SAS table called have, there are 3 options to consider (leaving aside fancy hash code and the like)

Go with the IN( ) statement but automate the ID list creation; but there is a limit to the number of IDs you can stuff into an IN( ) selection:

Proc SQL NoPrint ;

Select cats(Id)

into :ID_List

separated by ','

From have

;

Create table want as

Select *

from big_data_table

where ID in (&ID_List)

;

Quit ;

Create a disposable format and use it in the where clause; but this is not the optimal solution if your big_data_table is in a SAS Access library

proc sql ;

create table ID_List as

Select distinct

ID as start

, 'OK' as label

, 'ID_List' as fmtname

From have

;

quit ;

Proc format cntlin = ID_List ;

quit ;

proc sql ;

Create table want as

Select *

from big_data_table

where put(ID, ID_List.) = 'OK'

;

Quit ;

If your big_data_table is in a SAS Access library and you have many IDs to select from and can copy your have table to the same library then consider a SQL join

proc sql ;

Create table want as

Select big.*

from big_data_table big

, have lst

where big.ID = lst.ID

;

Quit ;

Richard in Oz