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

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

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

5 REPLIES 5
Reeza
Super User

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. 

Boa
Obsidian | Level 7 Boa
Obsidian | Level 7
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;
Reeza
Super User

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

 

 

 

Ksharp
Super User

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

Does variable NUMUNITS stand for a freq/weight variable ?

Boa
Obsidian | Level 7 Boa
Obsidian | Level 7

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

 

i want Q3 for all customer. 

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
  • 5 replies
  • 1078 views
  • 2 likes
  • 3 in conversation