Use existing table or combine tables based on year

[wil1212]

Hello, 

I need to sum revenue each month to accumulate the revenue for the year. The revenue is reported from Feb of the current year to Jan of the next year. So, to sum revenue for 2022, I need two billing tables (Billing2022 and Billing 2023). I am automating this code using Marco variables, so I do not have to update the dates each year. How do I create code that tells SAS to use the Billing2022 table if the monthyear is >= Feb2022? But if the monthyear = Jan2023, I want to combine the two tables (Billing2022 and Billing 2023) together. I am familiar with SAS and SQL. Thank you. 

[Kurt_Bremser]

In which form do you get monthyear? As variable in a dataset, a macro variable, or something else?

[wil1212]

It's a variable in the dataset.

The billing2023 table will not be created until Jan 2023 (billing for services used in Dec 2022). I thought about creating an empty table for Billing2023, but I want to automate this process and not have to update it each month. I want it to only use the Billing2023 once it has been created. My data has a date variable for the 1st of each month.

[Tom]

Just don't include that dataset when it does not exist.

%let fy=2022;
%let next=%eval(&fy+1);
data want;
  set billing&fy 
%if %sysfunc(exist(billing&next)) %then billing&next ;
  ;
  ....

[Kurt_Bremser]

---

@wil1212 wrote:

It's a variable in the dataset.

---

This makes no sense. You need to know the date to decide which dataset(s) to read, so you can't read it from this (or those) dataset(s).

[Tom]

It depends on the structure of your datasets.  If the data has a DATE variable perhaps you just need to apply a WHERE filter?

For example if you wanted the data for FY2022 you might just want to do something like:

data want;
  set billing2022 billing2023;
  where '01FEB2022'd <= date < '01MAR2023'd ;
run;

So if you had a macro variable with the value 2022 you could generate that with code like:

data want;
  set billing&fy. billing%eval(&fy+1);
  where "01FEB&fy."d <= date < "01MAR%eval(&fy+1)"d ;
run;

[wil1212]

The billing2023 table will not be created until Jan 2023 (billing for services used in Dec 2022). I thought about creating an empty table for Billing2023, but I want to automate this process and not have to update it each month. I want it to only use the Billing2023 once it has been created. My data has a date variable for the 1st of each month.  

[ballardw · Accepted solution]

---

@wil1212 wrote:

The billing2023 table will not be created until Jan 2023 (billing for services used in Dec 2022). I thought about creating an empty table for Billing2023, but I want to automate this process and not have to update it each month. I want it to only use the Billing2023 once it has been created. My data has a date variable for the 1st of each month.  

---

Here is one example of something similar to what you are requesting:

/* create some example data sets*/
data work.set23;
   x=23;
run;
data work.set24;
   x=24;
run;
data work.set25;
   x=25;
run;

/* macro that builds a list of sets IF they exist
using sequential parameters
*/
%macro seqlist(basesetname= ,start=, end=);
%let namelist=;
%do i=&start. %to &end.;
   %if %sysfunc(exist(&basesetname.&i.))=1 %then 
       %let namelist= &namelist. &basesetname.&i. ;
%end;
 &namelist. 
%mend;

data combined;
   set 
%seqlist (basesetname=work.set, start=23, end= 26)
   ; /* this ; ends the SET statement*/
run;

The three data sets are so we have something to use. The macro looks at sequentially numbered data sets providing a base value such as libname.dataset, an start and end parameters.

The macro variable namelist holds  values that actually exist, space delimited. The use of the &namelist. without a ; means that is the value exposed to the code processor when the macro is called.

Note that the example specifically requests a data set that does not exist, the one that should end in 26, but the log from combining the data sets will show that it was not used as a source.

This macro is moderately generic. You could hard code the data basesetname value and even start and end values to where the code may not need any change depending on what you do such as which could place all the billing sets from 2010 to 2200 (when it exists) into the list. This is likely a bit cumbersome and having the start at least as a parameter is more flexible.

%macro billinglist;
%let namelist=;
%do i=2010 %to 2200;
   %if %sysfunc(exist(billing.&i.))=1 %then 
       %let namelist= &namelist. billing.&i. ;
%end;
 &namelist. 
%mend;

Caution: using data set name lists generated like this may have issues if the variable types ever change.

[wil1212]

Thank you so much! This works.