BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
robertrao
Quartz | Level 8

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

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

6 REPLIES 6
Reeza
Super User

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)

robertrao
Quartz | Level 8

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

Miracle
Barite | Level 11

Hi Karun. Try the "if" command.

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

robertrao
Quartz | Level 8

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

RichardinOz
Quartz | Level 8

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

joehinson
Calcite | Level 5

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

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
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
  • 6 replies
  • 2057 views
  • 7 likes
  • 5 in conversation