topic Re: How to extract a specified values from the data with a common value? in SAS Programming

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

RAVI2000 — Thu, 05 Aug 2021 22:41:13 GMT

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 ?

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

jimbarbour — Thu, 05 Aug 2021 22:47:29 GMT

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;

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

RAVI2000 — Thu, 05 Aug 2021 22:44:38 GMT

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

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

jimbarbour — Thu, 05 Aug 2021 22:50:16 GMT

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;

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

RAVI2000 — Thu, 05 Aug 2021 22:52:54 GMT

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

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

novinosrin — Thu, 05 Aug 2021 22:53:40 GMT

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;

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

jimbarbour — Thu, 05 Aug 2021 22:56:42 GMT

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

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

Reeza — Thu, 05 Aug 2021 22:57:56 GMT

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 ?

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

jimbarbour — Thu, 05 Aug 2021 23:01:11 GMT

@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

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

jimbarbour — Fri, 06 Aug 2021 00:08:41 GMT

@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

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

ballardw — Fri, 06 Aug 2021 00:39:29 GMT

@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,'.')

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

RAVI2000 — Fri, 06 Aug 2021 04:54:28 GMT

like% function would work with it.

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

Ksharp — Fri, 06 Aug 2021 12:38:36 GMT

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