topic Re: Create a data set for each month between 2 dates in SAS Programming

Create a data set for each month between 2 dates

GS2 — Wed, 11 Nov 2020 21:20:48 GMT

Using SAS 9.4

Is there an efficient way to create SAS data sets for each month and year combination between 2 dates. For example 1 data set has dates between Jan 2017 and Dec 2020 and I would like to create a data set for Jan 2017, Feb 2017, Mar 2017 ... Dec 2020. Is there an efficient way to do this without having to individually create the data set? I understand the preference would be to keep all data together in one data set but the code is set up for individual data sets. Any ideas on the best way to go about this would be appreciated. Thank you!

Re: Create a data set for each month between 2 dates

ChrisNZ — Thu, 12 Nov 2020 04:18:14 GMT

> Is there an efficient way to create SAS data sets for each month and year combination between 2 dates.

I understand the preference would be to keep all data together in one data set

There we go, you answered your question. 🙂

Why can't your code reference the main data set with a where clause rather than reference a data set name?

Instead of loading DATASET_202011 you call MAINDATASET; if put(DATE,yymmn6.)='202011';

Re: Create a data set for each month between 2 dates

GS2 — Thu, 12 Nov 2020 13:27:48 GMT

In short, I am not using 1 data set because it has been previously set up as multiple data sets with 1000s of lines of code and I believe it will be more efficient to continue as the code currently is than to go back and re-write all of the code.

Re: Create a data set for each month between 2 dates

PaigeMiller — Thu, 12 Nov 2020 13:43:26 GMT

Create monthly data sets containing what? How should these monthly data sets be named? Yes, of course it is possible (although I would not call it "efficient") using macros or possibly CALL EXECUTE.

I'm sure that your very long code that works with these monthly data sets could be shortened quite a bit by having it working with a single data set containing all months; but if you don't feel its worth your time to change the programming, I understand.

Re: Create a data set for each month between 2 dates

Tom — Thu, 12 Nov 2020 15:08:25 GMT

Write the code for one (or possibly two) months. Then figure out how to generate that code for all of the dates.

Example data step to split out two months of data into individual datasets.

data want_2017_01 want_2017_02 ;
  set have;
  if '01JAN2017'd <= date < '01FEB2017'd then output want_2017_01;
  else if '01FEB2017'd <= date < '01MAR2017'd then output want_2017_02;
run;

You also might want to create views, especially if your real dataset has an index on DATE.

data want_2017_01 / view=want_2017_01;
  set have;
  where date between '01JAN2017'd and '31JAN207'd;
run;

Re: Create a data set for each month between 2 dates

ChrisNZ — Fri, 13 Nov 2020 04:18:30 GMT

Something like this might help:

%macro loop(start=, end=);
  %local i;
  %do i=&start. %to &end.;
    %if %substr(&i.,5,2)=13 %then %let i=%eval(&i+100-12);
    data DS_&i.; T=1; run;
  %end;
%mend;

%loop(start=201901, end=202010);