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
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;
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?
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.
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;
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
Ok. So what date should be present in the first 10 obs of your desired data? Please be specific.
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;
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!
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
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;
Sure, I have just reached my office. I will respond with an easy solution perhaps after breakfast. Thanks!
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
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.
Ready to level-up your skills? Choose your own adventure.