topic Re: how to add rows in sas in SAS Programming

how to add rows in sas

annypanny — Wed, 29 Apr 2020 13:32:34 GMT

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

Re: how to add rows in sas

PeterClemmensen — Wed, 29 Apr 2020 11:14:32 GMT

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?

Re: how to add rows in sas

annypanny — Wed, 29 Apr 2020 11:20:24 GMT

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.

Re: how to add rows in sas

PeterClemmensen — Wed, 29 Apr 2020 11:33:12 GMT

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;

Re: how to add rows in sas

annypanny — Wed, 29 Apr 2020 11:41:41 GMT

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

Re: how to add rows in sas

PeterClemmensen — Wed, 29 Apr 2020 11:56:39 GMT

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

Re: how to add rows in sas

annypanny — Wed, 29 Apr 2020 12:17:41 GMT

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

Re: how to add rows in sas

novinosrin — Wed, 29 Apr 2020 12:43:01 GMT

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;

Re: how to add rows in sas

annypanny — Wed, 29 Apr 2020 13:26:49 GMT

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

Re: how to add rows in sas

novinosrin — Wed, 29 Apr 2020 13:38:39 GMT

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!

Re: how to add rows in sas

annypanny — Wed, 29 Apr 2020 13:43:49 GMT

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

Re: how to add rows in sas

novinosrin — Wed, 29 Apr 2020 14:41:28 GMT

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;

Re: how to add rows in sas

annypanny — Wed, 29 Apr 2020 14:59:39 GMT

thank you very much sir

Re: how to add rows in sas

annypanny — Thu, 30 Apr 2020 10:14:13 GMT

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

Re: how to add rows in sas

novinosrin — Thu, 30 Apr 2020 11:39:08 GMT

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

Re: how to add rows in sas

novinosrin — Thu, 30 Apr 2020 12:11:58 GMT

HI @annypanny Here is a very simple solution that should be easy to follow for most. I have also written comments where necessary to help you understand the flow of the logic. I hope this is convenient enough.


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;

/*Create the backtracked monthend date list*/
data backtracked_dates;
 date=&start_dt;
 do while(date<=&curr_dt);
  output;
  date=intnx('mon',date,1,'e');
 end;
 format date ddmmyy10.;
run;
/*Combine the dates with distinct Sc_no*/
/*This will give you a complete date list associated with each scn_no */
proc sql;
create table combine_scno_dates as
select *
from 
(select distinct Sc_no from have), backtracked_dates
order by Sc_no,date;
quit;
 
/*Now, Join the combine_scno_dates back with have to get the needed result*/
proc sql;
create table want as
select a.*,case when perf=. then 0 else perf end as perf
from combine_scno_dates a left join have b
on a.Sc_no=b.Sc_no and a.date=b.date
order by a.sc_no,a.date;
quit;
/*Print and check the results*/
proc print noobs;run;