How do I use current date to identify tables to append to a new table

[jlhutch]

Another newbie question. I'm working in SAS Viya and asked a similar question a several months ago and have tried to implement a similar solution for this problem, but haven't had any success. This is what I have:

data work.last_per_month;

month = .;

year = .;

  set work.last_event_01JUN2022

work.last_event_01JUL2022

work.last_event_01AUG2022

work.last_event_30SEP2022

/*work.last_event_31OCT2022

work.last_event_30NOV2022

work.last_event_31DEC2022

work.last_event_31JAN2023

work.last_event_28FEB2023

work.last_event_31MAR2023

work.last_event_30APR2023

work.last_event_31MAY2023 */;

month = month(asof);

year = year(asof);

run;

Each month I shift to comment to the next line, but what I would like to be do is check the current date and only pull in the data for months are before today's date. 

[PaigeMiller · Accepted solution]

Why is it that sometimes the date is the first day of the month, and other times it is the last day of the month. How would a SAS program know which to use?

Would something like this work, which selects all data sets in the library whose name begins with work.last_event_ ??

data want;
    set work.last_event_:;
run;

[jlhutch]

The reason it's sometimes the beginning of the month and sometimes the end is because I got a little happy fingers with my copy/paste. Your solution looks better than I had any hope for.

Thank you!

[mkeintz]

Although the use of

    set work.last_event_:;

will read all datasets whose name starts with "last_event_", it will generate different results than 

  set work.last_event_01JUN2022
      work.last_event_01JUL2022
      work.last_event_01AUG2022
      work.last_event_30SEP2022 ;

First, if there are similarly-named datasets already present for later dates, they will be included even if not in your original list.

But even if such unlisted datasets don't exist, the resulting data will be differently ordered.  The observations will not be in the chronological order implied by your original list of datasets.  Instead, the data will be in a sequence based on the lexicographic order of the dataset names (i.e. in alphabetic order of dataset names).

Now, if data order doesn't matter, then you don't have a problem.  But if it does, you might be better off making dataset names whose alphabetic order would match the chronological order.  Something like

  set work.last_event_20220601   /*instead of 01JUN2022*/
      work.last_event_20220701   /*instead of 01JUL2022*/
      work.last_event_20220801   /*instead of 01AUG2022*/
      work.last_event_20220930   /*instead of 30SEP2022*/
     ;

for which

  set work.last_event_2022: ;

would generate the same data, in the same order.

[jlhutch]

I'm kind of stuck with the naming convention, but since those additional tables won't exist until the dates are past, the solution you provided should work

 set work.last_event_:;

Thank you for taking the time to explain what code I can use and why it should work for me!

[ballardw]

Possibly a silly question: You show a bunch of data sets in the WORK library. Are you rereading data from external files that is that old? If the data actually changes that's one thing but if the data hasn't then the files should have been read into a permanent library. Then you create one appended data set in the permanent library. Append the new data as it is read.

Second question: You are asking this question on 01AUG2022 but include multiple data sets with names in the future and apparently are including the 30SEP2022 as a set " pull in the data for months are before today's date."  So how does 30SEP2022 get to be "before today's date"?

Third question: Do these data sets even exist?

/*work.last_event_31OCT2022
work.last_event_30NOV2022
work.last_event_31DEC2022
work.last_event_31JAN2023
work.last_event_28FEB2023
work.last_event_31MAR2023
work.last_event_30APR2023
work.last_event_31MAY2023 */;

The current names you use, if they are of your construction, have many issues such as @PaigeMiller brought up plus sorting and a few other issues.

If those sets in the /* */ do not even exist and you had a nicer naming convention you might be able to use a fairly simple list constructor.

If your names ended in YYYYMMDD such as 20220801 instead of 01AUG2022 you could use a list such as

set  work.last_event_2022: ;

Which would use all of the sets whose names start with work.last_event_2022   . As I said, this is if those sets don't exist yet.

[jlhutch]

Not silly questions at all

1) They are not old or external datasets, they created earlier in the program to get month specific events.

2) The data for the extra months don't exist now, they are created earlier in the program, but only if there are events for those months.

3) No, those future months don't exist now, but will later. I'm trying to avoid editing that section of code every month.

I'm kind of stuck with the naming construct, but since the other tables won't exist until those dates are past, the solution you provided will work for me.

Thank you for taking the time to explain what code I can use and why it should work for me!