transaction by quarter

[Aexor]

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

[ballardw]

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.

[Aexor]

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

[ballardw]

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'.

[Aexor]

My bad. Yes these are sas dates

[Reeza]

where qtr(trans_date) = 3;

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

[novinosrin]

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;

[Aexor]

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

[Reeza]

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.

[novinosrin]

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

[asuman]

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;

[sbxkoenk]

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