Hello I am using SAS EG to run a prompt, which provides the output I need if I don't add the "CASE" statement. However, I need to use the same prompt to create a new variable that identify the category of the store once flagged. In other words, the prompt will check and see if the store number I entered exist and produce a data called "QUERY_HAVE". I also want the data "QUERY_HAVE" to identify the category that it found this number of the store: whether it was selling, shipping, or billing? Here is my data data have;
input year Quarter store_num $ shipping_num $ Billing_num $ sales;
datalines;
2020 1 A1 . A1 520
2020 1 A2 A1 . 15
2020 1 A3 A2 A3 753
2020 2 A1 . A3 662
2020 2 A2 A1 A2 779
2020 3 A1 . A1 895
2020 3 A2 A2 A2 1012
2020 3 A3 A3 A1 1128
2020 4 A2 A1 A3 1245
2020 4 A3 A3 . 1361
;
run; Here is the code I am using to combine the prompt and the creation of the new variable "Store_Category". The results I am getting is what I would expect for the prompt if I don't add the "CASE" statement. However, when I use the "CASE" statement, then the store category is all blank with no data in it. PROC SQL;
CREATE TABLE WORK.QUERY_HAVE AS
SELECT t1.year,
t1.Quarter,
t1.store_num,
t1.shipping_num,
t1.Billing_num,
/* Store_Category */
(Case
When t1.Store_NUM = (%unquote('%_eg_WhereParam( t1.store_num, Stores, IN, TYPE=S, IS_EXPLICIT=0 )')) then 'Selling'
When t1.Shipping_NUM = (%unquote('%_eg_WhereParam( t1.shipping_num, Stores, IN, TYPE=S, IS_EXPLICIT=0 )')) then 'Shipping'
When t1.Billing_NUM = (%unquote('%_eg_WhereParam( t1.Billing_num, Stores, IN, TYPE=S, IS_EXPLICIT=0 )')) then 'Billing'
Else ''
end) AS Store_Category,
t1.sales
FROM WORK.HAVE t1
WHERE %_eg_WhereParam( t1.store_num, Stores, IN, TYPE=S, IS_EXPLICIT=0 ) OR %_eg_WhereParam( t1.shipping_num,
Stores, IN, TYPE=S, IS_EXPLICIT=0 ) OR %_eg_WhereParam( t1.Billing_num, Stores, IN, TYPE=S, IS_EXPLICIT=0 );
QUIT;
... View more
