Proc SQL Substring of a numeric variable

[pepevo]

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

[LNEW]

try substrn for numeric variable.

[pepevo]

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

[SASKiwi]

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 

[PaigeMiller · Accepted solution]

---

@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

[Astounding]

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

[pepevo]

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

[SASKiwi]

@pepevo - see my post above for simpler answer.

[ballardw]

---

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

[Tom]

---

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

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

[PaigeMiller]

---

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

[Tom]

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)

[ErikLund_Jensen]

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)

[pepevo]

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