topic Create daily status of a dataset from a delta reporting dataset in SAS Programming

Create daily status of a dataset from a delta reporting dataset

Rohit_R — Thu, 22 Mar 2018 19:17:53 GMT

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

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

novinosrin — Thu, 22 Mar 2018 19:27:55 GMT

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;

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

Reeza — Thu, 22 Mar 2018 19:39:29 GMT

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_syntax08.htm&locale=en

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;

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

mkeintz — Thu, 22 Mar 2018 20:57:04 GMT

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.

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

Rohit_R — Thu, 22 Mar 2018 21:00:11 GMT

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

Thanks!

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

Rohit_R — Thu, 22 Mar 2018 21:01:23 GMT

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

Thanks!

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

Rohit_R — Thu, 22 Mar 2018 21:01:42 GMT

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

Thanks!