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!
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
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.
data want; set have; if _n_=1 then do; declare hash h (dataset:'have(obs=0)'); h.definekey(all:'Y'); h.definedone(); end; h.add(); 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-' run;
Of course, in the absence of sample data in the format of a working DATA step, this code is untested.
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.
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.