topic Re: Select quarterly data in SAS Programming

Select quarterly data

kajal_30 — Fri, 06 May 2022 17:04:20 GMT

Hi There I have a scenario where I need to create and run a program to load quarterly data. So I need to write a code to load historical quarterly data. For eg if I run today the code to should get me the data for last 7 quarters. So looking to create a macro with input current run date and accordingly it will get me last 7 quarter's dates.

data one;

format y date9.;

y = intx('qtr',&rep_date.,0,'e');

put y ;

run;

Thanks

kajal

Re: Select quarterly data

Reeza — Fri, 06 May 2022 17:11:03 GMT

data one;
*end of previous quarter;
report_end_date = intnx('qtr', today(), -1, 'e');
*start of 8 quarters ago (if you want 7 quarters);
report_start_end = intnx('qtr', today(), -8, 'b');

format report_: date9.;

call symputx('report_start_dt', report_start_date);
call symputx('report_end_dt', report_end_date);

run;

*will show as numbers but work fine as filters;
%put &report_start_dt.;
%put &report_end_dt.;

Something like above should get you started. You can modify the increments as needed or change the alignment parameters to get the start/end dates.

@kajal_30 wrote:

Hi There I have a scenario where I need to create and run a program to load quarterly data. So I need to write a code to load historical quarterly data. For eg if I run today the code to should get me the data for last 7 quarters. So looking to create a macro with input current run date and accordingly it will get me last 7 quarter's dates.

data one;

format y date9.;

y = intx('qtr',&rep_date.,0,'e');

put y ;

run;

Thanks

kajal

Re: Select quarterly data

kajal_30 — Fri, 06 May 2022 17:14:36 GMT

Thanks Reeza for your very quick reply but I forgot to add one more point here as quarters are fiscal quarters like 1st -qtr april, 2nd -qtr july, 3rd -qtr october, 4th -qtr january

Re: Select quarterly data

Reeza — Fri, 06 May 2022 17:17:30 GMT

Show sample data and expected output please. Fake data is fine.

Variable types (numeric/character) and format may be relevant as well.

Re: Select quarterly data

kajal_30 — Fri, 06 May 2022 17:30:43 GMT

Sorry accidently accepted as solution.

so I have like 100 quarterly files out of which user wants to select as per demand files. file names have quarter end dates like.

So here let say I am selecting files for last 7 quarters

customer_scn_bb_bdk_20220430.xls,

customer_scn_bb_bdk_20220131.xls

customer_scn_bb_bdk_20220730.xls

customer_scn_bb_bdk_20210131.xls

customer_scn_bb_bdk_20210430.xls

customer_scn_bb_bdk_20210731.xls

customer_scn_bb_bdk_20211031.xls

So if I can create a table of date values as per given start date I can choose files accordingly.

Kajal

Re: Select quarterly data

Reeza — Fri, 06 May 2022 17:52:40 GMT

data list_dates;
*end of previous quarter;

do i=1 to 7;
report_end_date = intnx('qtr', today(), -1*i, 'e');
output;
end;

format report_end_date yymmddn8.;
run;

Full import/combine code.

data list_dates;
*end of previous quarter;

do i=1 to 7;
report_end_date = intnx('qtr', today(), -1*i, 'e');
file_name = catt('customer_scn_bb_bdk', put(report_end_date, yymmddn8.), '.xlsx');

str = catt('proc import out=qtr', i, ' datafile="', file_name, ' dbms=xlsx replace; run;');

call execute(str);
end;

format report_end_date yymmddn8.;
run;

data last7qtrs;
set qtr1-qtr7;
run;

Re: Select quarterly data

kajal_30 — Fri, 06 May 2022 18:06:35 GMT

Thanks again but in the output if you see dates are coming as march, dec, september, june but we are expecting it to be like jan, apr, July, October.

Re: Select quarterly data

Kurt_Bremser — Fri, 06 May 2022 21:12:56 GMT

Just loop:

do i = -6 to 0;
  y = intx('qtr',&rep_date.,i,'e');
  /* whatever you intend to do */
end;

Re: Select quarterly data

Reeza — Mon, 09 May 2022 15:48:13 GMT

@kajal_30 wrote:

Thanks again but in the output if you see dates are coming as march, dec, september, june but we are expecting it to be like jan, apr, July, October.

Try playing around with the INTNX parameters to get what you need. Your quarters are not standard quarters, it's using the end of the first month of a quarter it seems. Typical quarters are Jan 1 - March 31.

The code structure is correct, you need to modify the parameters within INTNX to get what you need.

Here's the documentation for reference. If you cannot figure it out, post what you've tried and we can help from there.

https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.2/lefunctionsref/p10v3sa3i4kfxfn1sovhi5xzxh8n.htm