Create daily status of a dataset from a delta reporting dataset

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 12
Accepted Solution

Create daily status of a dataset from a delta reporting dataset

Hi,

 

I have a dataset that records only the changes to a variable on the change date. For ex.

 

ID       Date       Value

1       19/3/18       2

1       21/3/18       3

1       22/3/18       5

2       19/3/18       1

2       23/3/18       2

 

The dataset I want is the daily status for the full week. If the Value has not changed, the record should stay the same as the previous day.

 

ID       Date       Value

1       19/3/18       2

1       20/3/18       2

1       21/3/18       3

1       22/3/18       5

1       23/3/18       5

1       24/3/18       5

1       25/3/18       5

2       19/3/18       1

2       20/3/18       1

2       21/3/18       1

2       22/3/18       1

2       23/3/18       2

2       24/3/18       2

2       25/3/18       2

 

 

I have two datasets currently, one dataset that has the values for all IDs as at 19/3/18 and other dataset that records all the delta entries. I have created another dataset that has a Date variable that goes from 19/3/18 to 25/3/18.

 

Any help will be greatly appreciated.

 

Many thanks,

Rohit


Accepted Solutions
Solution
‎03-22-2018 05:48 PM
Trusted Advisor
Posts: 1,345

Re: Create daily status of a dataset from a delta reporting dataset

If you don't have PROC TIMESERIES, there is compact DATA step solution that use a combination of a SET statement, and  "self-MERGE with offset" technique:

 

data have;
  input ID       Date  :ddmmyy9.     Value;
  format date date9.;
datalines;
1       19/3/18       2
1       21/3/18       3
1       22/3/18       5
2       19/3/18       1
2       23/3/18       2
run;

data want (drop=nxt_date);
  set have (keep=id);
  by id;
  merge have
        have (firstobs=2 keep=date rename=(date=nxt_date));
  if last.id then do date=date to '25mar2018'd;
    output;
  end;
  else do date=date to nxt_date-1;
    output;
  end;
run;

 

I have put in the specific date value '25mar2018'd as a do loop upper value.  But if you need to read in a date, and then find the subsequent SUNDAY, you can put   

    intnx('week.2',date,0,'E');

in place of '28mar2018'd.    The "week.2" interval refers to weeks beginning on Monday.   So this just add 0 weeks to DATE, and then aligns the result to the END of the 7-day span.

View solution in original post


All Replies
PROC Star
Posts: 1,844

Re: Create daily status of a dataset from a delta reporting dataset

Do you have SAS ETS module? 

I have it in my college lab, so here you go. If you want datastep, let the community know

 

data have;
input ID    $   date :ddmmyy8.       Value;
format date date9.;
datalines;
1       19/3/18       2
1       21/3/18       3
1       22/3/18       5
2       19/3/18       1
2       23/3/18       2
;


proc timeseries data=have out=want;
id date interval=day setmissing=previous;
var value;
by id;
run;
Occasional Contributor
Posts: 12

Re: Create daily status of a dataset from a delta reporting dataset

Posted in reply to novinosrin

Thanks for your reply, but unfortunately I dont have SAS/ETS. Please can you suggest an alternative?

Thanks!

Super User
Posts: 23,776

Re: Create daily status of a dataset from a delta reporting dataset

Are the dates always the same between the ID's? If so, this is a great opportunity to use PROC TIMESERIES.

The SETMISSING allows you to specify how to fill in the missing values and there are several methods available. The PREV corresponds to LOCF.

 

http://documentation.sas.com/?docsetId=etsug&docsetVersion=14.2&docsetTarget=etsug_timeseries_syntax...

 

You will need a SAS/ETS license to use this method

 

data have;
informat ID $1. date ddmmyy10. value 8.;
input ID       Date       Value;
cards;
1       19/3/18       2
1       21/3/18       3
1       22/3/18       5
2       19/3/18       1
2       23/3/18       2
;
run;

proc timeseries data=have out=want;
by id;
	id date interval=day start='19Mar2018'd end='25Mar2018'd setmissing=prev;
	var value;
run;
Occasional Contributor
Posts: 12

Re: Create daily status of a dataset from a delta reporting dataset

Thanks for your reply, but unfortunately I dont have SAS/ETS. Please can you suggest an alternative?

Thanks!
Solution
‎03-22-2018 05:48 PM
Trusted Advisor
Posts: 1,345

Re: Create daily status of a dataset from a delta reporting dataset

If you don't have PROC TIMESERIES, there is compact DATA step solution that use a combination of a SET statement, and  "self-MERGE with offset" technique:

 

data have;
  input ID       Date  :ddmmyy9.     Value;
  format date date9.;
datalines;
1       19/3/18       2
1       21/3/18       3
1       22/3/18       5
2       19/3/18       1
2       23/3/18       2
run;

data want (drop=nxt_date);
  set have (keep=id);
  by id;
  merge have
        have (firstobs=2 keep=date rename=(date=nxt_date));
  if last.id then do date=date to '25mar2018'd;
    output;
  end;
  else do date=date to nxt_date-1;
    output;
  end;
run;

 

I have put in the specific date value '25mar2018'd as a do loop upper value.  But if you need to read in a date, and then find the subsequent SUNDAY, you can put   

    intnx('week.2',date,0,'E');

in place of '28mar2018'd.    The "week.2" interval refers to weeks beginning on Monday.   So this just add 0 weeks to DATE, and then aligns the result to the END of the 7-day span.

Occasional Contributor
Posts: 12

Re: Create daily status of a dataset from a delta reporting dataset

P.S. Two great solutions with SAS/ETS but unfortunately I dont have SAS/ETS. Please can anyone suggest a datastep version?

Thanks!
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 121 views
  • 1 like
  • 4 in conversation