Solved
Contributor
Posts: 29

# count function in data step

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;
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:

 Customer ID Customer Country Customer Gender Personal ID Customer Name Customer First Name Customer Last Name Customer Birth Date Customer Address Street ID Street Number Customer Type ID 4 US M James Kvarniq James Kvarniq 27/06/1978 4382 Gralyn Rd 9.26E+09 4382 1020 5 US F Sandrina Stephano Sandrina Stephano 9/07/1983 6468 Cog Hill Ct 9.26E+09 6468 2020 9 DE F Cornelia Krahl Cornelia Krahl 27/02/1978 Kallstadterstr. 9 3.94E+09 9 2020 10 US F Karen Ballinger Karen Ballinger 18/10/1988 425 Bryant Estates Dr 9.26E+09 425 1040 11 DE F Elke Wallstab Elke Wallstab 16/08/1978 Carl-Zeiss-Str. 15 3.94E+09 15 1040 12 US M David Black David Black 12/04/1973 1068 Haithcock Rd 9.26E+09 1068 1030 13 DE M Markus Sepke Markus Sepke 21/07/1992 Iese 1 3.94E+09 1 2010 16 DE M Ulrich Heyde Ulrich Heyde 16/01/1943 Oberstr. 61 3.94E+09 61 3010 17 US M Jimmie Evans Jimmie Evans 17/08/1958 391 Greywood Dr 9.26E+09 391 1030

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

Thanks for any help

Accepted Solutions
Solution
‎04-07-2017 06:12 PM
Super User
Posts: 9,465

## Re: count function in data step

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?

All Replies
Contributor
Posts: 54

## Re: count function in data step

[ Edited ]

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.

Super User
Posts: 6,648

## Re: count function in data step

COUNT compares two character strings:

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

What is it that you want it to do?

Contributor
Posts: 29

## Re: count function in data step

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.

Super User
Posts: 9,465

## Re: count function in data step

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?

Super User
Posts: 9,465

## Re: count function in data step

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-dat...

Super User
Posts: 7,948

## Re: count function in data step

``````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?

``````85 proc sort data = customer_1;
86 by house_price;
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.

Contributor
Posts: 29

## Re: count function in data step

So It cannot be done in one data step?

Super User
Posts: 6,648

## Re: count function in data step

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.

Contributor
Posts: 29

## Re: count function in data step

``````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;
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?

Solution
‎04-07-2017 06:12 PM
Super User
Posts: 9,465

## Re: count function in data step

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?

Contributor
Posts: 29

## Re: count function in data step

I do, thank you good sir

☑ This topic is solved.