Hi,
I would like to create a table using below code, but dates and quarter might differ based on portfolio. That is why I want to use do loop to make dynamice my code. Anyone can help me to give some idea?
Date will increase as quarterly, quarter will increase 0-n
if performance_dt = '31dec2016'd then quarter="A0";
if performance_dt = '31mar2017'd then quarter="A1";
if performance_dt = '30jun2017'd then quarter="A3";
if performance_dt = '30sep2017'd then quarter="A4";
if performance_dt = '31dec2017'd then quarter="A5";
What about something like below?
data have(drop=_:);
do account='ABC','XYZ';
format performance_dt date9.;
do _i=0 to 8;
performance_dt=intnx('quarter','31dec2016'd,_i,'e');
amount=_i*100;
output;
amount=_i*200;
output;
end;
end;
run;
/* if multiple rows per account and performance_dt possible: aggregated data */
proc sql;
create table inter as
select
account,
performance_dt,
sum(amount) as amount
from have
group by account, performance_dt
order by account, performance_dt
;
quit;
/* if a single row per account and performance_dt guaranteed: Just sort the data */
/*proc sort data=have out=inter;*/
/* by account performance_dt;*/
/*run;*/
proc transpose data=inter out=want(drop=_:) prefix=Q_;
by account;
id performance_dt;
format performance_dt yymon7.;
run;
@blue34 wrote:
Hi,
I would like to create a table using below code, but dates and quarter might differ based on portfolio. That is why I want to use do loop to make dynamice my code. Anyone can help me to give some idea?
Probably, but you need to explain the logic. One good option may be to format the date using a QTR type format which would show up as 2017Q1, 2017Q2, ... 2018Q1 etc.
@error_prone wrote:
Idea:
Can't post code now, no SAS installation on my mobile phone.
@error_prone Why I have a SAS Academics on Demand account 😉
My input dataset:
This can be 20 or 13 fiscal quarters, that is why I want this part id dynamic.
Let`s say I have 5 quarters for each account number now in input;
Performance_date Account
Q1_2016 1
Q2_2016 1
Q3_2016 1
Q4_2016 1
Q1_2017 1
Q1_2016 2
Q2_2016 2
Q3_2016 2
Q4_2016 2
Q1_2017 2
Desired output;
Performance_date Account ID
Q1_2016 1 A1
Q2_2016 1 A2
Q3_2016 1 A3
Q4_2016 1 A4
Q1_2017 1 A5
Q1_2016 2 A1
Q2_2016 2 A2
Q3_2016 2 A3
Q4_2016 2 A4
Q1_2017 2 A5
Why can't you use your Performance_Date as ID?
If you calculate the group id based on performance date then as I understand it the values of this ID (i.e. A1) will depend on the currently used data (i.e. the lowest performance date in your data) and you can't ensure that A1 always stands for the same date unless you implement and maintain some permanent look-up table.
After create this ID I will transpose this data, that is why I am trying to create that ID field.
My final dataset after transpose will be based on account number:
acccount A1 A2 A3 A4 A5
1
2
This is my full code and want to automatize this:
data data
set data;
length quarter $ 11;
if performance_dt = '31dec2016'd then quarter="A0";
if performance_dt = '31mar2017'd then quarter="A1";
if performance_dt = '30jun2017'd then quarter="A2";
if performance_dt = '30sep2017'd then quarter="A3";
if performance_dt = '31dec2017'd then quarter="A4";
if performance_dt = '31mar2018'd then quarter="A5";
if performance_dt = '30jun2018'd then quarter="A6";
if performance_dt = '30sep2018'd then quarter="A7";
keep account quarter amount;
run;
proc sort data=data; by account ;run;
proc transpose data=data out=data;
by account;
id quarter;
run;
What about something like below?
data have(drop=_:);
do account='ABC','XYZ';
format performance_dt date9.;
do _i=0 to 8;
performance_dt=intnx('quarter','31dec2016'd,_i,'e');
amount=_i*100;
output;
amount=_i*200;
output;
end;
end;
run;
/* if multiple rows per account and performance_dt possible: aggregated data */
proc sql;
create table inter as
select
account,
performance_dt,
sum(amount) as amount
from have
group by account, performance_dt
order by account, performance_dt
;
quit;
/* if a single row per account and performance_dt guaranteed: Just sort the data */
/*proc sort data=have out=inter;*/
/* by account performance_dt;*/
/*run;*/
proc transpose data=inter out=want(drop=_:) prefix=Q_;
by account;
id performance_dt;
format performance_dt yymon7.;
run;
I don`t think this will work, because I want amount into 20 fields after I transpose. Am i missing something
Amount will be A1-A5 for each performance_date each account
acccount A1 A2 A3 A4 A5
1
2
Yes, I believe you're missing something. Have you executed the code I've posted?
In your initial post there are values A1 to A7 - a new A<n> value per quarter. The code I've posted does the same except that the values are not called A<n> but Q_<yyyymon>
I want to create fields as Q1, Q2, Q3 to Q20
You can of course do whatever you like but as already explained for A1 to A<n>: It results in code which is less dynamic. Using the actual dates in the names is imho the better solution as this is going to work for any data and the same column name stands always for exactly the same quarter no matter which source data you're using.
You could use code as below:
proc format;
value groups
'31mar2017'd ="A1"
'30jun2017'd ="A2"
'30sep2017'd ="A3"
'31dec2017'd ="A4"
'31mar2018'd ="A5"
'30jun2018'd ="A6"
'30sep2018'd ="A7"
'31dec2018'd ="A8"
;
run;
proc transpose data=inter out=want(drop=_:) let;
by account;
id performance_dt;
format performance_dt groups.;
run;
Thank you very much again!!
There is also a YYQ format which returns eventually a column name even more to your liking.
proc transpose data=inter out=want(drop=_:) prefix=Q_;
by account;
id performance_dt;
format performance_dt yyq6.;
run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: