BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Rohit_R
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
mkeintz
PROC Star

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.

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------

View solution in original post

6 REPLIES 6
novinosrin
Tourmaline | Level 20

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;
Rohit_R
Obsidian | Level 7

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

Thanks!

Reeza
Super User

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;
Rohit_R
Obsidian | Level 7
Thanks for your reply, but unfortunately I dont have SAS/ETS. Please can you suggest an alternative?

Thanks!
mkeintz
PROC Star

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.

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
Rohit_R
Obsidian | Level 7
P.S. Two great solutions with SAS/ETS but unfortunately I dont have SAS/ETS. Please can anyone suggest a datastep version?

Thanks!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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