Read one month previous data

Reply
Contributor
Posts: 41

Read one month previous data

Hi,

I need to read one month previous / last month  data from one folder having historical data. like In February I need to read January data. my data sets having below format. I need to automate the code. pls suggest me if any code available. 

1) XYZ__yyyymmdd

2) XYZ_yyyymm

Super User
Posts: 5,434

Re: Read one month previous data

Posted in reply to chirumalla

By format, I assume you mean data set name naming standard.

For 2), just create a macro variable that corresponds to the YYYYMM part, by using the INTNX function.

For 1), are there a data set for each day in the previous month? How does your code look like that consume these data sets?

In this scenario, my guess that you need some kind of macro that loops over the input data set names, based on the dates in the previous month.

Either way, it could be useful to have logic that lets you input the month of choice, if a situation occurs when you need to reload data older than the previous month.

Data never sleeps
Contributor
Posts: 41

Re: Read one month previous data

I receive data on monthly.  Every month my data set name is same but extension month and year(if old year) is change in some data sets having date extension. I want to take only previous month data set based on month and year. if any code pls suggest me.

Super User
Posts: 5,434

Re: Read one month previous data

Posted in reply to chirumalla

As I said, INTNX for month data sets. Then you probably need some kind of macro %do loop for iterating over the data sets that is per day.

Data never sleeps
Super Contributor
Posts: 644

Re: Read one month previous data

@chirumalla

You did not specify whether the input tables were in SAS format or external files that need to be imported into SAS.

If the tables are already SAS datasets you may in the same library then you can get a lis of all SAS datasets in the library using SQL, something like this (untested)

     Proc SQL ;

          Create table monthly_tables as

               Select     MEMNAME

                    ,     Scan (MEMNAME, -1, '_') As Date_Suffix

               From Dictionary.Members

               Where Library = 'HAVE'     /*     Library name must be in caps */

               Having missing (compress (Date_Suffix, ' ', 'D') )          /*     suffix must be all numerals      */

               ;

     Quit ;   

              

     %Let LastMonth     =     %Sysfunc (INTNX (Month, %Sysfunc (Today ()), -1)) ;

     Data Wanted_Tables ;

          Set     Monthly_Tables ;

          Retain     Last_Month      &LastMonth ;

          If      Length (Date_Suffix) =     8      then

               do ;

                    If INTNX ('MONTH', Input (Date_Suffix, YYMMDDN8.), 0)     =     last_Month

                         then Output ;

               end ;

          Else     If     Length (Date_Suffix) =     6      then

               do ;

                    If Input (Date_Suffix, YYMMN6.)     =     last_Month

                         then Output ;

               end ;

          Else Delete ;

     Run ;


That should give you a list of files to use.  You do not specify what the next step of your process should be but it could be put into a macro and then invoked using Call Execute () in a data _Null_ step.


If your data are in external files this method could be adapted but you would need to use a method to read the directory / directories containing the files, and use Scan (filename, -2, '._') in the first step (note the additional period).


Richard


Respected Advisor
Posts: 4,173

Re: Read one month previous data

Posted in reply to chirumalla

As Linus said: Populate a macro variable which you then can use in the dynamic part of the name.

data _null_;
  _val=intnx('month',today(),-1,'b');
  call symputx('yymmdd_begin_prev_month',put(_val,yymmddn8.));

  call symputx('yymm_prev_month',put(_val,yymmn6.));
run;

%put yymmdd_begin_prev_month= &yymmdd_begin_prev_month;
%put yymm_prev_month=         &yymm_prev_month;

/**
data want;
  set have_&yymmdd_begin_prev_month;
run;

date want;
  infile "c:\test\have_&yymm_prev_month..txt";
  ....
run;
**/

Super User
Super User
Posts: 7,070

Re: Read one month previous data

Posted in reply to chirumalla

%put XYZ_%sysfunc(intnx(month,%sysfunc(today()),-1),yymmddn8.);

%put XYZ_%sysfunc(intnx(month,%sysfunc(today()),-1),yymmn6.);

To combine a bunch of datasets with similar names try using the : modifier in the SET statement. Here is an example.

* Make some datasets for previous and current month for testing ;

data xyz_201401 xyz_20140101 xyz_20140105 xyz_20140201 ;

  x=1;

run;

* Get basename for previous month in XYZ_yyyymm format ;

%let base=xyz_%sysfunc(intnx(month,%sysfunc(today()),-1),yymmn6.);

* Combine all datasets for previous month ;

data want ;

  length ds dsname $41 ;

  set &base: indsname=ds ;

  dsname = ds;

  put _n_ ds= ;

run;

Ask a Question
Discussion stats
  • 6 replies
  • 1178 views
  • 0 likes
  • 5 in conversation