Arrays and Do loops

[blue34]

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";

[Reeza]

---

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

[mkeintz]

1. Do you have multiple companies/entities/subject, with some sort of id variable?  If the companies/entities do not start at the same date, do you still assign A0 to the first record for each company/entity?  
2. Calendar year quarters?  Or fiscal year quarters?  Or (if you just assign A0 to the first record of each entity), it's implicit in the dates being read in?

[error_prone]

Please post input dataset and a dataset containing the desired output. I don't think that loops or arrays are useful to solve the problem.

Idea:
Create a dataset with distinct values of performance_dt, use that dataset to create a format. In your data step it is nothing more than
quarter = put(performance_dt, QuarterFormat.);
To set quarter.

Maybe, instead of selecting distinct values as source for the format, creating a dataset with all possible values is easier - and a way to get a loop in the solution 😉

Can't post code now, no SAS installation on my mobile phone.

[Reeza]

---

@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 😉

[blue34]

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

[Patrick]

@blue34

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.

[blue34]

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;

[Patrick · Accepted solution]

@blue34

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]

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

[Patrick]

@blue34

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>

[blue34]

I just did, Sounds great! Thank you! But how about instead of putting prefix Q, I want to create fields as Q1, Q2, Q3 to Q20 ?

[Patrick]

@blue34

 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;

[blue34]

Thank you very much again!!

[Patrick]

@blue34

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;