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.
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;
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;
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;
TQ for the helppp!!
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: