Error 22-322 and Error 76-322 / Audit

[alperebb]

Hello everyone,

We are making an inquiry for the audit department. We will review POs that were created and invoiced without PR. I am sharing the code below. Can you tell us how we can solve it? We are very new in the SAS world, we are in our infancy.

I'm getting errors 22-322, 76-322, 22-322 in sequence.

PROC SQL;
CREATE TABLE WORK.K04_PRolmadanPO AS
SELECT
t1.PR_Number,
t1.Require_Track_Num,
t1.Gross_order_value,
t1.Company_Code,
t1.Doc_Num,
t1.Funds_Center,
t1.Profit_Center,
t1.Client,
t1.Material_Group,
t1.Material_Number,
t1.Order_Unit_of_Measure,
t1.Order_Quantity,
t1.Net_Price,
t1.Net_Order_Value,
t1.Invoice_Receipt_Indicator,
t1.Goods_Receipt_Indicator,
t1.Short_Text,
t1.Plant,
t1.Rebate_basis_1,
t1.Order_Price_Unit,
t1.Net_Weight,
t1.Price_Unit,
t1.Update_group_for_statistics,
t1.Numerator_Conversion,
t1.VOLUME,
t1.Target_Value,
t1.Material_Number1,
t1.Material_Type,
t1.MRP_Area,
t1.Ind_Update_Info_Record,
t1.Material_ledger_activated,
t1.Num_Purch_Info_Record,
t1.Name_of_Requester,
t1.Storage_Location,
t1.Unit_of_Weight,
t1.Delivery_Completed_Ind,
t1.Num_Manufacturer,
t1.Volume_unit,
t1.Price_Printout,
t1.Valuation_Category,
t1.Valuation_Type,
t1.Account_Assign_Cat,
t1.Consumption_Posting,
t1.ZZ_EQUNR_,
t1.Deletion_Indicator,
t1.Material_Number2,
t1.Item_statistical,
t1.Invoicing_plan_number,
t1.Adress_number,
t1.Tax_code,
t1.Returns_Item,
t1.Date_Price_Determination,
FROM TEST.PURCHASEORDER t1 WHERE
t1.PR_Number is missing and t1.Invoice_Receipt_Indicator is not missing and t1.Doc_Category = "F" and t1.Material_Group like "ZC%"

;
*Those with document category "K" are not included because they are rare and the observations in them are mostly invalid.;
*Since there is a high rate of non-PR in POs, only those in certain categories were run.
Category selection was decided based on the data distribution.;

QUIT;

[Kurt_Bremser]

"is missing" is not valid SAS SQL syntax. Compare with a missing value ("" for character, . for numeric) or use the MISSING function.

[Patrick]

@Kurt_Bremser 

A current SAS version accepts is missing without any note or warning.

[ballardw]

---

@Patrick wrote:

@Kurt_Bremser 

A current SAS version accepts is missing without any note or warning.

---

But does it like "is not missing" which OP also uses.

[Patrick]

---

@ballardw wrote:

---

@Patrick wrote:

@Kurt_Bremser 

A current SAS version accepts is missing without any note or warning.

---

But does it like "is not missing" which OP also uses.

---

is missing/is not missing is documented SAS syntax for Proc SQL: IS Operator

It's also the syntax the EG Query Builder wizard generates.

What I'm curious about it - and can't test right now - is to what syntax that gets translated if running against a database table (like Postgres or Oracle) where missing and NULL have different meanings.

[ballardw]

Best practice when receiving an error message is to copy the code and all the messages for the step from the log and paste that into a text box.

[FreelanceReinh]

---

@alperebb wrote:

t1.Date_Price_Determination,
FROM TEST.PURCHASEORDER t1 WHERE

---

Hello @alperebb,

I think removing the comma after the last SELECT item will resolve the issue.

[Tom]

---

@FreelanceReinh wrote:

---

@alperebb wrote:

t1.Date_Price_Determination,
FROM TEST.PURCHASEORDER t1 WHERE

---

Hello @alperebb,

I think removing the comma after the last SELECT item will resolve the issue.

---

Which is why I always place such continuation characters at the start of the overflow line where it is easy to scan for and not at the end of the truncated line where it is very difficult to see.  The same goes for the conjunctions in long boolean expressions.

CREATE TABLE WORK.K04_PRolmadanPO AS
SELECT
  t1.PR_Number
, t1.Require_Track_Num
, t1.Gross_order_value
, t1.Company_Code
...
WHERE t1.PR_Number is missing
  and t1.Invoice_Receipt_Indicator is not missing
  and t1.Doc_Category = "F"
  and t1.Material_Group like "ZC%"
;