BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
blue34
Calcite | Level 5

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

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

View solution in original post

14 REPLIES 14
Reeza
Super User

@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
PROC Star
  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?
--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
error_prone
Barite | Level 11
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
Super User

@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
Calcite | Level 5

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
Opal | Level 21

@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
Calcite | Level 5

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
Opal | Level 21

@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
Calcite | Level 5

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
Opal | Level 21

@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
Calcite | Level 5
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
Opal | Level 21

@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
Calcite | Level 5

Thank you very much again!!

Patrick
Opal | Level 21

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

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 14 replies
  • 1528 views
  • 0 likes
  • 5 in conversation