How do I determine monthly to quarterly

[Hhh111]

Hi,

I have monthly datasets as per below:

Case_072017

Case_082017

Case_092017

Case_102017

Case_112017

Case_122017

How do I convert monthly datasets name to determine that dataset falls into which quarter?

Is it scan function? Or I can use macro?

My expectation is I want to gather monthly datasets and group it under each quarter. This program will be running the next month of each quarter. For example for 4th quarter, the program will be executed on 4th of January.

[RahulG]

I have saved all my dataset in one library and then using sashelp.vstable I am able to find dataset name. 

Using dataset name, I try to find out the month and year.

Finaly I create a macro variable qtr_dsn that would store all the dataset name for quarter=3 and year =2017.

libname month "/folders/myfolders";
data month.Case_072017;
dt='01Jul2017'd;
run;

data month.Case_082017;
dt='01Aug2017'd;
run;

data month.Case_092017;
dt='01Sep2017'd;
run;


proc sql;
create table dataset_nm
as select memname
from sashelp.vstable
where libname='MONTH';
quit;

DATA TEMP;
SET dataset_nm;
month=input(SUBSTR(memname,6,2),8.);
year=input(SUBSTR(memname,8,4),8.);
if month in (1,2,3) then qtr =1;
if month in (4,5,6) then qtr =2;
if month in (7,8,9) then qtr =3;
if month in (10,11,12) then qtr =4;
RUN;

proc sql; 
select memname into :qtr_dsn separated by " "
from temp
where qtr=3 and year =2017;
quit;
%put &qtr_dsn;

data dsn_qtr3;
set &qtr_dsn;
run;

[ShiroAmada]

%macro case_months;
%do mo=1 %to 12;
%if %eval(&mo<10) %then %do;
data case_0&mo.2017;
case_mo=&mo.;
case_yr=2017;
run;
%end;
%if %eval(&mo>=10) %then %do;
data case_&mo.2017;
case_mo=&mo.;
case_yr=2017;
run;
%end;
%end;
%mend;
%case_months;


proc sql noprint;
create table case_table_list as
select memname,mdy(input(substr(scan(memname,2,"_"),1,2),8.),1,input(substr(scan(memname,2,"_"),3,4),8.) ) as date format=date9.,
qtr(calculated date) as qtr
from dictionary.tables
where libname='WORK' and MEMNAME like 'CASE_%';
quit;


%macro Combine_By_Qtr(qtr);
proc sql noprint;
select memname into: memname separated by ' ' from case_table_list where qtr=&qtr. ;
quit;
%if %eval(&sqlobs>0) %then %do;
data qtr_&qtr;
set &memname.;
run;
%end;
%mend;
%Combine_By_Qtr(1);
%Combine_By_Qtr(2);
%Combine_By_Qtr(3);
%Combine_By_Qtr(4);

[andreas_lds · Accepted solution]

Please explain the expected result. Do you want to append the monthly datasets creating a new dataset? Or, do you want to rename the monthly datasets?

The following code creates one dataset per quarter:

proc sql noprint;
   create view work.datasets as
      select catx('.', LibName, MemName) as Dataset, 
            input(cats('01', substr(MemName, 6)), ddmmyy8.) as quarter format=yyq.
         from sashelp.vtable
            where libname = 'WORK' and MemName like 'CASE%2017'
         order by quarter;
   ;
quit;

data work.NewNames;
   set work.Datasets;
   by quarter;

   length DatasetList $ 200;

   if first.Quarter then DatasetList = '';

   DatasetList = catx(' ', DatasetList, Dataset);

   if last.Quarter then do;
      call execute(catx(' ', 'data', cats('case_', vvalue(Quarter)), '; set', DatasetList, '; run;'));
   end;
run;

[Hhh111]

TQ for the helppp!!