how to add rows in sas

[annypanny]

suppose i have a dataset

Sc_no         perf         date

A000855A  -0.28305 30112018
A000855A  0.49306 30092018
A000855A  2.14955 31032019
A000855A  2.53133 31072018
A000855A  4.19303 31012019
A000856A  -0.28305 30112018
A000856A  0.49306 30092018
A000856A  2.14955 31032019
A000856A  2.53133 31072018
A000856A  4.19303 31012019

My task is I want to create another dataset and having same field from the above dataset i.e.,

Sc_no  perf   Date

but there should be 61 rows for every distinct Sc_no. Example: for A000855A Sc_no there should be 61 rows. Similarly if there is 2 distinct sc_no then there will be 61*2=122 rows will be there. And The date values should dynamic  i.e., there will be 61 rows of one distinct sc_no and the date should be dynamically change from 30th april 2020 i.e., the last date of this month to 61 month back i.e., 31st march 2015. How can I do and that should NOT be hard coded.

Edited

[PeterClemmensen]

So for A000855A there are 5 obs right now. What should the values of perf and date be in the 56 obs to be added?

[annypanny]

No, from the above dataset there is only two distinct sc_no A000855A and A000856A  so for this 2 distinct sc_no there will be 61*2 rows will be there and in the perf. there will be perf=0 and date should be the last date of the month of that given sc_no. are you able to understand a bit now or I have to give more explanation sir.

[PeterClemmensen]

See if this gives you what you want

data have;
input Sc_no $ perf date :ddmmyy10.;
format date ddmmyy10.;
datalines;
A000855A  -0.28305 30112018
A000855A  0.49306 30092018
A000855A  2.14955 31032019
A000855A  2.53133 31072018
A000855A  4.19303 31012019
A000856A  -0.28305 30112018
A000856A  0.49306 30092018
A000856A  2.14955 31032019
A000856A  2.53133 31072018
A000856A  4.19303 31012019
;

data new (drop=_:);
    do until (last.Sc_no);
        set have;
        by Sc_no;
        if date > _date then _date=date;
    end;

    perf = 0;
    date = _date;

    do _N_ = 1 to 61;
        output;
    end;
run;

[annypanny]

the above code is running well but the only problem is that the date is similar in all of the rows, if you see in the given dataset there is different dates for same sc_no. How can I get that dates. every date is last date of the month i.e., 30 or 31st. but different month

[PeterClemmensen]

Ok. So what date should be present in the first 10 obs of your desired data? Please be specific.

[annypanny]

if you see in the above dataset sc_no is same but dates are different so in first 10 obs the date which are above in the dataset runs as same as the above dataset

[novinosrin]

HI @annypanny  Good morning. Upon reading the thread so far, is the following is perhaps what you are likely after?  All i did was modified @PeterClemmensen 's code a tiny bit

data have;
input Sc_no $ perf date :ddmmyy10.;
format date ddmmyy10.;
datalines;
A000855A  -0.28305 30112018
A000855A  0.49306 30092018
A000855A  2.14955 31032019
A000855A  2.53133 31072018
A000855A  4.19303 31012019
A000856A  -0.28305 30112018
A000856A  0.49306 30092018
A000856A  2.14955 31032019
A000856A  2.53133 31072018
A000856A  4.19303 31012019
;

data new (drop=_:);
 do _n_= 1 by 1 until (last.Sc_no);
  set have;
  by Sc_no;
  if date > _date then _date=date;
  output;
 end;
 perf = 0;
 date = _date;
 do _n_=_n_+1 to 61;
  output;
 end;
run;

proc print noobs;run;

[annypanny]

I want dynamic date i.e., there will be 61 rows of one distinct sc_no and the date should be dynamically change from 30th april 2020 i.e., the last date of this month to 61 month back i.e., 31st march 2015. what logic can help in this? thanks in advance

[novinosrin]

Alright, I see that explanation seems to start making sense of the logic. One more request plz. Can you paste your expected output for one distinct value i.e. A000855A to make sure what's on our mind aligns with your expectations. Thank you!

[annypanny]

sc_no        pref  Date

A000855A 0      31032015

A000855A 0      30042015

A000855A 0      31052015

A000855A 0      30062015

A000855A 0      31072015

.

.

A000855A 0      31032020

A000855A 0      30042020

Thanks in Advance

[novinosrin · Accepted solution]

Hi @annypanny  Alright, I would have liked you to provide the full listing of one distinct Sc_no expected result to save each other's time. Never mind. Looking at your expected result, the following is what I comprehend. 

You have a set of sc_no that has various unique month end dates and its associated perf_no. You want to backtrack from the current month for a period of 61 months or in other words 61 month end dates. Some month end dates within those 61 month end dates are likely to exist in the input dataset with a valid non zero  perf number associated with it. And for those non existing dates in the input, you want the perf value to assigned as zero.

If the above description is correct, the following should do:-

data have;
input Sc_no $ perf date :ddmmyy10.;
format date ddmmyy10.;
datalines;
A000855A  -0.28305 30112018
A000855A  0.49306 30092018
A000855A  2.14955 31032019
A000855A  2.53133 31072018
A000855A  4.19303 31012019
A000856A  -0.28305 30112018
A000856A  0.49306 30092018
A000856A  2.14955 31032019
A000856A  2.53133 31072018
A000856A  4.19303 31012019
;
/*Get the current month date and start date backtracking 61 months*/
%let  curr_dt=%sysfunc(intnx(mon,%sysfunc(today()),0,e));
%let  start_dt=%sysfunc(intnx(mon,&curr_dt,-61,e));
%put &=curr_dt;
%put &=start_dt;
/*Load the available dates from the input in a look up hash table*/
/*Loop through the 62 months and assign the failed look up with existing as zero*/
data new ;
 if _n_=1 then do;
  dcl hash H () ;
  h.definekey  ("date") ;
  h.definedata ("perf") ;
  h.definedone () ;
 end;
 do until (last.Sc_no);
  set have;
  by Sc_no;
  h.add();
 end;
 do _n_=0 by 1 until(date=&curr_dt);
  date=intnx('mon',&start_dt,_n_,'e');
  if h.find() ne 0 then perf=0;
  output;
 end;
 h.clear();
run;
proc print noobs;run;

[annypanny]

thank you very much sir

[annypanny]

can we do it with a simple do loop logic as the code is not so simple and as a basic learnr the code seems to be tough to undesrtand

[novinosrin]

Sure, I have just reached my office. I will respond with an easy solution perhaps after breakfast. Thanks!