Assinging Variables Based On Table Values

[mhoward2]

Hi all!

What I am looking to do is assign variables based on table variables. I'm not sure this is possible but if not please would love to know the best way to go about it. Here's the deal:

I have a table that looks like this:

Run_Month	Inc_Start
1	BaseYear & 0131
2	Year & 0228
3	Year & 0331

And I have Month and Year variables using the following:

%LET Run_Month = %SYSFUNC(month("&sysdate"d));
%LET Year = %SYSFUNC(year("&sysdate"d));
%LET Base_Year = %EVAL(&Year - 1);

And what I want is to use the &Run_Month variable to go into the table, find the corresponding entry and then assign a new variable called &Inc_Start as the value in the table, except concatenating the correct &Year variable with the 4 digits in the entry.

So if &Run_Month was 2 and &Year was 2021, then my &Inc_Start variable would = '20210228'

Does that make sense?

Thanks in advance!

[Astounding]

I get the general idea of what is in your table, but the details are a little fuzzy.  I can give you an approach that matches my suspicions about what is in your data.

From your table, create a set of 12 macro variables:

data _null_;
   set have;
   call symputx(cat('EndM', Run_Month), Inc_Start);
run;

This gives you a set of 12 macro variables, equivalent to:

%let EndM1 = 0131;

%let EndM2 = 0228;

etc.

If I'm wrong about that result, you might have to adjust the DATA step to pick out the right pieces from your data.

Once you have processed your other macro variables to get the month and year, it should be simple enough to retrieve the pieces you want:

%let Inc_start = &Base_year&&EndM&month;

[mhoward2]

Thanks for the reply! I wonder about your final statement that sets the &Inc_Month variable, as the table shows, the &Inc_Month variable might not always use &Base_Year, it may sometimes use &Year, depeinding on the Run_Month.

Does that make sense?

[Astounding]

Yes, there's an issue there.  Let's start with clearing up the fuzzy parts.

What exactly is in your table?  Is Inc_Start exactly the characters that you posted (including words like Year, Baseyear, and &)?

[mhoward2]

Yes my table looks exactly like that, but can be changed to values if it would make what I'm trying to do here easier. I just want to be able to look up a &Run_Month value and assign a new variable based on the value in the Inc_Start field.

So again if &Run_Month = 2 and &Year = 2021, the Inc_Start field would equal "Year & 0228". What I want the new variable, &Inc_Start, to just follow the "Year & 0228" formula. Meaning I want it to resolve to 20210228.

Thanks again for the help I hope what im saying makes sense!

[Astounding · Accepted solution]

Given that you have control over your table structure, I would change it so it holds three variables:

MONTH (numeric, with a value of 1 through 12)

PART1 (character, holding either "Year" or "BaseYear")

PART2 (character, holding the day and year, such as "0131", "0228", "0331" etc.)

Then you could extract the right pieces using:

data _null_;
   set have;
   where month=&run_month;
   call symputx('part1', part1);
   call symputx('part2', part2);
run;

%let inc_start = &&&part1..&part2;

If you can't control the table structure in this way, it can still be done with slightly more complex formulas inside SYMPUTX.  But the new structure would be clearest for future use.

This would be a good time to consider what happens when your processing can span multiple years.  Could February end with 0229?  Might you need more than 12 observations in your table?  

[mhoward2]

Yup I split out the years vs end dates and your code worked like a charm! Thank you so much for stopping by, I greatly appreciate it!