topic Re: Proc SQL Substring of a numeric variable in SAS Programming

Proc SQL Substring of a numeric variable

pepevo — Tue, 16 Apr 2019 19:43:19 GMT

Hello,

I'm new in SAS and I need include a substring on my query of a numeric and it's not work and I get this error message -

the table has column sbmdt_dt_id as numeric

and my query is

select count(a.receipt_number)
from EXISDRDM.application_cdim a
where substr(a.sbmtd_dt_id, z4.) = 2019;

even using put(a.sbmtd_dt_id, z4.) = 2019

error: column sbmtd_dt_id could not be found in the table/view identified with the correlation name A.

error: numeric format Z in put function requres a numeric argument.

error: Expression usqing equals (=) has components that are of different data types.

If the column declared as numeric and how to I pull the 1st 4 numeric like '2019" and/or middle numeric like "9011" in where clause?

If I query

select count(a.receipt_number)
from EXISDRDM.application_cdim a
where left(put(a.sbmtd_dt_id, 4.)) = '2019';

I get zero count. That's not right.

thank you,

Bach-Nga

Re: Proc SQL Substring of a numeric variable

LNEW — Tue, 16 Apr 2019 19:42:12 GMT

try substrn for numeric variable.

Re: Proc SQL Substring of a numeric variable

PaigeMiller — Tue, 16 Apr 2019 19:50:34 GMT

@pepevo wrote:

Hello,

I'm new in SAS and I need include a substring on my query of a numeric and it's not work and I get this error message -

the table has column sbmdt_dt_id as numeric

If the column declared as numeric and how to I pull the 1st 4 numeric like '2019" and/or middle numeric like "9011" in where clause?

Use math.

where floor(a.sbmdt_dt_id/10000) = 2019;
If the column declared as numeric and how to I pull the 1st 4 numeric like '2019" and/or middle numeric like "9011" in where clause?

If you want 9011 from this longer number

where floor((a.sbmdt_dt_id-floor(a.sbmtdt_dt_id/100000))/10)

But really, if these are dates, then they should be converted to valid SAS date values,

sasdatevalue = input(put(a.sbmt_dt_id,8.),yymmdd8.);

and then the whole problem is much easier:

where year(sasdatevalue)=2019

where month(sasdatevalue)=1

where date(sasdatevalue)=11

Re: Proc SQL Substring of a numeric variable

Astounding — Tue, 16 Apr 2019 19:46:41 GMT

I'm not sure that SQL supports SUBSTR in any form.

Other variables can do the trick. For example:

where int(date/1000) = 2019

The middle part is harder, but might still use a combination of functions:

where mod( int(date/100) , 1000) = 1901

Re: Proc SQL Substring of a numeric variable

pepevo — Tue, 16 Apr 2019 19:47:22 GMT

thank you for replying.

tried that as where substrn(a.sbmtd_dt_id, z4.) = 2019 or with single quote around '2019'

Error 22-322:syntax error, expecting one of the following: a name, *.

if I use substrn(a.sbmtd_dt_id, 4.) = 2019, I don't see any result.

thank you.

Bach-Nga

Re: Proc SQL Substring of a numeric variable

pepevo — Tue, 16 Apr 2019 19:50:54 GMT

thank you all experts and the

where floor(a.sbmdt_dt_id/10000) = 2019;

work, but would you please explain to me why I need to divided by 10000? The column declared numeric 8 length

thank you so much for the inputs.

v/r,

Bach-Nga

Re: Proc SQL Substring of a numeric variable

SASKiwi — Tue, 16 Apr 2019 22:01:49 GMT

This should work. Since your date number has year, then month, then day numeric and date order are the same.

where sbmtd_dt_id between 20190101 and 20191231

Re: Proc SQL Substring of a numeric variable

SASKiwi — Tue, 16 Apr 2019 22:03:50 GMT

@pepevo - see my post above for simpler answer.

Re: Proc SQL Substring of a numeric variable

ballardw — Tue, 16 Apr 2019 22:14:22 GMT

@pepevo wrote:

thank you all experts and the
where floor(a.sbmdt_dt_id/10000) = 2019;
work, but would you please explain to me why I need to divided by 10000? The column declared numeric 8 length

thank you so much for the inputs.

v/r,

Bach-Nga

"Length" defined for numeric variables is the number of bytes used for storage. The default is 8 bytes.

data junk;

x=1;

y=123456789;

run;

will have both x and y with a "length" of 8.

The number of bytes limits the range of values than can be stored.

Re: Proc SQL Substring of a numeric variable

Tom — Tue, 16 Apr 2019 23:22:03 GMT

