CODEWITHSUNDEEP
sqlsas
Sara H
Sara H

Reputation: 1

How to create new column based on condition of another column PROC SQL

I'm trying to create a set of new columns based on the condition of another column using a sql statement in SAS. However, with my current code, whenever the condition is not met, it is showing up as a Null value. But I don't want the new columns to be constructed with these blanks. Could someone help with this? In the actual dataset, I'm joining a few tables together.

Here is a simplified mock dataset:

mock dataset

Here is the code used:

PROC SQL;
Create table Sales AS 
SELECT 
SUM (CASE WHEN Sub_Category in('Coffee')THEN Previous_Sales END)AS 
Coffee_Sale,
SUM(CASE WHEN Sub_Category in('Tea')THEN Previous_Sales END)AS Tea_Sale,
SUM(CASE WHEN Sub_Category in('Soda')THEN Previous_Sales END)AS Soda_Sale
FROM data
GROUP BY Sub_Category;

And I'm trying to get to this format:

target format

This code is returning:

Code Result

Upvotes: 0

Views: 3421

Answers (3)

Tom
Tom

Reputation: 51566

I wouldn't use SQL for this problem because you will need to hard card the values and variable names (or use some code generation). If instead you just use standard SAS procedures your code will automatically adjust to new sub categories.

proc summary data=have nway ;
  class sub_category ;
  var previous_sales ;
  output out=stats sum= ;
run;
proc transpose data=stats out=want(drop=_name_) suffix=_sales;
  id sub_category;
  var previous_sales;
run;

Upvotes: 0

miritovil
miritovil

Reputation: 31

Using PIVOT, more detail in this document from Oracle.

SELECT * FROM
 (SELECT Previous_Sales, Sub_Category
  FROM data)
PIVOT (
  SUM(Previous_Sales),
  FOR Sub_Category IN ('Coffee', 'Tea', 'Soda')
  )

Upvotes: 0

Popeye
Popeye

Reputation: 35900

You just need to remove group by from your existing code.

Create table Sales AS 
SELECT SUM (CASE WHEN Sub_Category in('Coffee')THEN Previous_Sales END)AS Coffee_Sale, 
       SUM(CASE WHEN Sub_Category in('Tea')THEN Previous_Sales END)AS Tea_Sale, 
       SUM(CASE WHEN Sub_Category in('Soda')THEN Previous_Sales END)AS Soda_Sale 
   FROM data -- no group by

You can add the same SUM with case expression for beer and water.

Upvotes: 1

Related Questions