BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
anandrc
Obsidian | Level 7

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?

1 ACCEPTED SOLUTION

Accepted Solutions
gamotte
Rhodochrosite | Level 12

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

9 REPLIES 9
andreas_lds
Jade | Level 19

@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.

anandrc
Obsidian | Level 7

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;

anandrc
Obsidian | Level 7

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;

gamotte
Rhodochrosite | Level 12

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.

anandrc
Obsidian | Level 7

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

anandrc
Obsidian | Level 7

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, 

gamotte
Rhodochrosite | Level 12

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;
anandrc
Obsidian | Level 7

Thank you for your reply.

snapdate is in monyy5. format.

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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