Search code examples
sqlproc-sql

Creating an indicator based on the same ID in different years in PROC SQL/SQL

I'd like to create an indicator based on the ID and product type. My data:

Year ID Purchase_Category
2020 1  Kitchen
2020 2  Home
2020 2  Kitchen
2020 3  Home
2021 1  Home
2021 2  Kitchen
2021 3  Kitchen

If someone with the same ID purchased Kitchen in 2020 and then Home in 2021 or vice versa, then they are deemed holistic. ID 2 in this case is not holistic because Home and Kitchen were purchased in the same year. The output should look like this:

ID Indicator
1  Holistic
2  Not Holistic
3  Holistic
Solution

  • Something like this might work:

    SELECT ID, CASE COUNT(*) WHEN 1 THEN 'Not Holistic' ELSE 'Holistic' END AS INDICATOR
FROM (SELECT ID, YEAR, COUNT(*) FROM DATA GROUP BY ID, YEAR)
GROUP BY ID

    First, determine the distinct years per ID, then from that set, if an ID appears only once then everything was purchased in same year, otherwise there were products purchased in different years.