- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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'.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Assumes you have a SAS date, which means a numeric variable with a date format.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Oh I was just making a mock sample data in which I wanted to have a variable called tx_amount with random numbers. 🙂
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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