BookmarkSubscribeRSS Feed
Obsidian | Level 7

I have a dataset of car sales by various locations, by month. I'm trying to see if a location sold the same model and color in consecutive months. Example below. 



I would want to only keep the locations that have sold the same make, model, color car in both January and February.


What's a good way to do this considering there are 100's of thousands of records to go through? 


Thanks in advance!




Diamond | Level 26

We can't see your screen capture, and even if we could see it, we cannot work from data in a screen capture.


Data should be provided as working SAS data step code (examples and isntructions). DO NOT PROVIDE DATA IN ANY OTHER FORMAT

Paige Miller
Super User

Is your "month" variable actually a SAS date, that can reliably tell us January or February or just some random number?

Do you have data extending across more than one year? (goes to which January and February might be involved)?

Do you actually have "months" other than January and February (assuming ending in 01 means Jan and 02 means Feb)?


Actually concerned with Jan and Feb or consecutive months? Different approaches. The values of month are critical if you expect month to cross calendar year such as Dec to Jan and treat them as consecutive. 202112 is generally not followed by 202201 (not consecutive) which is why see need to know about your date variable in more detail.


Jade | Level 19


  1.  Your data are sorted chronologically
  2. MONTH is stored as a sas date variable, always taking the 1st day of the indicated month,


data want;
  set have;
  if _n_=1 then do;
    declare hash h (dataset:'have(obs=0)');
  if h.check(key:location,key:make,key:model,key:color,key(intnx('month',month,-1))=0 
     then sold_same_model_last_month='Yes'
     else sold_same_model_last_month='No-'

Of course, in the absence of sample data in the format of a working DATA step, this code is untested.

The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets




Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

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.

Get the $99 certification deal.jpg



Back in the Classroom!

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

View all other training opportunities.

Discussion stats
  • 3 replies
  • 4 in conversation