topic Re: Arrays and Do loops in SAS Programming

Arrays and Do loops

blue34 — Sat, 30 Sep 2017 20:32:05 GMT

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

Re: Arrays and Do loops

Reeza — Sat, 30 Sep 2017 22:41:20 GMT

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

Re: Arrays and Do loops

mkeintz — Sat, 30 Sep 2017 22:51:39 GMT

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?
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?

Re: Arrays and Do loops

error_prone — Sat, 30 Sep 2017 22:52:54 GMT

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.

Re: Arrays and Do loops

Reeza — Sat, 30 Sep 2017 22:58:27 GMT

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

Re: Arrays and Do loops

blue34 — Sun, 01 Oct 2017 00:40:16 GMT

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

Re: Arrays and Do loops

Patrick — Sun, 01 Oct 2017 01:05:52 GMT

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

Re: Arrays and Do loops

blue34 — Sun, 01 Oct 2017 01:15:23 GMT

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

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;

Re: Arrays and Do loops

Patrick — Sun, 01 Oct 2017 01:38:21 GMT

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

Re: Arrays and Do loops

blue34 — Sun, 01 Oct 2017 01:45:44 GMT

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

Re: Arrays and Do loops

Patrick — Sun, 01 Oct 2017 01:52:01 GMT

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

Re: Arrays and Do loops

blue34 — Sun, 01 Oct 2017 02:00:28 GMT

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 ?

Re: Arrays and Do loops

Patrick — Sun, 01 Oct 2017 02:05:29 GMT

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

Re: Arrays and Do loops

blue34 — Sun, 01 Oct 2017 02:14:30 GMT

Thank you very much again!!

Re: Arrays and Do loops

Patrick — Sun, 01 Oct 2017 02:23:18 GMT

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