Hello, I'm using Proc SQL and have the following statement where I'm deriving a count field.  My problem is I am trying to sum up the data and have fields I'm grouping by.  Is it possible to SUM the case statement because I don't want each record splitting out on me and I have lots of other fields summing up?    CASE WHEN CODE = 'X' THEN -1 ELSE 1 END as COUNT,   Not working: SUM (CASE WHEN CODE = 'X' THEN -1 ELSE 1 END as COUNT), ... View more

I'm trying to limit some results to within a certain date range and struggling with the syntax of how the date should be entered in my WHERE statement.  The field is "datetime" format at the source database with numeric precision of 23.  Looks like this "2022-08-05 12:35:08.637".  Any ideas with how the date should be entered in my where statement?   PROC SQL;    CREATE TABLE WORK.Results AS     SELECT Fruit, Date         FROM Database.Table   WHERE  Date >= 2022-08-05 12:35:08.637 and <= 2022-08-06 12:35:08.637 ; QUIT;     ... View more

I have the following code and table I'm using.  I am trying to do two different things.     1.  Pull back just the rows with the most recent date per product.  IE there would be three rows, one for each fruit, and the most recent date per fruit. 2. Do the same thing as 1, except I want to add code that allows for a prompt where I will enter a certain date and just pull back the most recent record per fruit less than or equal to the date I entered.   PROC SQL;    CREATE TABLE WORK.Most_Recent_Price_Per_Fruit AS     SELECT      Product,     Date,     Price       FROM Database.FruitTable   AND Price NOT= 0   ORDER BY Product, Date ; QUIT;   Date Product Price 01Jan2019 Oranges $1.00 01Jun2020 Oranges $1.75 01Mar2023 Oranges $1.50 01Sep2021 Apples $1.00 01Mar2023 Apples $2.00 01Jan2019 Bananas $5.00 01Jun2020 Bananas $3.00   Results from option 1 in blue (rest would not be pulled back just included for reference) Date Product Price 01Jan2019 Oranges $1.00 01Jun2020 Oranges $1.75 01Mar2023 Oranges $1.50 01Sep2021 Apples $1.00 01Mar2023 Apples $2.00 01Jan2019 Bananas $5.00 01Jun2020 Bananas $3.00       Results from option 2 in blue assuming entering prompt date of 01MAY2022 (rest would not be pulled back just included for reference) Date Product Price 01Jan2019 Oranges $1.00 01Jun2020 Oranges $1.75 01Mar2023 Oranges $1.50 01Sep2021 Apples $1.00 01Mar2023 Apples $2.00 01Jan2019 Bananas $5.00 01Jun2020 Bananas $3.00 ... View more