topic Subset data set in SAS Programming

Subset data set

Ronein — Wed, 17 Mar 2021 07:06:38 GMT

Hello

I want to select rows with following criteria:

1- start with 'ttt'

2- name structure is : ttt+8 digits

3-Length is 11

What is the correct way to do it?


data a;
input tblName: $12.;
datalines;
ttt01032021
ttt08032021
ttt17022021
ttt21022021
tttabcdefgh
tttttttttttt
ttt1
tttAbc
ABCttt
t1
t2
t3
;
Run;

PROC SQL;
	create table wanted  as
	select  *	   
	from  a
	where tblName like 'ttt%'
	and prxmatch('/ttt\d{11}/i',tblName)
;
QUIT;

Re: Subset data set

japelin — Wed, 17 Mar 2021 07:17:35 GMT

You can simply handle it with the following data steps, but do you want to use prxmatch?

data want;
  set a;
  if index(tblName,'ttt')=1 and
     input(subpad(tblName,4,8),??best.)>0 and
     length(tblName)=11;
run;

Re: Subset data set

PeterClemmensen — Wed, 17 Mar 2021 07:20:00 GMT

@Ronein Try this

data a;
input tblName: $12.;
datalines;
ttt01032021
ttt08032021
ttt17022021
ttt21022021
tttabcdefgh
tttttttttttt
ttt1
tttAbc
ABCttt
t1
t2
t3
;

data b;
   set a;
   where prxmatch("/^ttt\d{8}\s*$/", tblName);
run;

Re: Subset data set

Ronein — Wed, 17 Mar 2021 07:30:59 GMT

Thank you,

Using the following condition is giving a warning : input(subpad(tblName,4,8),best.)>0

I think that it happens because in some rows it try to covert to numeric a char.

What is the way to find a better code that have no warning?


PROC SQL;
	create table wanted  as
	select tblName,subpad(tblName,4,8) as x	   
	from  a
	where tblName like 'ttt%' 
	and length(tblName)=11
	and input(subpad(tblName,4,8),best.)>0
 ;
QUIT;

Re: Subset data set

japelin — Wed, 17 Mar 2021 07:42:54 GMT

To prevent the input function from throwing a warning, I used ??best. informat.

However, you can't use ?? in where clause.

Re: Subset data set

PeterClemmensen — Wed, 17 Mar 2021 07:42:10 GMT

@Ronein did you try my code?

Re: Subset data set

Ksharp — Wed, 17 Mar 2021 11:03:05 GMT

data a;
input tblName: $12.;
datalines;
ttt01032021
ttt08032021
ttt17022021
ttt21022021
tttabcdefgh
tttttttttttt
ttt1
tttAbc
ABCttt
t1
t2
t3
;
Run;

PROC SQL;
	create table wanted  as
	select  *	   
	from  a
	where tblName like 'ttt%'
	and prxmatch('/^ttt\d{8}$/i',strip(tblName))
;
QUIT;