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

hi all,

 

i need to find the following:

 

if today is first day of a month, then i need to append all the previous sas datasets to a monthly one

for ex: my files are like AB_20170201 . . . . . . . .20170229

anf todays date is march 1st of 2017 then all the files from 02/01 to 02/29 shoud get appended as ab_201702 and all the daily data sets should get deleted

                                          

1 ACCEPTED SOLUTION

Accepted Solutions
DaveShea
Lapis Lazuli | Level 10

Hello Arunasaran,

 

Finding the first day of the month of any date is very easy with the SAS IntNx (Interval Next) function. Given any date and an interval, in your case the month interval, IntNX can return the first, last and and whole range of dates.

 

The SAS code below is a straightforward example of calculating the 1st of the month for a given date:

Data _NULL_;
 ThisDate=Date();
 FirstDayOfMonth=IntNX("Month", ThisDate, 0);
 Put ThisDate=E8601DA. FirstDayOfMonth=E8601DA.;

Run;

The "Month" represents the sort of interval you are interested in.

ThisDate is the date you are starting with, in this example it is today's date, given by call to another function the Date function.

The third argument of 0 (zero) tells IntNx how many interval bounderies (ie month-ends to jump over from your starting date. As we have elected zero bounderies, the date returned is the start of the month of ThisDate. If we had used a value of 1 IntNx would jump from ThisDate and leave us immediately at the start of the next interval, ie the first of next month.

 

I have attached a macro that, I think does what you need. This macro is looking for daily datasets called SHOES_<yyyymmdd>.

 

The macro will determine if the current date is the 1st of the month and if it is, it will append all of the daily SHOES_<yyyymmdd> datasets into a single ARCHIVE_<yyyymm> dataset and then delete the daily datasets it appended.

 

Please ensure that you use this code carefully with test data. Read it through and make sure that it does what you think it does.

 

This first bit of code can be used to safely build a collection of play-around SAS datasets that you can afford to lose or break.

Options MPRINT LineSize=145;
********************************************************************************************************************************; *Compile a macro to create some daily plar-around datasets that we can afford to lose or break...... ; ********************************************************************************************************************************; %Macro CreateDailyDatasets(Start=, Finish=); Data _NULL_; StartDate=Input("&Start", B8601DA.); FinishDate=Input("&Finish", B8601DA.); Call Symput("StartDate", StartDate); Call Symput("FinishDate", FinishDate); Number_Of_Days=FinishDate-StartDate; Do i=StartDate To FinishDate; Loop_Number+1; Call Symput(Compress("DSN_Suffix"||Loop_Number), Compress(Put(i, yymmddn8.))); End; Call Symput("Loop_Number", Loop_Number); Run; **************************************************************; *Create the daily play-around datasets. ; **************************************************************; %Do i=1 %To &Loop_Number; Data SHOES_&&DSN_Suffix&i; Set SASHELP.SHOES (Obs=5); Run; %End; %Mend CreateDailyDatasets; ********************************************************************************************************************************; *Create some play-around datasets from 01Dec2016 to 05Apr2017. ; ********************************************************************************************************************************; %CreateDailyDatasets(Start=20161201, Finish=20170405);

Finally, once you have your play-around datasets, use this macro to conditionally append into a monthly archive and delete the contributing daily datasets.  This is a bit long-winded but my preference is to make things clear.

 

Options MPRINT LineSize=145;

********************************************************************************************************************************;
*Compile a macro to manage our daily SHOES_<yyyymmdd> datasets and archive them into a monthly dataset once the month is over.  ;
********************************************************************************************************************************;
%Macro ArchiveDailyDatasets;

Data _NULL_;
 **************************************************************;
 *What is todays date ?                                        ;
 **************************************************************;
 ThisDate=Date();
/* ThisDate="01Feb2017"d;*/

 **************************************************************;
 *Is ThisDate the 1st day of a month ?                         ;
 *If so we have work to do......                               ;
 **************************************************************;
 If ThisDate=IntNx("Month", ThisDate, 0, "B") Then
    Do;
        **************************************************************;
        *Calculate the Start/End dates of the month just finished.    ;
        **************************************************************;
        LastMonthStart=IntNX("Month", ThisDate, -1, "B");
        LastMonthEnd=IntNX("Month", ThisDate, -1, "E");
        NumberOfDays=LastMonthEnd-LastMonthStart+1; 

        ***************************************************************;
        *Create a variable to use as our month-end dataset prefix      ;
        ***************************************************************;
        MonthDSNSuffix=Put(LastMonthStart, yymmn6.);

        **************************************************************;
        *Create a family of macro variables where each member will be ;
        *the _<yyyymmdd> value on the suffix of our daily datasets.   ;                                                                 ;
        **************************************************************;

        Do i=1 To NumberOfDays;
            Call Symput(Compress("DailyDSNSuffix"||i), Compress(Put(LastMonthStart+i-1, yymmddn8.)));
        End;

        **************************************************************;
        *Create some macro variables to use in later steps.           ;
        **************************************************************;
        Call Symput("FirstDayOfMonth", FirstDayOfMonth);
        Call Symput("LastDayOfMonth", LastDayOfMonth);
        Call Symput("NumberOfDays", Compress(NumberOfDays));
        Call Symput("MonthDSNSuffix", Compress(MonthDSNSuffix));
        Call Symput("NumberOfDays", Compress(NumberOfDays));

        Call Symput("Work_To_Do", "YES");

    End;
    **************************************************************;
    *If this is not the 1st of a month, set a macro variable to   ; 
    *allow us to skip all the rest of the code in this macro.     ;
    **************************************************************;
    Else
    Do;

        Call Symput("Work_To_Do", "NO");

    End;

    **************************************************************;
    *Create a macro variable holding todays date, whatever it is. ;
    **************************************************************;
    Call Symput("ThisDate", ThisDate);

Run;

*******************************************************************;
*If we have archiving to do.....let us get to work........         ;
*******************************************************************;
%If &Work_To_Do=YES %Then
    %Do;

        %Put =======================================================================================================================;
        %Put As Today, %QSYSFUNC(PutN(&ThisDate, E8601DA.)), is the 1st of the month, we have some archiving work to do.......      ;
        %Put =======================================================================================================================;

        Data Archive_&MonthDSNSuffix;
         Set 
            %Do i=1 %To &NumberOfDays;
                SHOES_&&DailyDSNSuffix&i
            %End;
                ;

        Run;

        %Put ================================================================================;
        %Put Now delete the datasets that we have just appended into a single monthly dataset;
        %Put ================================================================================;
        Proc SQL;
            %Do i=1 %To &NumberOfDays;
                DROP Table SHOES_&&DailyDSNSuffix&i;
            %End;
        Quit;

    %End;
    %Else
    %Do;
        %Put =======================================================================================================================;
        %Put As Today, %QSYSFUNC(PutN(&ThisDate, E8601DA.)), is not the 1st of the month, we do not have any archiving work to do.  ;
        %Put =======================================================================================================================;

    %End;

%Mend ArchiveDailyDatasets;

********************************************************************************************************************************;
*Now actually call the macro and see what needs to be done....                                                                  ;
********************************************************************************************************************************;
%ArchiveDailyDatasets;

 

I hope that this helps more than it hinders.

 

Cheers,

 

Downunder Dave

 

View solution in original post

2 REPLIES 2
Yavuz
Quartz | Level 8
I didnt try but maybe give idea. "set AB:;" line is critical. Union all datasets which name begin "AB_"

data AB_all_data;
tday=substr(put(today(),date9.),1,2);
If tday="01" then do;
Set AB_:;
end;
run;

%let month=substr(put(today(),Ddmmyy10.),4,2);
%let year=substr(put(today(),Ddmmyy10.),7,4);

Data _&month.&year;
Set AB_all_data;
Run;

proc sql;
drop table AB_:;
quit;
DaveShea
Lapis Lazuli | Level 10

Hello Arunasaran,

 

Finding the first day of the month of any date is very easy with the SAS IntNx (Interval Next) function. Given any date and an interval, in your case the month interval, IntNX can return the first, last and and whole range of dates.

 

The SAS code below is a straightforward example of calculating the 1st of the month for a given date:

Data _NULL_;
 ThisDate=Date();
 FirstDayOfMonth=IntNX("Month", ThisDate, 0);
 Put ThisDate=E8601DA. FirstDayOfMonth=E8601DA.;

Run;

The "Month" represents the sort of interval you are interested in.

ThisDate is the date you are starting with, in this example it is today's date, given by call to another function the Date function.

The third argument of 0 (zero) tells IntNx how many interval bounderies (ie month-ends to jump over from your starting date. As we have elected zero bounderies, the date returned is the start of the month of ThisDate. If we had used a value of 1 IntNx would jump from ThisDate and leave us immediately at the start of the next interval, ie the first of next month.

 

I have attached a macro that, I think does what you need. This macro is looking for daily datasets called SHOES_<yyyymmdd>.

 

The macro will determine if the current date is the 1st of the month and if it is, it will append all of the daily SHOES_<yyyymmdd> datasets into a single ARCHIVE_<yyyymm> dataset and then delete the daily datasets it appended.

 

Please ensure that you use this code carefully with test data. Read it through and make sure that it does what you think it does.

 

This first bit of code can be used to safely build a collection of play-around SAS datasets that you can afford to lose or break.

Options MPRINT LineSize=145;
********************************************************************************************************************************; *Compile a macro to create some daily plar-around datasets that we can afford to lose or break...... ; ********************************************************************************************************************************; %Macro CreateDailyDatasets(Start=, Finish=); Data _NULL_; StartDate=Input("&Start", B8601DA.); FinishDate=Input("&Finish", B8601DA.); Call Symput("StartDate", StartDate); Call Symput("FinishDate", FinishDate); Number_Of_Days=FinishDate-StartDate; Do i=StartDate To FinishDate; Loop_Number+1; Call Symput(Compress("DSN_Suffix"||Loop_Number), Compress(Put(i, yymmddn8.))); End; Call Symput("Loop_Number", Loop_Number); Run; **************************************************************; *Create the daily play-around datasets. ; **************************************************************; %Do i=1 %To &Loop_Number; Data SHOES_&&DSN_Suffix&i; Set SASHELP.SHOES (Obs=5); Run; %End; %Mend CreateDailyDatasets; ********************************************************************************************************************************; *Create some play-around datasets from 01Dec2016 to 05Apr2017. ; ********************************************************************************************************************************; %CreateDailyDatasets(Start=20161201, Finish=20170405);

Finally, once you have your play-around datasets, use this macro to conditionally append into a monthly archive and delete the contributing daily datasets.  This is a bit long-winded but my preference is to make things clear.

 

Options MPRINT LineSize=145;

********************************************************************************************************************************;
*Compile a macro to manage our daily SHOES_<yyyymmdd> datasets and archive them into a monthly dataset once the month is over.  ;
********************************************************************************************************************************;
%Macro ArchiveDailyDatasets;

Data _NULL_;
 **************************************************************;
 *What is todays date ?                                        ;
 **************************************************************;
 ThisDate=Date();
/* ThisDate="01Feb2017"d;*/

 **************************************************************;
 *Is ThisDate the 1st day of a month ?                         ;
 *If so we have work to do......                               ;
 **************************************************************;
 If ThisDate=IntNx("Month", ThisDate, 0, "B") Then
    Do;
        **************************************************************;
        *Calculate the Start/End dates of the month just finished.    ;
        **************************************************************;
        LastMonthStart=IntNX("Month", ThisDate, -1, "B");
        LastMonthEnd=IntNX("Month", ThisDate, -1, "E");
        NumberOfDays=LastMonthEnd-LastMonthStart+1; 

        ***************************************************************;
        *Create a variable to use as our month-end dataset prefix      ;
        ***************************************************************;
        MonthDSNSuffix=Put(LastMonthStart, yymmn6.);

        **************************************************************;
        *Create a family of macro variables where each member will be ;
        *the _<yyyymmdd> value on the suffix of our daily datasets.   ;                                                                 ;
        **************************************************************;

        Do i=1 To NumberOfDays;
            Call Symput(Compress("DailyDSNSuffix"||i), Compress(Put(LastMonthStart+i-1, yymmddn8.)));
        End;

        **************************************************************;
        *Create some macro variables to use in later steps.           ;
        **************************************************************;
        Call Symput("FirstDayOfMonth", FirstDayOfMonth);
        Call Symput("LastDayOfMonth", LastDayOfMonth);
        Call Symput("NumberOfDays", Compress(NumberOfDays));
        Call Symput("MonthDSNSuffix", Compress(MonthDSNSuffix));
        Call Symput("NumberOfDays", Compress(NumberOfDays));

        Call Symput("Work_To_Do", "YES");

    End;
    **************************************************************;
    *If this is not the 1st of a month, set a macro variable to   ; 
    *allow us to skip all the rest of the code in this macro.     ;
    **************************************************************;
    Else
    Do;

        Call Symput("Work_To_Do", "NO");

    End;

    **************************************************************;
    *Create a macro variable holding todays date, whatever it is. ;
    **************************************************************;
    Call Symput("ThisDate", ThisDate);

Run;

*******************************************************************;
*If we have archiving to do.....let us get to work........         ;
*******************************************************************;
%If &Work_To_Do=YES %Then
    %Do;

        %Put =======================================================================================================================;
        %Put As Today, %QSYSFUNC(PutN(&ThisDate, E8601DA.)), is the 1st of the month, we have some archiving work to do.......      ;
        %Put =======================================================================================================================;

        Data Archive_&MonthDSNSuffix;
         Set 
            %Do i=1 %To &NumberOfDays;
                SHOES_&&DailyDSNSuffix&i
            %End;
                ;

        Run;

        %Put ================================================================================;
        %Put Now delete the datasets that we have just appended into a single monthly dataset;
        %Put ================================================================================;
        Proc SQL;
            %Do i=1 %To &NumberOfDays;
                DROP Table SHOES_&&DailyDSNSuffix&i;
            %End;
        Quit;

    %End;
    %Else
    %Do;
        %Put =======================================================================================================================;
        %Put As Today, %QSYSFUNC(PutN(&ThisDate, E8601DA.)), is not the 1st of the month, we do not have any archiving work to do.  ;
        %Put =======================================================================================================================;

    %End;

%Mend ArchiveDailyDatasets;

********************************************************************************************************************************;
*Now actually call the macro and see what needs to be done....                                                                  ;
********************************************************************************************************************************;
%ArchiveDailyDatasets;

 

I hope that this helps more than it hinders.

 

Cheers,

 

Downunder Dave

 

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
  • 2 replies
  • 21552 views
  • 0 likes
  • 3 in conversation