topic Re: blank values of a query in SAS Programming

blank values of a query

znhnm — Wed, 28 Dec 2022 20:33:56 GMT

Hi,

I am creating a new column called "group" in my dataset based on the value of another column. The real code I run has many more if conditions but the "group" column has only 3 values; indeterminate, not performance, performance. The 'reason' has no blank values in mydata.

data mydata; set mydata; if reason = "asd asd asd" then group = "indeterminate"; if reason = "abc abc abc" then group = "indeterminate"; if reason= "xxx" then group="not performance"; if reason= "yy" then group="performance"; if cancellation_reason="" then cancellation_grouping="missing"; run;

In the output, some certain group values are blank. They didn't get assigned to any of the 3 values.

For example, assume the reason "asd asd asd" is one of the values of 'reason' with blank 'group' in the output. Then I search for it in the data, it does not return any output.

proc sql;
select reason, group
from mydata
where reason = 'asd asd asd' ;
quit;

Somehome, the data seems it does not have this 'asd asd asd' value in the 'reason' column eventhough I exactly copy-pasted from the data itself. What could be the reason for this and how to solve it?

Re: blank values of a query

andreas_lds — Wed, 28 Dec 2022 20:43:33 GMT

Overwriting the source dataset during development is not recommended. It is always better to create a new dataset, so that steps are repeatable. The first thing you should to is re-creating "mydata". Then please post some lines of that dataset as data step, so we have actually see the data you have.

Re: blank values of a query

Patrick — Thu, 29 Dec 2022 02:28:08 GMT

Casing or leading blanks could be the issue. Some code like below should help to avoid such issues.

Use IF...THEN...ELSE constructs for cases like yours and always implement exhaustive conditions having a final ELSE without an IF to capture everything that remains (like exceptions).

data mydata;
  set mydata;
  length group $20;
  _reason=upcase(strip(reason));
  drop _reason;
  if _reason in ("asd asd asd","abc abc abc") then group = "indeterminate";
  else if _reason= "xxx" then group="not performance";     
  else if _reason= "yy" then group="performance"; 
  else group='ERROR';

  if missing(cancellation_reason) then cancellation_grouping="missing";
run;

Using formats is another way to code such IF...THEN...ELSE constructs. Such an approach can increase "readability" and "maintainability" of your code.

proc format;
  value $group
    "ASD ASD ASD","ABC ABC ABC" = "indeterminate"
    "XXX"   = "not performance"
    "YY"    = "performance"
    other   = "ERROR"
  ;
run;

data mydata;
  set mydata;
  group=put(upcase(strip(reason)),$group.);
	if missing(cancellation_reason) then cancellation_grouping="missing";
run;

And now that you captured the exceptions you can also easier investigate and debug your code.

proc sql;
  select reason, group
  from mydata
  where group='ERROR' 
  ;
quit;

Re: blank values of a query

Tom — Thu, 29 Dec 2022 05:59:10 GMT

I am assuming you are looking at some PRINT OUT of the values that makes you think that string is there.

The usual cause is leading spaces. Especially if the print out you looked at was generated with ODS as that REMOVES the leading spaces when it generates the print out.

if left(reason) = "asd asd asd" then ...

Other obvious things to check. Does the CASE of the letters agree? ASD is a different string than asd.

if lowcase(left(reason)) = "asd asd asd" then ...

And the harder problem is non-printing characters in the string. So they look like spaces, but they aren't spaces. Things like tab ('09'x) or linefeed ('0A'x) or carriage return ('0D'x) or non-breaking spaces ('A0'x) .

if lowcase(left(translate(reason,' ','090A0DA0'x))) = "asd asd asd" then ...

or perhaps something more exotic. To look for those print the values using the $HEX format.