DATA Step, Macro, Functions and more

count function in data step

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 19
Accepted Solution

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)Smiley SadColumn).
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:

 

Customer IDCustomer CountryCustomer GenderPersonal IDCustomer NameCustomer First NameCustomer Last NameCustomer Birth DateCustomer AddressStreet IDStreet NumberCustomer Type ID
4USM James KvarniqJamesKvarniq27/06/19784382 Gralyn Rd9.26E+0943821020
5USF Sandrina StephanoSandrinaStephano9/07/19836468 Cog Hill Ct9.26E+0964682020
9DEF Cornelia KrahlCorneliaKrahl27/02/1978Kallstadterstr. 93.94E+0992020
10USF Karen BallingerKarenBallinger18/10/1988425 Bryant Estates Dr9.26E+094251040
11DEF Elke WallstabElkeWallstab16/08/1978Carl-Zeiss-Str. 153.94E+09151040
12USM David BlackDavidBlack12/04/19731068 Haithcock Rd9.26E+0910681030
13DEM Markus SepkeMarkusSepke21/07/1992Iese 13.94E+0912010
16DEM Ulrich HeydeUlrichHeyde16/01/1943Oberstr. 613.94E+09613010
17USM Jimmie EvansJimmieEvans17/08/1958391 Greywood Dr9.26E+093911030

 

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
Super User
Posts: 7,997

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? 

View solution in original post


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: 5,518

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?

Occasional Contributor
Posts: 19

Re: count function in data step

Posted in reply to Astounding

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
Super User
Posts: 7,997

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
Super User
Posts: 7,997

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
Super User
Posts: 7,077

Re: count function in data step

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.

Occasional Contributor
Posts: 19

Re: count function in data step

So It cannot be done in one data step?

Super User
Posts: 5,518

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.

 

Occasional Contributor
Posts: 19

Re: count function in data step

Posted in reply to Astounding
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?

Solution
‎04-07-2017 06:12 PM
Super User
Super User
Posts: 7,997

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? 

Occasional Contributor
Posts: 19

Re: count function in data step

I do, thank you good sir

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 11 replies
  • 2828 views
  • 0 likes
  • 5 in conversation