topic Re: How to set condition within a range of numbers shortly in SAS Programming

How to set condition within a range of numbers shortly

ResoluteCarbon — Tue, 02 Mar 2021 06:54:32 GMT

Hi all!

In my dataset, I have a data column named "SIC", it is a char variable

A small example

And I need to exclude observation that ( 4900<SIC<4949) and (6000<SIC<6999)

Here is my code

data want;
     set have;
         SIC_ =input (SIC,8.);
         if SIC_<4900 or (4949<SIC_  and SIC_ < 6000) or (SIC_>6999) 
run

1. Can you tell me whether my datatype conversion as above is reasonable, I do not know when I should put 4. or 8. or 12., I have a look on this document but it is quite overwhelming to me

(https://go.documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.4&docsetId=allprodslang&docsetTarget=syntaxByType-format.htm&locale=en)

2. And is there any alternative way to code the condition above?

if SIC_<4900 or (4949<SIC_  and SIC_ < 6000) or (SIC_>6999)

Thanks in advance

Re: How to set condition within a range of numbers shortly

PeterClemmensen — Tue, 02 Mar 2021 07:08:07 GMT

1) The 4., 8., or 12. is the informat. Meaning how SAS should read the variable. In this case, if you know you have only 4 digits, the right thing is to use 4.

2) If you don't really need the numeric value of SIC, you can use its converted value directly and do something like this

data have;
input SIC $;
datalines;
3559
3559
3559
3559
4911
4911
4911
2070
2070
2070
;

data want;
   set have;
   if input(sic, 4.) not in (4900:4949, 6000:6999);
run;

Re: How to set condition within a range of numbers shortly

mkeintz — Tue, 02 Mar 2021 07:19:33 GMT

You don't really need to convert data types:

 data want;
   set have;
   where not (length(sic)=4 
              and
              (('4900'<sic<'4949') or ('6000'<sic<'6999'))
             );
run;

The "length(sic)=4" condition prevents a value like "493" (left justified) from qualifying as being between "4900" and "4949". This is what would happen when lexicographic ordering is being applied.

Re: How to set condition within a range of numbers shortly

ResoluteCarbon — Tue, 02 Mar 2021 08:16:26 GMT

Hi @mkeintz

Is there anyway to convert this code from "where" to "if" please ?

TIA.

Re: How to set condition within a range of numbers shortly

ballardw — Tue, 02 Mar 2021 09:12:01 GMT

@ResoluteCarbon wrote:

Hi @mkeintz

Is there anyway to convert this code from "where" to "if" please ?

TIA.

Context? Why is "IF" preferred over "WHERE"?

Did you try replacing "where" with "if"?

"Where" is typically faster as it applies to the data vector as it is read into the data set and "If" is applied later.

Re: How to set condition within a range of numbers shortly

Astounding — Tue, 02 Mar 2021 15:08:37 GMT

Have you cleaned the data? Values like those below could throw off your calculations:

0000

493A

Re: How to set condition within a range of numbers shortly

mkeintz — Tue, 02 Mar 2021 15:26:05 GMT

@ResoluteCarbon wrote:

Hi @mkeintz

Is there anyway to convert this code from "where" to "if" please ?

TIA.

Yes ... replace the word "where" with the word "if".

But, as @ballardw said, where is likely faster. That's because the where filter is outsourced to the data engine, and the data step never sees the rejected data. But the subsetting IF is not applied until after the data is read in.

Re: How to set condition within a range of numbers shortly

ResoluteCarbon — Tue, 02 Mar 2021 19:41:20 GMT

@mkeintz

So, you mean, in this specific case, where and if can be used interchangeable ?

TIA.

Re: How to set condition within a range of numbers shortly

ResoluteCarbon — Tue, 02 Mar 2021 19:43:35 GMT

Hi @Astounding

It is very nice to see your caution.

Can I ask why I need to filter "0000" value?

And I am quite confident about that "493A" will not exist following some finance papers. SIC is a 4-digit code as described from the datasource.

Thanks.

Re: How to set condition within a range of numbers shortly

Astounding — Wed, 03 Mar 2021 00:45:48 GMT

"0000" would be selected, as it satisfies the condition SIC_ < 4900.

Not sure if you want it to be selected or not.

Missing values would also be selected, as they also satisfy that same condition. (But they would be removed by checking for length of 4.)