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

I've the data below.

NameAddressZipProd
John110,KX Colony11198PersonalLoan
Kiran7/189 Rajaji Road11094Mortagge
Kiran7/189 Rajaji Road11094Creditcard
Amar16,Mt.batten Street12409Mortagage
Raju11,Kannan nagar12546Home Loan
Raju11,Kannan nagar12546PersonalLoan
Raju11,Kannan nagar12546Mortgage

My desired output is as follows.

NameAddressZipProdProd_number
John110,KX Colony11198PersonalLoan1
Kiran7/189 Rajaji Road11094Mortagge1
Kiran7/189 Rajaji Road11094Creditcard2
Amar16,Mt.batten Street12409Mortagage1
Raju11,Kannan nagar12546Home Loan1
Raju11,Kannan nagar12546PersonalLoan2
Raju11,Kannan nagar12546Mortgage

3

When I ran the code below, I'm not getting the desired output. Shouldn't we use lag function with by clause?

data new_hhlevel_prod;

set hhlevel_prod;

by name ADDRESS zip  notsorted;

if prod=lag(prod) then prod_number=1;

  else prod_number+1;

run;

Please suggest me to get the desired output.

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

If you are using BY then you do not need to use LAG() to check for changes.  SAS will have already done that and set the FIRST. and LAST. variables to indicate when values change.

So let's assume that NAME,ADDRESS,ZIP define a customer and you want to count the distinct products for that customer.

There is some risk in using the NOTSORTED option because it might be possible that the records for one customer are separated by the records for another customer.  In that case you could end up with two different values of PROD for that same customer getting PROD_NUMBER set to one.

data want ;

  set have ;

  by name address zip prod notsorted;

  if first.zip then prod_number=1;

  else prod_number+first.prod;

run;


View solution in original post

13 REPLIES 13
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10

BY statement processing and LAG function are unrelated - see using the FIRST.<break_variable> instead.

Also, from your data example, the ADDRESS-related attribute is influencing PROD_NUM data-value counting, correct?

Scott Barry

SBBWorks, Inc.

Kurt_Bremser
Super User

First, I would not use notsorted, instead I would sort by all the by criteria already used and by prod(!), to avoid the same product being counted twice if it is not in consecutive observations.

Then do the following:

data new_hhlevel_prod;

set hhlevel_prod;

by name ADDRESS zip;

oldprod = lag(prod);

if first.zip then do;

  prod_number = 0;

  oldprod = '';

end;

if prod ne oldprod then prod_number+1;

run;

(Assuming you wanted to get a count of the number of distinct prods per customer)

sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10

LAG should not be necessary, given that FIRST.<break_varname> will detect the condition explained as:  "current value equal to prior observation's value" based on the BY variable list shown.

Babloo
Rhodochrosite | Level 12

May I question why you will not use 'not sorted' option as you can skip one proc step if you use this option?

sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10

The presumption is your data does appear to be sorted and you need not use PROD in your BY list -- again, the use of BY and LAG are unrelated.

Suggestion would be to read up on the DATA step technique related to the DATA step elements being used.

Also, suggest you might add to your DATA step some PUTLOG "I am here" _ALL_;   statements for desk-checking with your DATA step operational execution processing to learn more about the BY statement use and how it is exploited and for what purposes -- that being to interrogate FIRST. and LAST.  "break variable" conditions (those variables on the BY statement).

Kurt_Bremser
Super User

Imagine your file looks like that:

customer prod

a     x

a     y

b     x

a     y

the notsorted option would give you two counts for customer a, one where (a,y) had 1, and one where (a,y) has 2. According to my view of your task, you would want both (a,y) record to have the count of 2.

Babloo
Rhodochrosite | Level 12

Apologies, I missed this point. There are duplicates by name, address, zip and product. In that case I no need to increase the count of the new variable prod_number. It should be displaying as 1.

data have:

NameAddressZipProd
John110,KX Colony11198PersonalLoan
John110,KX Colony11198PersonalLoan
Kiran7/189 Rajaji Road11094Mortagge
Kiran7/189 Rajaji Road11094Creditcard
Amar16,Mt.batten Street12409Mortagage
Raju11,Kannan nagar12546Home Loan
Raju11,Kannan nagar12546PersonalLoan
Raju11,Kannan nagar12546Mortgage
Raju11,Kannan nagar12546Mortgage

data want:

