How to extract a specified values from the data with a common value?

[RAVI2000]

I have a variable which has values like below;

CODE

C50.012

Z180

H05.23

C90.00

I90

C50.011

C50.111

284.19

K59.09

C50.112

C50.119

Z89.09

C50.211

C50.212

C50.311

How can i extract all the values that start with C50 from that code variable ?

[jimbarbour]

Have you tried using the INDEX() function?

Something like the below program which yields:

Jim

DATA	Have;
	INPUT	CODE	$;
DATALINES;
C50.012
Z180
H05.23
C90.00
I90
C50.011
C50.111
284.19
K59.09
C50.112
C50.119
Z89.09
C50.211
C50.212
C50.311
;
RUN;

DATA	Want;
	SET	Have;
	IF	INDEX(Code, 'C50');
RUN;

[RAVI2000]

I am trying to filter the data in the WHERE condition statement.

[jimbarbour · Accepted solution]

OK.  No problem.  How about the below code?  Same (identical) result but using a WHERE clause.

Jim

DATA	Have;
	INPUT	CODE	$;
DATALINES;
C50.012
Z180
H05.23
C90.00
I90
C50.011
C50.111
284.19
K59.09
C50.112
C50.119
Z89.09
C50.211
C50.212
C50.311
;
RUN;

/*DATA	Want;*/
/*	SET	Have;*/
/*	IF	INDEX(Code, 'C50');*/
/*RUN;*/

PROC	SQL;
	CREATE	TABLE	Want	AS
		SELECT	Code
			FROM	Have
				WHERE	INDEX(Code, 'C50')
				;
QUIT;

[RAVI2000]

Can we give multiple values here? For eg, I also want to filter values starting with "H" "C56" etc., ?

[jimbarbour]

Yes.  Just use an OR to add additional uses of the INDEX function.

Like this:

PROC	SQL;
	CREATE	TABLE	Want	AS
		SELECT	Code
			FROM	Have
				WHERE	INDEX(Code, 'C50')
					OR	INDEX(Code, 'C56')
					OR	INDEX(Code, 'H')
				;
QUIT;

Jim

[novinosrin]

data have;
  input   CODE :$10.;
  cards;
C50.012

Z180

H05.23

C90.00

I90

C50.011

C50.111

284.19

K59.09

C50.112

C50.119

Z89.09

C50.211

C50.212

C50.311
;

data want;
  set have;
  where scan(code,1,'.')='C50';
 run;

[Reeza]

WHERE CODE IN: ("C50", "H", "Z");

Use the colon operator and IN.

---

@RAVI2000 wrote:

I have a variable which has values like below;

CODE

C50.012

Z180

H05.23

C90.00

I90

C50.011

C50.111

284.19

K59.09

C50.112

C50.119

Z89.09

C50.211

C50.212

C50.311

How can i extract all the values that start with C50 from that code variable ?

---

[jimbarbour]

@RAVI2000, 

Take a look at what @Reeza is suggesting.  That's an improvement on my multiple values example.

Adding it all together:

PROC	SQL;
	CREATE	TABLE	Want	AS
		SELECT	Code
			FROM	Have
				WHERE	Code  IN:  ('C50', 'C56', 'H')
				;
QUIT;

Jim

[jimbarbour]

@Reeza 

I can't get the colon operator to work with IN in my SQL.  Doing a quick Google, I found https://support.sas.com/resources/papers/proceedings09/056-2009.pdf which I have excerpted, below.

Apparently no such luck with SQL.  Any other suggestions or does one just have to list the conditions one by one as in my first SQL example?

Jim

[ballardw]

---

@jimbarbour wrote:

@Reeza 

I can't get the colon operator to work with IN in my SQL.  Doing a quick Google, I found https://support.sas.com/resources/papers/proceedings09/056-2009.pdf which I have excerpted, below.

Apparently no such luck with SQL.  Any other suggestions or does one just have to list the conditions one by one as in my first SQL example?

Jim

---

If it isn't in ANSI SQL SAS is unlikely to implement it Proc SQL.

I think about the only exception to that may be some of the functions, but base syntax like comparison operators not in Proc SQL.

Is there any real reason not to use a Data step instead of Proc SQL?

Perhaps another table with the distinct "before the decimal" code values wanted from the data and then join on that table with something like a.distinctcode = scan(fulldata.code,1,'.')

[RAVI2000]

like% function would work with it.

[Ksharp]

data want;
set have;
if code=:'C50';
run;