DATA Step, Macro, Functions and more

Proc Univariate

Accepted Solution Solved
Reply
Contributor Boa
Contributor
Posts: 21
Accepted Solution

Proc Univariate

[ Edited ]

Hi sasusers, i need to merge the 2 datasets as some columns not included in the dataset. Therefore, is it possible if i use proc univariate and then merge together? And i tried using sql but do not get the result.

 

 Just an example of the dataset:

Spending data set, there are spendingid, customerid, totalprice and numunits

Spending_id                  Customer_id                 totalprice              numunits

  1212112                        100000                           19                       2

  989898                           112121                           298                    10

  3i31030                           20000                            2                        22 

  

Customer dataset, there are customerid and firstname

  Customer_id                 firstname

  12311111                        Ellen

  9908009                         JOhn

  3376247                         Jay

 

 Question: Show all the customers in the 3rd quartile of their spending. Display their name, total spent and their customer id

 

 

Thanks in advance.Smiley Happy


Accepted Solutions
Solution
‎11-27-2016 08:00 AM
Super User
Posts: 19,851

Re: Proc Univariate

I didn't provide merge code, I left that as an exercise for you - it's your homework after all, and you seemed to have this step from previous questions. 

 

You first need to summarize your table 1 to a single record per customer ID. 

Then merge in names from table 2. 

Then use PROC RANK to find quartiles. 

 

Good luck

 

 

 

View solution in original post


All Replies
Super User
Posts: 19,851

Re: Proc Univariate

Do you need to summarize your data first? Do you have more than one record for each customer in Table 1?

 

Otjeriwse make the final table you want using a join, containing the name and totalprice.

Then use PROC RANK with group = 4. It will divide the data into quartiles, 0-3. Take rank = 2

 

Proc RANK data=merged out=quartiles groups=4;

 VAR totalprice;

Ranks price_quartile;

run;

 

Then filter the quartiles data set to obtain the desired quartile. 

Contributor Boa
Contributor
Posts: 21

Re: Proc Univariate

There are more than 1 record in the customer data, where should i make the join statement?

I tested out the code and it got error, that shows variable is not found

data merged;
run;

proc rank data=merged out =quartiles groups=4;
VAR totalprice
Ranks price_quartile;
run;

proc print data=merged;
run;
Solution
‎11-27-2016 08:00 AM
Super User
Posts: 19,851

Re: Proc Univariate

I didn't provide merge code, I left that as an exercise for you - it's your homework after all, and you seemed to have this step from previous questions. 

 

You first need to summarize your table 1 to a single record per customer ID. 

Then merge in names from table 2. 

Then use PROC RANK to find quartiles. 

 

Good luck

 

 

 

Super User
Posts: 10,041

Re: Proc Univariate


You want Q3 for each and every customer 
or want Q3 for all of customer.

Does variable NUMUNITS stand for a freq/weight variable ?

Contributor Boa
Contributor
Posts: 21

Re: Proc Univariate

numunits is just another column but i supposed is not needed for my question.

 

i want Q3 for all customer. 

☑ This topic is solved.

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

Discussion stats
  • 5 replies
  • 303 views
  • 2 likes
  • 3 in conversation