BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Scott86
Obsidian | Level 7

 

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

11 REPLIES 11
naveenraj
Quartz | Level 8

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.

Astounding
PROC Star

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?

Scott86
Obsidian | Level 7

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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?

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

Tom
Super User Tom
Super User

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.

Scott86
Obsidian | Level 7

So It cannot be done in one data step?

Astounding
PROC Star

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.

 

Scott86
Obsidian | Level 7
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?

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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? 

Scott86
Obsidian | Level 7

I do, thank you good sir

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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