Hi,
Please can someone help to provide suggestions/code for me create a reference table that can be updated every month, based on the values that are in a variable?
I currently have the code below, where I am trying to identify any new values for the 'Colour' variable (characteristic variable) that have not appeared my dataset (which gets updated every month to include the latest month) before. The reference table will be updated so that it will contain all of the values that have appeared before, but the code should allow me to add on any new values to the list.
As April and May is the first time that I have checked, the reference table that will be created should store all of the 'Colour' values from April and then if there are any new 'Colour' values in May, this will be added onto the reference table. As a result, I can substitute the dataset in my proc sql code below and compare my 'Test' dataset to the reference table that will be created.
For example, if accounts in April have 'Colour' values of Red, Blue, Green, Yellow and Orange, then the initial reference table created will contain all of these. If May in my 'Test' dataset has a new value of Purple, the reference table will add on Purple, so it will now have Red, Blue, Green, Yellow, Orange and Purple, which I can compare against and update every month in the future.
Code:
proc sql;
select distinct Colour
from Test
where "01May2021"d<=MONTH<="31May2021"d
except
select distinct Colour
from Test
where "01Apr2021"d<=MONTH<="30Apr2021"d
;
quit;
/*Proc freq of more recent month*/
proc freq data=Test;
tables Colour;
where "01May2021"d<=MONTH<="31May2021"d;
run;
/*Proc freq of older month*/
proc freq data=Test;
tables Colour;
where "01Apr2021"d<=MONTH<="30Apr2021"d;
run;
... View more
