BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
RAVI2000
Lapis Lazuli | Level 10

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 ?

1 ACCEPTED SOLUTION

Accepted Solutions
jimbarbour
Meteorite | Level 14

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;

View solution in original post

12 REPLIES 12
jimbarbour
Meteorite | Level 14

Have you tried using the INDEX() function?

 

Something like the below program which yields:

jimbarbour_0-1628203632494.png

 

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
Lapis Lazuli | Level 10
I am trying to filter the data in the WHERE condition statement.
jimbarbour
Meteorite | Level 14

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
Lapis Lazuli | Level 10
Can we give multiple values here? For eg, I also want to filter values starting with "H" "C56" etc., ?
jimbarbour
Meteorite | Level 14

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
Tourmaline | Level 20
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
Super User
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
Meteorite | Level 14

@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
Meteorite | Level 14

@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.

jimbarbour_0-1628208424390.png

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
Super User

@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.

jimbarbour_0-1628208424390.png

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
Lapis Lazuli | Level 10
like% function would work with it.
Ksharp
Super User

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

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 12 replies
  • 1744 views
  • 10 likes
  • 6 in conversation