@pepevo wrote:

thank you all experts and the
where floor(a.sbmdt_dt_id/10000) = 2019;
work, but would you please explain to me why I need to divided by 10000? The column declared numeric 8 length

thank you so much for the inputs.

v/r,

Bach-Nga

The LENGTH of a variable is how many bytes it will take to store the variable. Since all SAS number are 8 byte floating point numbers if you store it with fewer bytes you just lose some of the bits of precision off of the number.

https://documentation.sas.com/?docsetId=lrcon&docsetTarget=p0ji1unv6thm0dn1gp4t01a1u0g6.htm&docsetVersion=9.4&locale=en#p1ikmedbli0rz2n1de001ryz895o

The following table lists the largest integer that can be reliably stored by a SAS variable in the mainframe, UNIX, and Windows operating environments.

Largest Integer That Can Be Safely Stored in a Given Length

When Variable Length Equals ...

Largest Integer

z/OS

Largest Integer

Windows/UNIX

2

256

not applicable

3

65,536

8,192

4

16,777,216

2,097,152

5

4,294,967,296

536,870,912

6

1,099,511,627,776

137,438,953,472

7

281,474,946,710,656

35,184,372,088,832

8 (default)

72,057,594,037,927,936

9,007,199,254,740,992

Largest Integer That Can Be Safely Stored in a Given Length
When Variable Length Equals ...	Largest Integer z/OS	Largest Integer Windows/UNIX
2	256	not applicable
3	65,536	8,192
4	16,777,216	2,097,152
5	4,294,967,296	536,870,912
6	1,099,511,627,776	137,438,953,472
7	281,474,946,710,656	35,184,372,088,832
8 (default)	72,057,594,037,927,936	9,007,199,254,740,992

Re: Proc SQL Substring of a numeric variable

Tom — Tue, 16 Apr 2019 23:29:05 GMT

If you want to treat a number as if it is a string then first convert the number into a string.

So if all of your values are between less than 100,000,000 then you could use Z8. format to convert it with leading zeros.

substr(put(a.sbmtd_dt_id,z8.),1,4)

If you want the leading digits (that is ignore the leading zeros) even if the magnitude of the numbers vary then use the normal numberic format (also known as F format) and left align the result.

substr(put(a.sbmtd_dt_id,F32.-L),1,4)

Re: Proc SQL Substring of a numeric variable

ErikLund_Jensen — Wed, 17 Apr 2019 10:13:45 GMT

Hi @pepevo

I would - as a start - solve this problem in your log, because as long as it's there nothing will work. It is good practice to to correct errors in the order they appear in the log, because following errors are most often caused by the first.

error: column sbmtd_dt_id could not be found in the table/view identified with the correlation name A.

Then try this:

substr(put(variable,8.),1,4)

Re: Proc SQL Substring of a numeric variable

PaigeMiller — Wed, 17 Apr 2019 11:05:40 GMT

@pepevo wrote:

thank you all experts and the
where floor(a.sbmdt_dt_id/10000) = 2019;
work, but would you please explain to me why I need to divided by 10000? The column declared numeric 8 length

thank you so much for the inputs.

This is simple mathematics at this point. You need it because you wanted to extract 2019 from a longer number.

And as I said, if this number is actually a date, then you need to convert it to a valid SAS date value, which will make your programming much much much much much much much much much much much much simpler.

Re: Proc SQL Substring of a numeric variable

pepevo — Wed, 17 Apr 2019 15:10:37 GMT

thank you all experts for all your help.

Mr. ErikLund_Jensen,

this syntax below is not working.

substr(put(variable,8.),1,4)

ERROR: Expression using different data types.

May I ask if how do I get the output for a day from sysdate in SAS. In Oracle, I used

SELECT DISTINCT a.APPLICATION_ID, a.CRTD_USER_ID, a.FRM_TYP_ID
FROM APPLICATION_CDIM a
WHERE (trunc(a.CRTD_LOAD_DTTM) = trunc(sysdate - 1) OR trunc(a.UPTD_LOAD_DTTM) = trunc(sysdate - 1));

if I want to run the query in SAS, I don't find trunc(sysdate -1)

select DISTINCT a.APPLICATION_ID, a.CRTD_USER_ID, a.FRM_TYP_ID
FROM APPLICATION_CDIM a where weekday(a.updt_load_dttm) = 1; and get 0 row and not sure it's corrected to pull a day old from a column, updt_load_dttm (this column declared as timestamp in oracle and datetime27.6 in sas) and according from sas, I see the a tons of million rows upload today

thank you sir and all.

v/r,

Bach-Nga