topic Re: Dynamically concatinating datasets based on a date in SAS Programming

Dynamically concatinating datasets based on a date

SM3 — Sat, 23 Jul 2016 17:47:09 GMT

Hi,

I have a report date which is end of every qtr like 31Dec2015 and Using a variable created from report date ,I have 9 datasets like data_2015Q4, data_2015Q3.....data_2013Q3.Finally I want to append these 9 datasets to create a dataset like inp_2013q3_2015q4.but the issue is this should be done dynamically.For ex, in next qtr i,e 31mar2016, I need to append datasets going back 9 quarters i,e from 2014q1 to 2016q1. How do I do this?

Re: Dynamically concatinating datasets based on a date

Reeza — Sun, 24 Jul 2016 01:28:54 GMT

I would change your process so that it appends the dataset to the 'master' dataset at the end of the process.

Alternatives are to store all the output with a consistent naming structure in a library.

If you use a prefix that isn't used with any other datsets you can refer to it with a colon to append all datasets with the same prefix.

Ie this would append all datasets starting with IMP.

data want;
set imp: ;
run;

Re: Dynamically concatinating datasets based on a date

Ksharp — Sun, 24 Jul 2016 02:40:13 GMT






data have;
input date date9.;
cards;
31Dec2015 
31mar2016
;
run;
data temp;
 set have;
 length list $ 400;
 do i=-1 to -9 by -1;
  list=catx(' ',list,'data_'||put(intnx('qtr',date,i),yyq.));
 end;
 data=catx('_','inp',put(intnx('qtr',date,-9),yyq.),put(date,yyq.));
 drop i;
run;

data _null_;
 set temp;
 call execute(catx(' ','data',data,';set',list,';run;'));
run;

Re: Dynamically concatinating datasets based on a date

SM3 — Sun, 24 Jul 2016 03:05:23 GMT

Thank u so much Ksharp & Reeza for ur reply. But the report date is an user input every quarter like % let enddate="31dec2015".Using these date, sas has to go 9 qtr back dynamically and append datasets in a library with a fixed naming structure like data_2015Q1,data_2014q4....Tge library might have more than 9 qtrs of data but it sud append till 9 qtrs back starting from enddate.
Appreciate ur help.

Re: Dynamically concatinating datasets based on a date

Ksharp — Sun, 24 Jul 2016 03:27:23 GMT

Still the same thing :




%let date=31dec2015;


data temp;
 length list $ 400;
 do i=-1 to -9 by -1;
  list=catx(' ',list,'data_'||put(intnx('qtr',"&date"d,i),yyq.));
 end;
 data=catx('_','inp',put(intnx('qtr',"&date"d,-9),yyq.),put("&date"d,yyq.));
 drop i;
run;

data _null_;
 set temp;
 call execute(catx(' ','data',data,';set',list,';run;'));
run;

Re: Dynamically concatinating datasets based on a date

Ksharp — Sun, 24 Jul 2016 03:31:03 GMT

Still the same thing :


%let date=31dec2015;
data temp;
 length list $ 400;
 do i=-1 to -9 by -1;
  list=catx(' ',list,'data_'||put(intnx('qtr',"&date"d,i),yyq.));
 end;
 data=catx('_','inp',put(intnx('qtr',"&date"d,-9),yyq.),put("&date"d,yyq.));
 drop i;
run;

data _null_;
 set temp;
 call execute(catx(' ','data',data,';set',list,';run;'));
run;

Re: Dynamically concatinating datasets based on a date

PGStats — Sun, 24 Jul 2016 03:56:04 GMT

Get the dataset names from dictionary.tables and filter according to your requirements:

data data_2015Q4; x=1; run;
data data_2015Q3; x=2; run;
data data_2015Q2; x=3; run;
data data_2015Q1; x=4; run;
data data_2014Q4; x=5; run;
data data_2014Q3; x=6; run;
data data_2014Q2; x=7; run;
data data_2014Q1; x=8; run;
data data_2013Q4; x=9; run;
data data_2013Q3; x=10; run;
data data_2013Q2; x=11; run;
data data_2013Q1; x=12; run;

%let rptDate=31Dec2015;

proc sql noprint;
create table rptList as
select memname
from dictionary.tables
where libname="WORK" and
    memname like "DATA_____Q_" and
    intnx("QTR",input(scan(memname,2,"_"), ? yyq6.),0,"END") between
    intnx("QTR", "&rptDate"d, -8, "END") and
    "&rptDate"d;
select memname 
into:setList separated by " "
from rptList order by memname desc;
select catx("_", "inp", min(scan(memname,2,"_")), max(scan(memname,2,"_")))
into:setName trimmed
from rptList; 
drop table rptList, &setName;
quit;

data &setName;
set &setList;
run;

proc print; run;

Re: Dynamically concatinating datasets based on a date

SM3 — Sun, 24 Jul 2016 12:19:47 GMT

This is awesome. Thanks for ur help😊