WHERE CODITION TO LIMIT THE LENGHT OF THE CHAR IN SAS DATA SET.

[shailaja3]

Good evening everyone.

I have a csv files which consists of data and lenght to the colume was given 100. and i wrote another program, that find out if the program encounters more than 100 variables in it. It has to give an error message to the log folder(which is my pc log folder). 

But when i execute the below code I see in the log(SAS ENTERPRISE LOG)

NOTE: "No rows were selected".

proc sql noprint;
select distinct DB, DD
into: cod_es separated by ";" , : des_es separated by ";"
from work.Data
WHERE length(DD) = 0 OR length(DD) > 100 ;  /*Where length(DD) = 0 and length(DD) > 100*/
quit;

(Also tried with the "and"  in where)

Please help me with this. Thank you.

Any suggestions would be appreciated

[ballardw]

First thing I suggest is running proc contents on your data set. If the defined length of a variable is 100 or less then you should not expect to find any values with a length greater than 100 as that is the limit on the variable for that data set. No rows selected means none of the variables meet your condition.

I suspect this line has a typo somewhere : "i wrote another program, that find out if the program encounters more than 100 variables in it." I suspect that you meant characters. Without seeing the code and exact error message then I can't really address what may have happened.

[Tom]

Please clarify what you have done. 

Your code is testing the length of the variable DD in the dataset WORK.DATA (note: this is potentially confusing name a dataset).  If the variable DD is defined with a length of $100 it can never have a value that is larger than 100.

How did you read the CSV file?

If you want to test the length of the strings in the column of the CSV file then you will need to read the data from the CSV into a variable with a length that is longer then $100.

[FreelanceReinh]

Hello @shailaja3,

In addition to the other comments:

• Regardless of the defined length and content of character variable DD, the condition length(DD)=0 will always be FALSE. You can use the LENGTHN (or MISSING or CMISS) function or an explicit comparison (DD=' ') to check if DD is empty.

---

@shailaja3 wrote:

(...)

WHERE length(DD) = 0 OR length(DD) > 100 ;  /*Where length(DD) = 0 and length(DD) > 100*/

(...)

(Also tried with the "and"  in where)

---

• Logical conditions should not be subject to trial and error. While in terms of human language you may be interested in observations where DD is empty and in (other) observations where DD exceeds length 100, the WHERE condition using the Boolean operator AND would be a contradiction in terms and hence never be met: A numeric value such as length(DD) cannot be greater than 100 and at the same time (i.e., in the same observation) equal to 0.
• If variable DD has defined length 100 and the question is whether truncation has occurred, I would first look out for telltale values of DD with length 99 or 100. But without the CSV file nobody could give a definitive answer.
• Without a good reason I would not use the semicolon as a separator in macro variables because I like to use the %PUT statement (e.g., %put &=des_es;) without macro quoting.