topic Create series if macro varaibles for dates values in data set in SAS Programming

Create series if macro varaibles for dates values in data set

Ronein — Wed, 26 Sep 2018 05:20:57 GMT

Hello

I have a data set that contain dates( This data set is created automatically by enter start and end dates ).

The task is to create sas macro varaibles:

First date value will be in macro varaible called: m1

second date value will be in macro varaible called: m2

and do on

How can I do it please?

/*create date series from start and end dates*/
%let start_date=01sep2017;
%let end_date=01sep2018;


/*create dates series from start and end dates-Jump in months*/
data want_month;
date="&start_date"d;
date_char=put("&start_date"d,yymmn4.);
do while (date<="&end_date"d);
    output;
    date=intnx('month', date, 1, 's');
	date_char=put(date,yymmn4.);
end;
format date yymmn4.;
run;

Re: Create series if macro varaibles for dates values in data set

ChrisNZ — Wed, 26 Sep 2018 05:34:55 GMT

Your data step can be simplified:

data WANT_MONTH;
  do DATE="&start_date"d to "&end_date"d;
    DATE_CHAR=put(DATE,yymmn4.);
    output;
    DATE=intnx('month', DATE, 0, 'e');
  end;
  format DATE date9.;
run;

You don't need it to create the macro variables, but if you just want to add their creation as an extra benefit during the creation of the table, this works:

data WANT_MONTH;
  do DATE="&start_date"d to "&end_date"d;
    DATE_CHAR=put(DATE,yymmn4.);
    output;
    N+1;
    call symput (catt('M',N),DATE_CHAR);
    DATE=intnx('month', DATE, 0, 'e');
  end;
  format DATE yymmn4.;
run;

Re: Create series if macro varaibles for dates values in data set

Ronein — Wed, 26 Sep 2018 07:29:04 GMT

Thanks.

When I run it I don't see the we get the desired outcome.

As you can see &m0 &m1 &m2 ......were not created

%let start_date=15sep2017;
%let end_date=01sep2018;


data WANT_MONTH;
  do DATE="&start_date"d to "&end_date"d;
    DATE_CHAR=put(DATE,yymmn4.);
    output;
    N+1;
    call symput (catt('M',N),DATE_CHAR);
    DATE=intnx('month', DATE, 0, 'e');
  end;
  format DATE yymmn4.;
run;
%put &m0;
%put &m1;
%put &m2;

Re: Create series if macro varaibles for dates values in data set

RW9 — Wed, 26 Sep 2018 07:57:58 GMT

Where to start, ok the bit that gets ignored. Putting data into lots of macro variables, especially numeric dates in character macro variables, is really really not a good idea.

Onto your code. N is never initialised, therefore is always missing. It is also never retained anyways, so would not increment. You do not need most of that code which consists of writing a dataset, as you do not use that dataset. What is it you expect this code to do?

DATE=intnx('month', DATE, 0, 'e');

All that will do is set the date to be the end of the month, however your loop is operating on days, numeric dates are the number of days since the cuttoff, so m0=day 0, m1=day 1, m2=day 2 - they are all in month Sep though, so the output will be number of days between start and stop = how many macro variables you create. What I suspect you want is:

%let start_date=15sep2017;
%let end_date=01sep2018;

data _null_;
  do i=1 to intck('month',"&start_date."d,"&end_date."d);
    call symput(cats('M',put(i,best.)),put(intnx('month',"&start_date."d,i,'e'),yymmn4.));
  end;
run;
%put &m0.;
%put &m1.;
%put &m2.;

Do note to finish macro varaibles with a decimal point, and avoid coding in uppercase for readability. And again, this really is not a good idea.

Re: Create series if macro varaibles for dates values in data set

gamotte — Wed, 26 Sep 2018 07:59:41 GMT

@ChrisNZ code works fine although it creates macrovariables m1 to m3.

Il you want the macrovariables index to start from 0, just move the call symput

above the N+1 instruction.

Re: Create series if macro varaibles for dates values in data set

ChrisNZ — Thu, 27 Sep 2018 02:20:46 GMT

@RW9

1. N is never initialised, therefore is always missing.

Not true. The increment operator works like the sum() function

2.It is also never retained anyways, so would not increment.

Not true. The increment operator takes care of this too.

@Ronein

3.First date value will be in macro varaible called: m1

second date value will be in macro varaible called: m2

As you can see &m0 &m1 &m2 ......were not created

Not true. The program provided works as per the requirements.

%let start_date=01sep2017;
%let end_date  =01sep2018;
data WANT_MONTH;
  do DATE="&start_date"d to "&end_date"d;
    DATE_CHAR=put(DATE,yymmn4.);
    output;
    N+1;
    call symput (catt('M',N),DATE_CHAR);
    DATE=intnx('month', DATE, 0, 'e');
  end;
  format DATE yymmn4.;
run;
%put &=m1 &=m3;

M1=1709 M3=1711