topic Re: How do I create datasets based on date using macros in SAS Programming

How do I create datasets based on date using macros

anandrc — Wed, 07 Feb 2018 14:12:27 GMT

I have a dataset as below

Data test;

infile cards;

Input snapdate Account Amount;

cards;

Dec-16 A 10

Jan-17 A 20

Feb-17 A 30

Mar-17 A 40

Apr-17 A 50

May-17 A 60

Jun-17 A 70

Jul-17 A 80

Aug-17 A 90

Sep-17 A 100

Oct-17 A 110

Nov-17 A 120

Dec-17 A 130

;

run;

If i am running for example Dec17, I need rolling previous 12 months datasets (from Dec17 back to Jan17) created based on snapdate using macros

I can code manually as below. But i am looking to implement this using macros and do loop

data test_201712 test_201711 . . . . . test_201701;

set test;

if date = '31Dec2017'd then output test_201712;

if date = '30Nov2017'd then output test_201711;

if date = '31Jan2017'd then output test_201701;

run;

Any suggestions?

Re: How do I create datasets based on date using macros

andreas_lds — Wed, 07 Feb 2018 14:19:17 GMT

@anandrc wrote:

I have a dataset as below

Data test;

infile cards;

Input snapdate Account Amount;

cards;

Dec-16 A 10

Jan-17 A 20

Feb-17 A 30

Mar-17 A 40

Apr-17 A 50

May-17 A 60

Jun-17 A 70

Jul-17 A 80

Aug-17 A 90

Sep-17 A 100

Oct-17 A 110

Nov-17 A 120

Dec-17 A 130

;

run;

If i am running for example Dec17, I need rolling previous 12 months datasets (from Dec17 back to Jan17) created based on snapdate using macros

I can code manually as below. But i am looking to implement this using macros and do loop

data test_201712 test_201711 . . . . . test_201701;

set test;

if date = '31Dec2017'd then output test_201712;

if date = '30Nov2017'd then output test_201711;

.

.

.

if date = '31Jan2017'd then output test_201701;

run;

Any suggestions?

There is no variable named "date" in dataset test.

Please explain the next step, because the posted data-step seems to be useless.

Re: How do I create datasets based on date using macros

anandrc — Wed, 07 Feb 2018 14:24:20 GMT

sorry, that date should have read snapdate

data test_201712 test_201711 . . . . . test_201701;

set test;

if snapdate = '31Dec2017'd then output test_201712;

if snapdate = '30Nov2017'd then output test_201711;

if snapdate = '31Jan2017'd then output test_201701;

run;

Re: How do I create datasets based on date using macros

anandrc — Wed, 07 Feb 2018 14:29:41 GMT

data test_201712 test_201711 test_201710 test_201709 test_201708 test_201707 test_201706

test_201705 test_201704 test_201703 test_201702 test_201701;

set test;

if snapdate = '31Dec2017'd then output test_201712;

if snapdate = '30Nov2017'd then output test_201711;

if snapdate = '31Oct2017'd then output test_201710;

if snapdate = '30Sep2017'd then output test_201709;

if snapdate = '31Aug2017'd then output test_201708;

if snapdate = '31Jul2017'd then output test_201707;

if snapdate = '30Jun2017'd then output test_201706;

if snapdate = '31May2017'd then output test_201705;

if snapdate = '30Apr2017'd then output test_201704;

if snapdate = '31Mar2017'd then output test_201703;

if snapdate = '28Feb2017'd then output test_201702;

if snapdate = '31Jan2017'd then output test_201701;

run;

Re: How do I create datasets based on date using macros

gamotte — Wed, 07 Feb 2018 14:30:57 GMT

Hello,

Why do you want to create a separate (single-row) dataset for each month ?

There is probably a better way to achieve your goal.

Re: How do I create datasets based on date using macros

anandrc — Wed, 07 Feb 2018 14:39:27 GMT

Hello,

That was just for illustration purpose. My intended dataset has approx. 1 million rows and increasing for each month and is historical dating back to Jan2010. I only need rolling previous 12 months

I need to separate the observations based on snapdate and create previous 12 months rolling datasets

Thanks

Re: How do I create datasets based on date using macros

RW9 — Wed, 07 Feb 2018 15:03:04 GMT

Then filter out data you don't want:

data want;
  set have (where=(snapdate >= intnx('year',today,1)));
run;

Then work with the one dataset. It is never a good idea to split data out into different datasets, all you will do is create far more (probably macro) code to try to deal with it, and in the process create more read/writes and processor use than one datastep.

Re: How do I create datasets based on date using macros

anandrc — Wed, 07 Feb 2018 15:45:23 GMT

Thanks for the reply. I do agree.

But in my instance, currently, legacy report code does look for previous 12 months worth datasets in legacy world.

I have new warehouse dataset with historical information. I need to repoint the legacy code to pick up new warehouse information. For that I need to create 12 historical monthly dataset and feed through the old report code replacing legacy datasets.

Thanks,

Re: How do I create datasets based on date using macros

gamotte — Wed, 07 Feb 2018 16:18:09 GMT

Hello,

It is not clear which format is your snapdate variable so you will probably have to adapt a little to suit your needs.

data test;
infile cards;
informat snapdate monyy.;
Input snapdate Account $ Amount;
cards;
Dec16 A 10
Jan17 A 20
Feb17 A 30
Mar17 A 40
Apr17 A 50
May17 A 60
Jun17 A 70
Jul17 A 80
Aug17 A 90
Sep17 A 100
Oct17 A 110
Nov17 A 120
Dec17 A 130
;

run;

%macro split;

    data _NULL_;
        length lst_data $200.;
        
        lst_data='';

        do i=0 to 11;
            dt=intnx('month','01Dec17'd,-i);
            lst_data=cat(strip(lst_data),' ','test_',year(dt),put(month(dt),z2.));
        end;

        call symputx("lst_data",lst_data);
    run;
            
    data &lst_data.;
        set test;

        %do i=1 %to %sysfunc(countw(&lst_data.));
            if snapdate=intnx('month','01Dec17'd,-&i.+1) then output %scan(&lst_data.,&i.,%str( ));
        %end;    
    run;

%mend;

%split;

proc print data=test_201701;
format snapdate monyy.;
run;

Re: How do I create datasets based on date using macros

anandrc — Wed, 07 Feb 2018 16:51:09 GMT

Thank you for your reply.

snapdate is in monyy5. format.