BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
mhoward2
Obsidian | Level 7

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!

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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?  

View solution in original post

6 REPLIES 6
Astounding
PROC Star

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
Obsidian | Level 7

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
PROC Star

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
Obsidian | Level 7

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
PROC Star

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
Obsidian | Level 7

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!

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 772 views
  • 0 likes
  • 2 in conversation