NameAddressZipProdProd_number
John110,KX Colony11198PersonalLoan1
John110,KX Colony11198PersonalLoan1
Kiran7/189 Rajaji Road11094Mortagge1
Kiran7/189 Rajaji Road11094Creditcard2
Amar16,Mt.batten Street12409Mortagage1
Raju11,Kannan nagar12546Home Loan1
Raju11,Kannan nagar12546PersonalLoan2
Raju11,Kannan nagar12546Mortgage3
Raju11,Kannan nagar12546Mortgage3
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10

Your most effective/efficient approach is not to use LAG but focus on using / exploiting your BY statement variable list and the accompanying FIRST.<break_varname>  DATA step IF/THEN/ELSE coding technique.  Have a look at the SAS Language Elements for relevant information and examples.  And yes LAG is another example, but it has no relevance to also using BY statement coding technique.

Babloo
Rhodochrosite | Level 12

I got my desired output with this code. Can we still make it simple with one if clause?

data have;

infile cards dsd dlm=',';

input Name $ Address $ Zip    Prod $;

length address prod $25.;

cards;

John,110KX Colony,11198,PersonalLoan

Kiran,7/189 Rajaji Road,11094,Mortagge

Kiran,7/189 Rajaji Road,11094,Creditcard

Amar,16Mt.batten Street,12409,Mortagage

Amar,16Mt.batten Street,12409,Mortagage

Raju,11Kannan nagar,12546,Home Loan

Raju,11Kannan nagar,12546,PersonalLoan

Raju,11Kannan nagar,12546,Mortgage

Raju,11Kannan nagar,12546,Mortgage

;

run;

data want;

set have;

LAG_PROD= lag(prod);

by name ADDRESS zip prod  notsorted;

if first.name and first.address and first.zip and first.prod then prod_number = 1;

else prod_number+1;

if name=lag(name) and address=lag(address) and zip=lag(zip) and prod=lag(prod) then new_prod_number=1 ;

else new_prod_number=prod_number;

run;

sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10

I encourage you to learn the SAS programming language, as much through help from this list, but also with your own desk-checking and DOC-review.

You don't need LAG and if you were to add a PUTLOG _ALL_ ;  statement at various points in your DATA step coding logic, you would learn/understand why not.

Your SAS code that assigns PROD_NUMBER based on FIRST.<break_varname> tests is sufficient -- meaning no LAG required.  And you do not need to test each BY variable -- only the "significant" variable in the list that would influence your DO/END or otherwise some SAS assignment logic -- in your case that is the PROD_NUMBER assignment, either as a first-occurrence value of 1 or to increment.

Please take the opportunity and review the available SAS DOC and examples.

No doubt you will be given the answer at some point, if you continue to throw out code-pieces not thoroughly explored / tested on our own accord.

Also, the location of the assignment statement that uses LAG function has no bearing on the location of the BY statement at all.

Astounding
PROC Star

While I agree with sbb ... it is important that you learn about FIRST. and LAST. ... here is a program that should fit your example.  It assumes your data are properly sorted:

data want;

   set have;
   by name address zip prod notsorted;

   if first.zip then prod_number=1;

   else if first.prod then prod_number + 1;

run;

Good luck.

Steelers_In_DC
Barite | Level 11

You should define your lag variable before doing anything else with it.

Here's a solution for you but it doesn't agree with your output:

data have;

infile cards dsd;

length address prod $25.;

input Name $ Address $ Zip    Prod $;

cards;

John,110,KX Colony,11198,PersonalLoan

Kiran,7/189 Rajaji Road,11094,Mortagge

Kiran,7/189 Rajaji Road,11094,Creditcard

Amar,16,Mt.batten Street,12409,Mortagage

Raju,11,Kannan nagar,12546,Home Loan

Raju,11,Kannan nagar,12546,PersonalLoan

Raju,11,Kannan nagar,12546,Mortgage

;

run;

data want;

set have;

LAG_PROD= lag(prod);

by name ADDRESS zip prod  notsorted;

if first.prod then prod_number = 1;

if prod=LAG_PROD then prod_number+1;

run;

Tom
Super User Tom
Super User

If you are using BY then you do not need to use LAG() to check for changes.  SAS will have already done that and set the FIRST. and LAST. variables to indicate when values change.

So let's assume that NAME,ADDRESS,ZIP define a customer and you want to count the distinct products for that customer.

There is some risk in using the NOTSORTED option because it might be possible that the records for one customer are separated by the records for another customer.  In that case you could end up with two different values of PROD for that same customer getting PROD_NUMBER set to one.

data want ;

  set have ;

  by name address zip prod notsorted;

  if first.zip then prod_number=1;

  else prod_number+first.prod;

run;


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
  • 13 replies
  • 2812 views
  • 2 likes
  • 6 in conversation