topic Re: transaction by quater in SAS Programming

transaction by quarter

Aexor — Tue, 11 May 2021 18:44:06 GMT

I have below table

table have

id trans_date

1 23-11-2021

2 20-10-2021

3 15-08-2021

4 29-04-2021

8 10-03-2021

9 15-05-2021

10 12-03-2021

11 03-06 -2021

2 15-08-2021

3 15-08-2021

4 16-01-2021

I want all ids of transactions done in a particular quarter.

e.g for third quarter ids are 8,10,4

Re: transaction by quater

ballardw — Tue, 11 May 2021 14:30:20 GMT

Which "particular quarter"? You need to specify what quarter you mean. If the "quarter" does not correspond to a calendar quarter you also need to specify which dates make up your quarter.

Is the trans_date variable actually a SAS date value? That would mean the variable is 1) numeric and 2) with a format assigned like ddmmyy10. or similar.

Re: transaction by quater

Aexor — Tue, 11 May 2021 14:35:15 GMT

here we need ids of transaction in below quater category
1st quarter(Jan-mar), 2nd quarter(apr-jun) , 3rd quarter(jul-sept),
4th quarter(oct-dec) .

All the transactions made in between jan to mar will come under quarter 1

Re: transaction by quater

ballardw — Tue, 11 May 2021 14:40:51 GMT

So, what does the output look like?

And still have not answered the question whether the variable is actually as SAS date.

There are lots of functions and formats that work with SAS dates but do not work with random text values like '21-10-2020'.

Re: transaction by quater

Reeza — Tue, 11 May 2021 14:43:37 GMT

where qtr(trans_date) = 3;

Assumes you have a SAS date, which means a numeric variable with a date format.

Re: transaction by quater

Aexor — Tue, 11 May 2021 14:44:35 GMT

My bad. Yes these are sas dates

Re: transaction by quater

novinosrin — Tue, 11 May 2021 15:56:24 GMT

Hi @Aexor Please use the below as template. The Proc summary groups by the formatted value which is rather more elegant imho albeit consumes ton of memory to sort and dump in the classfication group in memory addresses very similar to a Hash table. More details I would leave it to sages @ballardw / @Reeza if they will as I am lazy


data have;
 input id  trans_date :ddmmyy10.;
 format trans_date ddmmyy10.;
 tx_amount=rand('uniform')*1000;
 cards;
1    23-11-2021

2    20-10-2021

3    15-08-2021

4    29-04-2021

8    10-03-2021

9    15-05-2021

10  12-03-2021

11   03-06-2021

2    15-08-2021

3    15-08-2021

4     16-01-2021
;

proc sql;
 create table want as
 select intnx('qtr',trans_date,0,'e') as year_qtr format=yyq6.,
  sum(tx_amount) as sum
  from have
  group by year_qtr;
quit;
/*OR*/
proc summary data=have nway;
 class trans_date;
 var tx_amount;
 format trans_date yyq6.;
 output out=want(drop=_:) sum=;
run;

Re: transaction by quater

asuman — Tue, 11 May 2021 15:39:55 GMT

data test;
input id trans_date;
informat trans_date ddmmyy10.;
format trans_date ddmmyy10.;
cards;
1 23-11-2021
2 20-10-2021
3 15-08-2021
4 29-04-2021
8 10-03-2021
9 15-05-2021
10 12-03-2021
11 03-06-2021
2 15-08-2021
3 15-08-2021
4 16-01-2021
;
run;
data test;
set test;
quater=qtr(trans_date);
run;

Re: transaction by quater

sbxkoenk — Tue, 11 May 2021 16:01:51 GMT

data table_have;
input id trans_date;
informat trans_date ddmmyy10.;
  format trans_date ddmmyy10.;
cards;
1    23-11-2021
2    20-10-2021
3    15-08-2021
4    29-04-2021
8    10-03-2021
9    15-05-2021
10   12-03-2021
11   03-06-2021
2    15-08-2021
3    15-08-2021
4    16-01-2021
;
run;

data table_want;
 set table_have;
 Quarter1=QTR(trans_date);
 Quarter2=put(trans_date,YYQZ6.);
run;
/* end of program */

Koen

Re: transaction by quater

Aexor — Mon, 17 May 2021 18:13:56 GMT

Can you please explain this ? and the need of tx_amount=rand('uniform')*1000;

Re: transaction by quater

Reeza — Mon, 17 May 2021 18:55:51 GMT

It's just a method to create sample data to solve your problem. It's randomly generating the tx_amount value between 0 and 1000. There's a newer function, Rand('integer, 1, 1000) which will give similar answers but just integers, no decimals.

Re: transaction by quater

novinosrin — Mon, 17 May 2021 18:58:32 GMT

Oh I was just making a mock sample data in which I wanted to have a variable called tx_amount with random numbers. 🙂