topic Restricting proc sql to pull exact number of numeric character values from a column in SAS in SAS Programming

Restricting proc sql to pull exact number of numeric character values from a column in SAS

inquistive — Thu, 16 Dec 2021 17:43:06 GMT

Hi experts,
I have a dataset which has got 4 characters (alphanumeric) and 10 characters(numeric) ids in the SAME column.
I am trying to separate these into two data sets: one with 4 characters and another with 10 characters.
I need to join the data set with another to fetch other columns for analysis. The second dataset SEPARATE columns with 4 and 10 student ids. So I joined the 4 character student id from first table with 4 character column from second table easily.
But joining two tables to pull the 10 character student id has been a challenge to me.
How to tell sql to pull the student id (from student1) that has got exactly 10 (numeric) characters so that it could be joined on b.stdid (which has 10 numbers)?
```
proc sql;
create table ids as
select
a.id,
b.stdid,
b.lname,
b.fname,
b.mname,
b.subj,
b.stream,
b.gender
from student1 a
join student2 b on a.id= b.stdid
where a.id LIKE ' '

;quit;
```
How to tell proc sql to pull only 10 digit (numeric characters) records only? I could find only wild card characters example on the support forums. I am looking for code to be used within inverted comma( ... a.id LIKE '<what to put here?>'.).

Re: Restricting proc sql to pull exact number of numeric character values from a column in SAS

Kurt_Bremser — Thu, 16 Dec 2021 17:48:49 GMT

Use a

where length(id) = 10

clause or a

(where=(length(id = 10))

dataset option.

Re: Restricting proc sql to pull exact number of numeric character values from a column in SAS

Reeza — Thu, 16 Dec 2021 17:49:06 GMT

where length(a.id) = 10

Re: Restricting proc sql to pull exact number of numeric character values from a column in SAS

maguiremq — Thu, 16 Dec 2021 17:50:09 GMT

Can you please provide us some example data? You can use this as a guide:

https://blogs.sas.com/content/sastraining/2016/03/11/jedi-sas-tricks-data-to-data-step-macro/

You could use the LENGTH function? Like this?

data have;
input id :$10. subj :$10.;
datalines;
STUDENT123 Math
STUDENT242 History
STUDENT223 Reading
S123 Science
S456 Technology
STUDENT211 Spanish
;
run;

proc sql;
	create table want as
		select
					*
		from
					have
		where
					length(id) = 10;
quit;