DATA Step, Macro, Functions and more

subset a numeric variable

Accepted Solution Solved
Reply
Super Contributor
Posts: 1,041
Accepted Solution

subset a numeric variable

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


Accepted Solutions
Solution
‎11-15-2012 09:34 PM
Super User
Posts: 19,868

Re: subset a numeric variable

Posted in reply to robertrao

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)

View solution in original post


All Replies
Solution
‎11-15-2012 09:34 PM
Super User
Posts: 19,868

Re: subset a numeric variable

Posted in reply to robertrao

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)

Super Contributor
Posts: 1,041

Re: subset a numeric variable

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

Regular Contributor
Posts: 228

Re: subset a numeric variable

Posted in reply to robertrao

Hi Karun. Try the "if" command.

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

Super Contributor
Posts: 1,041

Re: subset a numeric variable

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

Super Contributor
Posts: 644

Re: subset a numeric variable

Posted in reply to robertrao

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

Contributor
Posts: 45

Re: subset a numeric variable

Posted in reply to robertrao

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 284 views
  • 7 likes
  • 5 in conversation