BookmarkSubscribeRSS Feed
James_Littlejohn
Calcite | Level 5

i am trying to see how many customers (by SSN) have applied for credit accross multiple customers/stores.  Each store has a unique ID and each SSN is unique.  I am using Enterprise Guide 5.1 and feel I shold use a Proc Freq but I need to see which unique SSN matches to more than on unique store_ID. 

 

Is this possible with a Proc Freq or some other statement? 

5 REPLIES 5
Cynthia_sas
SAS Super FREQ
Is all this information in one dataset? Or is the data in more than 1 dataset? How is the data structured? Do you have one row for every unique SSN or can you have multiple SSN rows? Does each person/SSN have one column for their store IDs or .... ? How/whether you use PROC FREQ will depend on the answer to how the data is structured.

cynthia

cynthia
James_Littlejohn
Calcite | Level 5

All of the data is in one dataset.  There is a column for Store_ID and another for SSN.  There are many dupllicates for both of these variables and I am trying to find the ssn's that are common in more than one store.

 

There is a unique record for each credit application that a customer fills out.  I want to see how many stores they apply to.  I de-duped by SSN and took the last record but that only gave me the store they applied for on the last application.

Astounding
PROC Star

A single PROC FREQ won't do it, but two PROC FREQs will.  The idea is that the output from the first PROC FREQ becomes the input to the second PROC FREQ:

 

proc freq data=have;

tables ssn * store / noprint out=unique;

run;

 

proc freq data=unique;

tables ssn / noprint out=want (keep=ssn count where=(count > 1));

run;

 

This gives you a final data set named WANT, with SSN and COUNT.  COUNT is a count of how many different stores that particular SSN applied at.

 

I'm sure there are some clever SQL programmers out there that could do this in one step. But with PROC FREQ it takes two.

James_Littlejohn
Calcite | Level 5

That worked perfectly, thank you.  I now need to isolate the last applications for each customer for each store.  I have de duped by last.app_id but that only gives me one record per customer.  If the customer applied at 4 stores, I want to keep the last application entered (these are numerical) for each customer by store.  If the customer went to 1 store, they would have 1 record, 4 stores 4 records etc.

 

I am trying to sort by store name ascending application numb er, then take the last ssn for each store but am not getting success..

Astounding
PROC Star

It sounds like  you are moving in the right direction.  Try using three variables in your BY statement:

 

by store ssn application_no;

 

Then condition on last.ssn.

 

Alternatively:

 

by ssn store application_no;

 

Then condition on last.store.

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
  • 948 views
  • 0 likes
  • 3 in conversation