DATA Step, Macro, Functions and more

How do I determine monthly to quarterly

Accepted Solution Solved
Reply
Contributor
Posts: 22
Accepted Solution

How do I determine monthly to quarterly

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.


Accepted Solutions
Solution
‎09-13-2017 04:58 AM
Super Contributor
Posts: 498

Re: How do I determine monthly to quarterly

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;

 

 

 

View solution in original post


All Replies
Super Contributor
Posts: 271

Re: How do I determine monthly to quarterly

[ Edited ]

 

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;

 

Frequent Contributor
Posts: 113

Re: How do I determine monthly to quarterly

%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);
Solution
‎09-13-2017 04:58 AM
Super Contributor
Posts: 498

Re: How do I determine monthly to quarterly

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;

 

 

 

Contributor
Posts: 22

Re: How do I determine monthly to quarterly

Posted in reply to andreas_lds

TQ for the helppp!!

☑ This topic is solved.

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

Discussion stats
  • 4 replies
  • 141 views
  • 0 likes
  • 4 in conversation