%let statement with some conditions

[Kayla_Tan222]

Hi all, I  want to create a %let statement so that everytime when I run file with the same code I just need to enter the year and date I want.

However, I facing a problem at the last part. my aim is to for ex:

if the month MM=01, my year will be deducted by one and the month will become December instead of January in the set statement like this -> polaclm.pola_clm_agg_mth_201712  and another file I want to combine with this file is  polaclm.pola_clm_mth_201801 which is same as the date I set at the beginning.

But for the rest of the month, for ex: YYYY=2018, MM=07, the files name will become like this below

 polaclm.pola_clm_agg_mth_201806 (only the month deducted by 1) and polaclm.pola_clm_mth_201807 (same as the number I set at the beginning also)

I put data statement in my if statement because I want to save it as intermediary. however it is not work. Whats wrong with  my code below?

%let YYYY=2018;

%let MM=01;

　

libname polaclm C:\Data\POLA\FILE\";

Data polaclm.POLA_clm_mth_&YYYY&MM.;

(BODY.......)

if &MM.=01 then

data polaclm.POLA_clm_agg_&YYYY&MM.;

set

polaclm.POLA_clm_agg_(&YYYY-1)&MM.

polaclm.POLA_clm_mth_&YYYY&MM.;

else

data polaclm.POLA_clm_agg_&YYYY&MM.;

set

data polaclm.POLA_clm_agg_&YYYY(&MM.-1)

polaclm.POLA_clm_mth_&YYYY&MM.;

　

RUN;

[Shmuel]

Replace the two %LET lines into a macro:

%macro date(y=, m=);
%global yyyy mm;
   %if &m = 01 %then %do;
         %let yyyy = %eval(&y - 1);
         %let mm = 12;
    %end; %else %do;
         %let  yyyy = &y;
          %let mm = &m;
%mend date;
%date(y=2018, m=01);

[Kayla_Tan222]

Hi Shmuel,

First of all , Thanks for your reply. Your method is work. However , when I combine the two files I want , the  two file chosen is

pola_clm_agg_mrh_201812 (correct) and pola_clm_mth_201801 (incorrect, I want 201901)

my set statement is below:

%macro date(y=2019, m=01);%global yyyy mm;

%if &m = 01 %then %do;

%let yyyy = %eval(&y - 1);

%let mm = 12;

%end; %else %do;

%let yyyy = &y;

%let mm = %eval(&m-1);

%end;

%mend date;

%date(YYYY=2019, MM=01);

data polaclm.pola_clm_agg_&y&m.;

set

polaclm.pola_clm_agg_&yyyy&mm.         *I want this to be 201812

polaclm.pola_clm_mth_&y&m.;           *I Want this to be 201901

　

RUN;

Is there anything wrong?

[Shmuel · Accepted solution]

There are two ways to solve your need:

1) create 4 macro variables (2019, 01, 2018, 12) 

%macro date(y=, m=);
   %global yyyy mm orgy orgm;  /* org = original value */
   %let orgy = &y;
   %let orgm = &m;
   %if &m = 01 %then %do;
         %let yyyy = %eval(&y - 1);
         %let mm = 12;
    %end; %else %do;
         %let  yyyy = &y;
         %let mm = &m;
    %end;
%mend date;
%date(y=2019, m=01);

data polaclm.pola_clm_agg_&orgy.&orgm.;
 set polaclm.pola_clm_agg_&yyyy.&mm.         
     polaclm.pola_clm_mth_&orgy.&orgm.; 
 .....
run; 

2) or - enter your code into a macro program

%macro date(y=, m=);
   %global yyyy mm 
   %let orgm = &m;
   %if &m = 01 %then %do;
         %let yyyy = %eval(&y - 1);
         %let mm = 12;
    %end; %else %do;
         %let  yyyy = &y;
         %let mm = &m;
    %end;
data polaclm.pola_clm_agg_&y.&m.;
 set polaclm.pola_clm_agg_&yyyy.&mm.         
     polaclm.pola_clm_mth_&y.&m.; 
 .....
run; 	
%mend date;
%date(y=2019, m=01);

[Tom]

It is easier to manipulate dates if you turn them into date values.

Also it does not look like you need separate year and month macro variables.

So let's just make two macro variables one for the current month and one for the month before.

%let current=201801;
%let previous=%sysfunc(intnx(month,%sysfunc(inputn(&current,yymmn6)),-1),yymmn6);

If you really need to pull out the year and month values just use %SUBSTR().

%let pyear=%substr(&previous,1,4);
%let pmonth=%substr(&previous,5,2);