topic Re: count function in data step in SAS Programming

count function in data step

Scott86 — Mon, 18 Mar 2019 18:19:12 GMT

I'm trying to add multiple variables in a data step then use them in another calculation for another variable. Is this possible? I think I'm using SAS base 9.2

proc sort data = scott.customer nodupkey;
by customer_ID;
run;
data Customer_1;
set scott.customer;
keep country customer_Firstname street_ID Customer_ID Customer_type_id;
house_price = Street_ID / (Customer_ID * Customer_type_ID);
cust_name = CATX(' ',customer_firstname,customer_lastname);
rates = house_price * 0.03;
total_customer_base = count(customer_ID);
Avg_rates = rates / total_customer_base;
run;
proc sort data = customer_1;
by house_price;
run;

The error log is below:

75 data Customer_1;
76 set scott.customer;
77 keep country customer_Firstname street_ID Customer_ID Customer_type_id;
78 house_price = Street_ID / (Customer_ID * Customer_type_ID);
79 cust_name = CATX(' ',customer_firstname,customer_lastname);
80 rates = house_price * 0.03;
81 total_customer_base = count(customer_ID);
-----
71
ERROR 71-185: The COUNT function call does not have enough arguments.

82 Avg_rates = rates / total_customer_base;
83 run;

NOTE: Numeric values have been converted to character values at the places given by: (Line):(Column).
81:29
NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.CUSTOMER_1 may be incomplete. When this step was stopped there were 0
observations and 5 variables.
WARNING: Data set WORK.CUSTOMER_1 was not replaced because this step was stopped.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds

84
85 proc sort data = customer_1;
86 by house_price;
ERROR: Variable HOUSE_PRICE not found.
87 run;

NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SORT used (Total process time):
real time 0.06 seconds
cpu time 0.01 seconds

When I try add the data set I get this error:

'The contents of the attachment doesn't match its file type.'

File type: customer.sas7bdat

I've copy pasted some of the data below:

Edit: removed data by request

I'm aware the customer full name already exists I'm just practising merging cells and creating my own data sets.

Thanks for any help

Re: count function in data step

naveenraj — Wed, 05 Apr 2017 11:17:51 GMT

here you are trying to use count as in the SQL query , hence you are getting the error.

A better approach would be to use PROC SQL.

Re: count function in data step

Astounding — Wed, 05 Apr 2017 10:34:05 GMT

COUNT compares two character strings:

http://support.sas.com/documentation/cdl/en/lefunctionsref/69762/HTML/default/viewer.htm#p02vuhb5ijuirbn1p7azkyianjd8.htm

What is it that you want it to do?

Re: count function in data step

RW9 — Wed, 05 Apr 2017 10:42:40 GMT

Do also note, that for any sort of decent answer post test data in the form of a datastep, follow this post if you need help:

https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712

Re: count function in data step

Tom — Wed, 05 Apr 2017 11:48:39 GMT

Your first error message is:

81 total_customer_base = count(customer_ID);
                         -----
                         71
ERROR 71-185: The COUNT function call does not have enough arguments.

So look up the meaning of the COUNT() function and see why you are getting the error message. What did you think that it was going to do?

Your second error message is:

85 proc sort data = customer_1;
86 by house_price;
ERROR: Variable HOUSE_PRICE not found.
87 run;

So this is saying that your new data set CUSTOMER_1 does not have a variable named HOUSE_PRICE. Partly this is because the previous step didn't run because of the mistaken syntax on the COUNT() function call. But your data step wouldn't have saved a variable named HOUSE_PRICE even if it did run.

Re: count function in data step

Scott86 — Thu, 06 Apr 2017 08:10:36 GMT

I'm trying to count how many customers there are by getting all the unique customer_ID and then counting how many there are. I'm trying to aviod SQL.

Re: count function in data step

Scott86 — Thu, 06 Apr 2017 08:11:19 GMT

So It cannot be done in one data step?

Re: count function in data step

RW9 — Thu, 06 Apr 2017 09:04:04 GMT

Why are you "I'm trying to aviod SQL."? SQL has great aggregation functions on normalised data which is what you are wokring on and so would seem a pretty good fit. No point chopping your left hand off as you only want to use your right?

You can do it all in one datastep, the coding is a bit more complex having to read the data in a couple of times (i.e. a couple of set statements), or you can create the summary information and then merge that onto your data. Not providing any code currently, need to post test data in the form of a datastep and what you want the output to look like before I do any coding, but really looking at the code you have posted, it can be replaced with a simple four or five line SQL step dropping all the sorting and such like, so I really don't see why you would want to make it more complicated?

Re: count function in data step

Astounding — Thu, 06 Apr 2017 10:37:18 GMT

You can do it in one DATA step, since SAS contains a feature that is the count of observations (and NODUPKEY on the first PROC SORT limits the data to one observation per CUSTOMER_ID). Just change the SET statement to read:

set scott.customer nobs=customer_count;

Then refer to CUSTOMER_COUNT wherever you need it.

Re: count function in data step

Scott86 — Fri, 07 Apr 2017 05:05:49 GMT

proc sort data = scott.customer nodupkey;
by customer_ID;
run;

data Customer_1;
set scott.customer nobs=customer_count;
keep country customer_Firstname street_ID Customer_ID Customer_type_id;
house_price = Street_ID / (Customer_ID * Customer_type_ID);
cust_name = CATX(' ',customer_firstname,customer_lastname);
rates = house_price * 0.03;
total_customer_base = customer_count;
Avg_rates = rates / total_customer_base;
run;

proc sort data = customer_1;
by house_price;
run;

19 /* Chapter 3*/

20 proc sort data = scott.customer nodupkey;
21 by customer_ID;
22 run;

NOTE: Input data set is already sorted, no sorting done.
NOTE: PROCEDURE SORT used (Total process time):
real time 0.28 seconds
cpu time 0.06 seconds

23
24 data Customer_1;
25 set scott.customer nobs=customer_count;
26 keep country customer_Firstname street_ID Customer_ID Customer_type_id;
27 house_price = Street_ID / (Customer_ID * Customer_type_ID);
28 cust_name = CATX(' ',customer_firstname,customer_lastname);
29 rates = house_price * 0.03;
30 total_customer_base = customer_count;
31 Avg_rates = rates / total_customer_base;
32 run;

NOTE: There were 77 observations read from the data set SCOTT.CUSTOMER.
NOTE: The data set WORK.CUSTOMER_1 has 77 observations and 5 variables.
NOTE: DATA statement used (Total process time):
real time 0.39 seconds
cpu time 0.03 seconds

33
34 proc sort data = customer_1;
35 by house_price;
ERROR: Variable HOUSE_PRICE not found.
36 run;

it is not recognising house_price even though I calculate it above in the data step. How do I order by a variable I just created?

Re: count function in data step

RW9 — Fri, 07 Apr 2017 08:19:06 GMT

Have a look at this line in your program in the datastep before the sort:

keep country customer_Firstname street_ID Customer_ID Customer_type_id;

Do you see a problem?

Re: count function in data step

Scott86 — Fri, 07 Apr 2017 22:12:22 GMT

I do, thank you good sir