sas macro - increment variable with a condition

Accepted Solution Solved
Reply
Contributor
Posts: 42
Accepted Solution

sas macro - increment variable with a condition

Hi all,

 

I'm trying to write a SAS macro for a time series and my time variable is "year_period", i.e. 20171, 20172, 20173, 20174, 20181, .....

 

I'm having a hard time to increment the variable. I'm trying to accomplish something like:

 

%macro projections(year_period);

%DO i = 1 %TO 8;

/********code block*****/

%let if mod(year_period,10) LE 3 then year_period = %eval(year_period +1);
%let if mod(year_period,10) > 3 then year_period = %eval(year_period +7);

%END;
%mend projections;

But the %let and %eval statements are not correct.Could you please help?


Accepted Solutions
Solution
‎01-21-2018 06:19 AM
Super User
Posts: 6,005

Re: sas macro - increment variable with a condition

If you are content to keep the processing in macro language, remember that macro variables are strings, not numbers.  So you could go this route:

 

%if %substr(&year_period, 5, 1) le 3 %then %let year_period = %eval(&year_period + 1);

%else %let year_period = %eval(&year_period + 7);

View solution in original post


All Replies
Super User
Posts: 8,590

Re: sas macro - increment variable with a condition

This looks more like data processing, which should be done in a data step, not in a macro.

 

What for do you do this?

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Super User
Posts: 8,634

Re: sas macro - increment variable with a condition

Provide test data in the form of a datastep, and what you want out at the end.  Macro language is not useful for data processing, it is there to aid in generating text - i.e. its nothing more than a find/replace system.  

Contributor
Posts: 42

Re: sas macro - increment variable with a condition

[ Edited ]

@RW9, @KurtBremser 

To be more clear, this is what I am trying to do:

I have a file called Credit_risk_20164.

 

Parameter file has all the relevant periods but I need to update the variables from the credit_risk file for the next period and merge in relevant parameters from the next period. I want to generate projections from 20171 to 20184 

 

%macro projections(year_period);
 
%DO i = 1 %TO 8;

%let if mod(year_period,10) LE 1 then prevyear_period = %eval(year_period -7);
%let if mod(year_period,10) > 1 then prevyear_period = %eval(year_period -1);

/***increment time in credit risk***/

data credit_risk_&year_period;
set credit_risk_&prevyear_period;
if mod(year_period,10) LE 3 then year_period_new = year_period +&i;
if mod(year_period,10) > 3 then year_period_new = year_period +6+&i;
drop year_period;
rename year_period_new = year_period;
run;

/****join it with parameters for that time period***/

proc sql ;
create table credit_risk_&year_period as 
select a.* , b.*
from credit_risk_&year_period as a left join parameters as b
on a.country = b.country and a.segment_combo = b.segment_combo and a.year_period = b.year_period and a.IND_CRE_IMPAIRMENT = b.IND_CRE_IMPAIRMENT and a.CRE_LTV_BAND = b.CRE_LTV_BAND and a.CRE_GEOGRAPHY_ZONE = b.CRE_GEOGRAPHY_ZONE;
quit;

/**********calculation of stocks*************/

/**********output the end state results for the period*******/

proc export
  data=credit_risk_&year_period
   dbms=XLSX
  outfile="C:\Users\credit_risk"
  replace; SHEET="Results_&year_period";
run;

/*********increment the period*********/

%let if mod(year_period,10) LE 3 then year_period = %eval(year_period +1);
%let if mod(year_period,10) > 3 then year_period = %eval(year_period +7);

%END;
%mend projections;

projections(20171);

/*********should give me results till 20184 and separate sheets in the credit_risk.xlsx file************/

 

Super User
Posts: 8,590

Re: sas macro - increment variable with a condition

[ Edited ]

First start out by making intelligent data out of dumb data.

Replace your period values with SAS date values, and assign a format of yyq6. to them. You can now use the intnx() function to increment periods, and don't need to use all those complicated calculations, which don't make sense to me anyway:

if mod(year_period,10) LE 3 then year_period_new = year_period +&i;

If &i = 4 and year_period = 20172, you'd get 20176, which is not a valid quarter.

If year_period were '01apr2017'd (displaying as 20172 when format yyqn5. is used), then

year_period_new = intnx('quarter',year_period,&i,'begin');
format year_period_new yyqn5.;

would get you a date that displays as 20182

 

Edit: changed format from yyq6. to yyqn5. and displayed values accordingly.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 42

Re: sas macro - increment variable with a condition

[ Edited ]
Posted in reply to KurtBremser

@KurtBremser@RW9, this is not working as expected. But I have a more basic question. I have variable "year" as a string "2017". "2018" etc and variable "period" as a number 1,2,3 or 4.

 

I want to create a date (sas date format) with yyyyq or yyyyqq.

 

I'm trying the following but not working:

 

data credit_risk_2;
set credit_risk;
period_new=put(period,2.);
drop period;
rename period_new = period;
newdate = put(cats(year,period), YYQN5.);
date = intnx("QTR", newdate, 0);
format newdate yyq.;
run;

What am I doing wrong?

Super User
Posts: 8,590

Re: sas macro - increment variable with a condition

Do it this way:

newdate = input(year !! 'Q' !! put(period,1.),yyq6.);
format newdate yyqn5.;

for year = '2017' and period = 1, you will get a SAS date 01jan2017, formatted as '20171';

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 21,546

Re: sas macro - increment variable with a condition

Super User
Super User
Posts: 8,634

Re: sas macro - increment variable with a condition

[ Edited ]

Yes, this type of thing;

Credit_risk_20164

 

Seems to be standard in finance, against all logic or reason - actually re-reading that, logic and reason comes from Excel and prevalent use of Excel leading to these ways of thinking.  Me, I would put all my Credit_risk data together in one dataset, with a variable which contains the 20164 as a date.  You can then remove all the macro code, and do a simple merge of parameters, and you can export it to excel using by group of the data, and sheet_interval="bygroup" in proc report (or by group out to libname excel).  Far simpler, easier to maintain, and expandable.

Solution
‎01-21-2018 06:19 AM
Super User
Posts: 6,005

Re: sas macro - increment variable with a condition

If you are content to keep the processing in macro language, remember that macro variables are strings, not numbers.  So you could go this route:

 

%if %substr(&year_period, 5, 1) le 3 %then %let year_period = %eval(&year_period + 1);

%else %let year_period = %eval(&year_period + 7);

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 170 views
  • 3 likes
  • 5 in conversation