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

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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

9 REPLIES 9
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.  

ss59
Obsidian | Level 7

@RW9, @Kurt_Bremser 

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************/

 

Kurt_Bremser
Super User

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.

ss59
Obsidian | Level 7

@Kurt_Bremser@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?

Kurt_Bremser
Super User

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';

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

Astounding
PROC Star

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);

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

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
  • 9 replies
  • 2965 views
  • 3 likes
  • 5 in conversation