topic Re: SQL adding variables in SAS Programming

SQL adding variables

Scott86 — Sun, 26 Mar 2017 01:54:42 GMT

Hi everyone,

I cannot get this code to work and I cannot figure out why (the code should explain what I'm trying to achieve), Any help would be appreciated.

title "Countries";
PROC SQL;
SELECT distinct country,
Customer_Firstname, Customer_lastname, Customer_ID, Street_ID / (Customer_ID * Customer_type_ID) as House_price
format = dollar15.2,
house_price * 0.03 as rates
format = dollar12.2,
input(rates, dollar12.2),
sum(rates) as total
format = dollar12.2,
CONCAT(customer_firstname,' ',customer_lastname) as Cust_Name,
count(distinct customer_ID) as total_customer_base,
rates / total_customer_base as Avg_rates
format = dollar12.2
FROM scott.customer
where rates > Avg_rates
order by rates desc;
quit;

title;

My error log is below:

ERROR: The SUM summary function requires a numeric argument.
ERROR: Function CONCAT could not be located.
ERROR: The following columns were not found in the contributing tables: Avg_rates, house_price,
rates, total_customer_base.
274 quit;

Thanks for your help.

Re: SQL adding variables

Reeza — Sun, 26 Mar 2017 02:21:15 GMT

The rate variable should be numeric, its character. Combine the previous variable in the SUM ()

CONCAT, as noted, is note a SAS function. CATT, CATX are probably what you want.

Lastly, your table customer doesn't seem to have the variables you think it does. Run a proc contents to confirm variable names and types.

@Scott86 wrote:

Hi everyone,

I cannot get this code to work and I cannot figure out why (the code should explain what I'm trying to achieve), Any help would be appreciated.

title "Countries";
PROC SQL;
SELECT distinct country,
Customer_Firstname, Customer_lastname, Customer_ID, Street_ID / (Customer_ID * Customer_type_ID) as House_price
format = dollar15.2,
house_price * 0.03 as rates
format = dollar12.2,
input(rates, dollar12.2),
sum(rates) as total
format = dollar12.2,
CONCAT(customer_firstname,' ',customer_lastname) as Cust_Name,
count(distinct customer_ID) as total_customer_base,
rates / total_customer_base as Avg_rates
format = dollar12.2
FROM scott.customer
where rates > Avg_rates
order by rates desc;
quit;

title;

My error log is below:

ERROR: The SUM summary function requires a numeric argument.
ERROR: Function CONCAT could not be located.
ERROR: The following columns were not found in the contributing tables: Avg_rates, house_price,
rates, total_customer_base.
274 quit;

Thanks for your help.

Re: SQL adding variables

art297 — Sun, 26 Mar 2017 03:16:37 GMT

The following lines of your code jumped out at me:

input(rates, dollar12.2),
sum(rates) as total
format = dollar12.2,
CONCAT

You had just calculated rates in the lines before that code. Remove the first line (i.e.)

input(rates, dollar12.2),

Then, like @Reeza said, CONCAT isn't a SAS function. You want:

CATX(' ',customer_firstname,customer_lastname) as Cust_Name,

Finally, where you are using calculated variables after they're created, you may have to preface their names with the word:

calculated (e.g., calculated rates)

Art, CEO, AnalystFinder.com

Re: SQL adding variables

Scott86 — Sun, 26 Mar 2017 03:58:04 GMT

How do I make rates numeric? I thought by setting the format as dollar12.2 it would make it numeric. Also the variables that are not in the table are ones I'm trying to calculate.

Re: SQL adding variables

PGStats — Sun, 26 Mar 2017 04:15:07 GMT

The message says that dataset scott.customer does not contain variables Avg_rates, house_price,
rates, total_customer_base.

Re: SQL adding variables

Patrick — Sun, 26 Mar 2017 23:46:54 GMT

@Scott86

I believe I understand what you're trying to achieve but there are quite a few things wrong with your code. The fastest way to answer your question would be to post working and tested code. Please post sample data (a SAS data step creating such sample data).

What you've got wrong in your code is mostly about "timing". You're assuming that you can create a new variable in a SELECT clause and then use this new variable straight away in the same select clause. Well: That's with standard SQL not the case.

Having said that: The SAS SQL flavour provides a keyword CALCULATED which allows you to reference a calculated column in the same SQL SELECT clause. http://support.sas.com/documentation/cdl/en/sqlproc/69822/HTML/default/viewer.htm#n0jxri3yohqdwbn1slekmsc882gz.htm

If you're not dealing with large data volumes then it's sometimes also easier to implement and read if you're not trying to do everything in a single SQL but if you split it up into several steps. You could implement a first SQL to just create the calculated columns and then a 2nd SQL where you use these calculated columns (and there are then also ways to combine these two SQL's into one using a inline view which then is proper SQL syntax and doesn't need the CALCULATED keyword anymore.

Provide some sample data and we can show you working code options for what you're trying to achieve.

Re: SQL adding variables

lakshmi_74 — Mon, 27 Mar 2017 02:02:29 GMT

I can't see proper SQL code. please can you give a dataset and let us know what exactly you are looking.

As I can see some mistakes:

Customer_ID, Street_ID / (Customer_ID * Customer_type_ID) as House_price : how the possibility to calculate ID for house_price.

sum(rates) as total : You can use total in the same sql as by using calculated total

CONCAT(customer_firstname,' ',customer_lastname) as Cust_Name : No Concat in SAS as previous authors mentioned

count(distinct customer_ID) as total_customer_base : You have to take as another sql statement.

Please give a sample dataset and what exactly you want?

Thanks

Re: SQL adding variables

Scott86 — Mon, 27 Mar 2017 03:32:50 GMT

@Patrick

title "Countries";
PROC SQL;
SELECT distinct country,
Customer_Firstname, Customer_lastname, Customer_ID, Street_ID / (Customer_ID * Customer_type_ID) as House_price
format = dollar15.2,
calculated house_price * 0.03 as rates
format = dollar12.2,
sum(calculated rates) as total
format = dollar12.2,
Catx(' ',customer_lastname,customer_firstname) as Cust_Name,
count(distinct customer_ID) as total_customer_base,
calculated rates / calculated total_customer_base as Avg_rates
format = dollar12.2
FROM scott.customer
where calculated rates > calculated Avg_rates
order by rates desc;
quit;

title;

This code gives me this error:

ERROR: Summary functions are restricted to the SELECT and HAVING clauses only.

Also it won't let me attach a SAS data table, do I have to copy paste it to excel?

Re: SQL adding variables

lakshmi_74 — Mon, 27 Mar 2017 06:44:03 GMT

Can you give us info. what exactly you want to get the results? As sometimes in SQL you have to use subqueries according to your criteria.

Re: SQL adding variables

Patrick — Mon, 27 Mar 2017 07:55:07 GMT

@Scott86

We really need sample data to support you!

Attaching a SAS file should be possible but it's not a good thing for us as such SAS files are SAS version and OS specific. It's much better if you post SAS data step code which generates sample data.

Most people don't like Excel file attachments for security reasons.

There is a simple and easy to use way how you can create such data step code using your SAS file (SAS table) as input.

How to create a data step version of your data AKA generate sample data for forums
https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...

Re: SQL adding variables

Scott86 — Sat, 01 Apr 2017 22:56:30 GMT

@Patrick

I'm trying to break down the steps now.

data customer1;
set scott.customer;
House_price = sum(Street_ID)/ (sum(Customer_ID) * sum(Customer_type_ID));
format House_price = dollar12.2;
run;

Here is a new error:

18 data customer1;
19 set scott.customer;
20 House_price = sum(Street_ID)/ (sum(Customer_ID) * sum(Customer_type_ID));
21 format House_price = dollar12.2;
-
79
ERROR 79-322: Expecting a DEFAULT.

22 run;

When I try attach the data set it give me this error: 'The contents of the attachment doesn't match its file type.'

Re: SQL adding variables

Reeza — Sat, 01 Apr 2017 23:01:40 GMT

I doubt the SUM function is doing what you think it does. Check your parentheses on previous line, house_price calculation. You have too many.

Re: SQL adding variables

Reeza — Sat, 01 Apr 2017 23:03:56 GMT

Re attachment, what file type are you trying to attach?

Re: SQL adding variables

Tom — Sun, 02 Apr 2017 00:24:35 GMT

Use the CODE icons in the web form so that you formatting doesn't disappear. Here is original SELECT statement re-formatted to make it easier to read.

SELECT distinct
  country
, Customer_Firstname
, Customer_lastname
, Customer_ID
, Street_ID / (Customer_ID * Customer_type_ID) as House_price format = dollar15.2
, house_price * 0.03 as rates format = dollar12.2
, input(rates, dollar12.2)
, sum(rates) as total format = dollar12.2
, CONCAT(customer_firstname,' ',customer_lastname) as Cust_Name
, count(distinct customer_ID) as total_customer_base
, rates / total_customer_base as Avg_rates format = dollar12.2
  FROM scott.customer
  where rates > Avg_rates
  order by rates desc
;

So let's look and see what this is doing. The first three variables are straight copies of existing variables.

The fourth variable you are deriving as House_price. Note that this will work only if the three variables used are numbers.

For the fifth variable you are trying to reference this new variable House_price. To do that you need to add the keyword CALCULATED.

For the sixth variable you are indicating that the variable RATES exists on the input and is a character variable. Also you are not giving this new variable a name.

For the 7th variable you appear to be trying to use the aggregate function SUM() on the same variable RATES that you just assumed above was a character string. So which is it? is RATES a number or a character string?

For the 8th you reference an unknow function CONCAT().

For the 9th you again are referencing an aggregate function COUNT().

For the 10th you are again referencing the problematic RATES variable of unknown type and unknown origin and you are also referencing the new variable total_cutomer_base without using the CALCULATED keyword.

So here is an attempt to clean it up a littile, but without a description of the vairables in your source table and a verbal description of how you want it transformed we cannot really help you much.

SELECT distinct
  country
, Customer_Firstname
, Customer_lastname
, Customer_ID
, Street_ID / (Customer_ID * Customer_type_ID) as House_price format = dollar15.2
, (calculated house_price) * 0.03 as rates format = dollar12.2
, sum( calculated rates) as total format = dollar12.2
, CATX(' ',customer_firstname,customer_lastname) as Cust_Name
, count(distinct customer_ID) as total_customer_base
, calculated rates / calculated total_customer_base as Avg_rates format = dollar12.2
  FROM scott.customer
  having calculated rates > calculated Avg_rates
  order by calculated rates desc
;

Re: SQL adding variables

Scott86 — Sun, 02 Apr 2017 04:02:22 GMT

@Tom

It worked. What you posted is what I was trying to do all along. For some reason it's not letting me provide the data table, not sure why.

thanks for your help