DATA Step, Macro, Functions and more

How do I create datasets based on date using macros

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 7
Accepted Solution

How do I create datasets based on date using macros

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?


Accepted Solutions
Solution
‎02-08-2018 06:30 AM
Super Contributor
Posts: 340

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

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;

View solution in original post


All Replies
Valued Guide
Posts: 568

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

[ Edited ]

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.

Occasional Contributor
Posts: 7

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

Posted in reply to andreas_lds

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;

Occasional Contributor
Posts: 7

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

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;

Super Contributor
Posts: 340

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

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.

Occasional Contributor
Posts: 7

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

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

Super User
Super User
Posts: 9,599

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

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.

Occasional Contributor
Posts: 7

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

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, 

Solution
‎02-08-2018 06:30 AM
Super Contributor
Posts: 340

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

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;
Occasional Contributor
Posts: 7

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

Thank you for your reply.

snapdate is in monyy5. format.

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 164 views
  • 0 likes
  • 4 in conversation