topic Re: Extend Dates to the existing SAS Dataset in SAS Programming

Extend Dates to the existing SAS Dataset

jhh197 — Thu, 12 Jan 2023 22:00:40 GMT

Hi All,

I have a dataset TEST1 which has Contype PMC and ERC . Another Dataset TEST2 which has distinct dates .

1. Check end date of each Contype for PMC its 30SEP2024:00:00:00 and if the date is less than end date of TEST2 which is 29DEC2028:00:00:00 then add all dates after 30SEP2024:00:00:00 from TEST2 Table to TEST1 Table and for CP,PP,MM will be 0 . Same for Contype CRC . But for Contype PCP then end date is 31dec2034 which is > end date of TEST2 which is 29DEC2028:00:00:00 so no need to do anything .

Can anyone please help . Thank you so much .

Output should be like below TEST3

ConType	TimeStamp	CP	PP	MM
PMC	31May2024:00:00:00	200	200	200
PMC	28JUN2024:00:00:00	235	244	33
PMC	31JUL2024:00:00:00	235	500	654
PMC	30SEP2024:00:00:00	238	234	222
PMC	31OCT2024:00:00:00	0	0	0
PMC	29NOV2024:00:00:00	0	0	0
PMC	28FEB2025:00:00:00	0	0	0
PMC	31MAR2027:00:00:00	0	0	0
PMC	31AUG2028:00:00:00	0	0	0
PMC	29DEC2028:00:00:00	0	0	0
ERC	31JAN2023:00:00:00	109	546	230
ERC	28FEB2023:00:00:00	234	320	777
ERC	31MAR2023:00:00:00	223	111	435
ERC	29SEP2023:00:00:00	187	325	120
ERC	31May2024:00:00:00	0	0	0
ERC	28JUN2024:00:00:00	0	0	0
ERC	31JUL2023:00:00:00	0	0	0
ERC	31JAN2024:00:00:00	0	0	0
ERC	31JUL2024:00:00:00	0	0	0
ERC	30SEP2024:00:00:00	0	0	0
ERC	31OCT2024:00:00:00	0	0	0
ERC	29NOV2024:00:00:00	0	0	0
ERC	28FEB2025:00:00:00	0	0	0
ERC	31MAR2027:00:00:00	0	0	0
ERC	31AUG2028:00:00:00	0	0	0
ERC	29DEC2028:00:00:00	0	0	0

For PMC end date is 30SEP2024:00:00:00 which is less than

TEST1

ConType	TimeStamp	CP	PP	MM
PMC	31May2024:00:00:00	200	200	200
PMC	28JUN2024:00:00:00	235	244	33
PMC	31JUL2024:00:00:00	235	500	654
PMC	30SEP2024:00:00:00	238	234	222
ERC	31JAN2023:00:00:00	109	546	230
ERC	28FEB2023:00:00:00	234	320	777
ERC	31MAR2023:00:00:00	223	111	435
ERC	29SEP2023:00:00:00	187	325	120
PCP	31JAN2023:00:00:00	200	200	200
PCP	28FEB2023:00:00:00	235	244	33
PCP	31MAR2023:00:00:00	235	500	654
PCP	29SEP2023:00:00:00	238	234	222
PCP	29DEC2028:00:00:00	109	546	230
PCP	31DEC2034:00::00	234	320	777

TEST2

Dates_Reference

30JULY2022:00:00:00

31JAN2023:00:00:00

28FEB2023:00:00:00

31MAR2023:00:00:00

29SEP2023:00:00:00

31May2024:00:00:00

28JUN2024:00:00:00

31JUL2023:00:00:00

31JAN2024:00:00:00

31JUL2024:00:00:00

30SEP2024:00:00:00

31OCT2024:00:00:00

29NOV2024:00:00:00

28FEB2025:00:00:00

31MAR2027:00:00:00

31AUG2028:00:00:00

29DEC2028:00:00:00

Re: Extend Dates to the existing SAS Dataset

andreas_lds — Fri, 13 Jan 2023 06:33:05 GMT

Please post the data in usable form.

Re: Extend Dates to the existing SAS Dataset

jhh197 — Fri, 13 Jan 2023 15:58:36 GMT

Hi ,

Below is the code for creating input data

data test1;
input ConType$1-3 TimeStamp CP PP MM;
informat TimeStamp date9.;
format TimeStamp date9.;
cards;
PMC 31May2024 200 200 200
PMC 28JUN2024 235 244 33
PMC 31JUL2024 235 500 654
PMC 30SEP2024 238 234 222
ERC 31JAN2023 109 546 230
ERC 28FEB2023 234 320 777
ERC 31MAR2023 223 111 435
ERC 29SEP2023 187 325 120
PCP 31JAN2023 200 200 200
PCP 28FEB2023 235 244 33
PCP 31MAR2023 235 500 654
PCP 29SEP2023 238 234 222
PCP 29DEC2028 109 546 230
PCP 31DEC2034 234 320 777
;
run;

And Test2

data test2;
input timestamp date9.;
informat TimeStamp date9.;
format TimeStamp date9.;
cards;
30JUL2022
31JAN2023
28FEB2023
31MAR2023
29SEP2023
31May2024
28JUN2024
31JUL2023
31JAN2024
31JUL2024
30SEP2024
31OCT2024
29NOV2024
28FEB2025
31MAR2027
31AUG2028
29DEC2028
;
run;

Re: Extend Dates to the existing SAS Dataset

jhh197 — Fri, 13 Jan 2023 16:40:12 GMT

Can anyone please help

Re: Extend Dates to the existing SAS Dataset

Tom — Fri, 13 Jan 2023 17:04:42 GMT

First problem. Your printout is showing DATETIME values and not DATE values.

Is your variable a DATE variable, like your words say, or a DATETIME variable, like your printouts say? That is does it contain counts of days or counts of seconds?

Main problem. It is not at all clear where you came up with the date of 29DEC2028 for PMC. Did you just KNOW that or did your CALCULATE that from some data? If you calculated it then what data did you use? And how did you calculate it.

Let's assume you have this source dataset, let's call it HAVE.

data have;
  ConType :$3. Date :date. CP PP MM ;
  format date date9.;
cards;
PMC 31May2024 200 200 200
PMC 28JUN2024 235 244 33
PMC 31JUL2024 235 500 654
PMC 30SEP2024 238 234 222
ERC 31JAN2023 109 546 230
ERC 28FEB2023 234 320 777
ERC 31MAR2023 223 111 435
ERC 29SEP2023 187 325 120
;

And you have some other dataset, let's call it DATES, that has variable named ENDDATE that is also a date value.

If the goal is to add extra observations to extend all of the CONTYPE groups to the same end date then do something like this to calculate the maximum enddate and use it to add new observations.

proc summary data=dates ;
  var enddate;
  output out=lastday max=enddate;
run;

data want;
  if _n_=1 then set lastday(keep=enddate);
  set have;
  by contype;
  output;
  if last.contype then do;
    cp=0; pp=0; mm=0;
    do date=date+1 to enddate;
      output;
    end;
  end;
run;

If instead you want to use a different enddate for each CONTYPE then calculate the max(enddate) for each CONTYPE and MERGE the two datasets instead.

proc summary nway data=dates ;
  class contype;
  var enddate;
  output out=lastday max=enddate;
run;

data want;
  merge have lastday(keep=contype enddate);
  by contype;
  output;
  if last.contype then do;
    cp=0; pp=0; mm=0;
    do date=date+1 to enddate;
      output;
    end;
  end;
run;

Re: Extend Dates to the existing SAS Dataset

jhh197 — Fri, 13 Jan 2023 17:10:56 GMT

Hi ,

First problem :Date values is ok to take .

Second I have another input dataset Test2 from there I have taken end date as 29 DEC2028

I need extend dates for each Contype to 2028 if the end date is less than 2028 and variables created should have values 0 .

Thank you

Re: Extend Dates to the existing SAS Dataset

jhh197 — Fri, 13 Jan 2023 17:44:11 GMT

hi ,
Invalid do loop control information , either the initial or TO expression is missing or the BY expression is missing, zero or invalid

Re: Extend Dates to the existing SAS Dataset

jhh197 — Fri, 13 Jan 2023 17:45:31 GMT

hi ,

Both queries are giving error not working .

Second query we dont have contype in Test2 table

Re: Extend Dates to the existing SAS Dataset

Tom — Fri, 13 Jan 2023 18:44:07 GMT

@jhh197 wrote:

hi ,

Both queries are giving error not working .

Second query we dont have contype in Test2 table

I did not post any queries. I showed how to use a PROCEDURE to calculate a maximum date. And how to use a data step to output extra observations.

Show the log from the steps that are causing you trouble.

Make sure the dataset names and variable names (and variable types) match what you have.

If your variables are DATETIME instead of DATE then you will need to use something different for the looping.

If your date values are missing then you will have trouble. What do you want to do if the date in the existing dataset is missing? What do you want to do if there is no maximum date to calculate?

Re: Extend Dates to the existing SAS Dataset

jhh197 — Fri, 13 Jan 2023 19:12:57 GMT

data have;
input contype$1-3 TimeStamp CP PP MM;
informat TimeStamp date9.;
format TimeStamp date9.;
cards;
PMC 31May2024 200 200 200
PMC 28JUN2024 235 244 33
PMC 31JUL2024 235 500 654
PMC 30SEP2024 238 234 222
ERC 31JAN2023 109 546 230
ERC 28FEB2023 234 320 777
ERC 31MAR2023 223 111 435
ERC 29SEP2023 187 325 120
PCP 31JAN2023 200 200 200
PCP 28FEB2023 235 244 33
PCP 31MAR2023 235 500 654
PCP 29SEP2023 238 234 222
PCP 29DEC2028 109 546 230
PCP 31DEC2034 234 320 777
;
run;

proc sort data=have;
by contype;
run;

data dates;
input enddate date9.;
informat enddate date9.;
format enddate date9.;
cards;
30JUL2022
31JAN2023
28FEB2023
31MAR2023
29SEP2023
31May2024
28JUN2024
31JUL2023
31JAN2024
31JUL2024
30SEP2024
31OCT2024
29NOV2024
28FEB2025
31MAR2027
31AUG2028
29DEC2028
;
run;

proc summary data=dates ;
var enddate;
output out=lastday max=enddate;
run;

data want;
if _n_=1 then set lastday(keep=enddate);
set have;
by contype;
output;
if last.contype then do;
cp=0; pp=0; mm=0;
do date=date+1 to enddate;
output;
end;
end;
run;

I ran this code but i get below error

ERROR: Invalid DO loop control information, either the INITIAL or TO expression is missing or the BY expression is missing, zero,
or invalid.
enddate=29DEC2028 contype=ERC TimeStamp=29SEP2023 CP=0 PP=0 MM=0 FIRST.contype=0 LAST.contype=1 date=. _ERROR_=1 _N_=4
NOTE: Missing values were generated as a result of performing an operation on missing values.
Each place is given by: (Number of times) at (Line):(Column).
1 at 96:17
NOTE: The SAS System stopped processing this step because of errors.
NOTE: There were 1 observations read from the data set WORK.LASTDAY.
NOTE: There were 5 observations read from the data set WORK.HAVE.
WARNING: The data set WORK.WANT may be incomplete. When this step was stopped there were 4 observations and 7 variables.
WARNING: Data set WORK.WANT was not replaced because this step was stopped.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

Output is coming looking like this

enddate	ConType	TimeStamp	CP	PP	MM	date
29-Dec-28	PMC	31-May-24	200	200	200	.
29-Dec-28	PMC	28-Jun-24	235	244	33	.
29-Dec-28	PMC	31-Jul-24	235	500	654	.

But i need output like this

For PMC the end date is 30SEP2024 and i need to extend the records until 2028 but the dates after 30sep 2024 i will take from Test2 take 31oct2024,29nov2024 ,28feb2025 all these dates are found in test2 and then cp,pp,mm will be zero for those extended dates

PMC	31OCT2024:00:00:00	0	0	0
PMC	29NOV2024:00:00:00	0	0	0
PMC	28FEB2025:00:00:00	0	0	0
PMC	31MAR2027:00:00:00	0	0	0
PMC	31AUG2028:00:00:00	0	0	0
PMC	29DEC2028:00:00:00	0	0	0

ConType	TimeStamp	CP	PP	MM
PMC	31May2024:00:00:00	200	200	200
PMC	28JUN2024:00:00:00	235	244	33
PMC	31JUL2024:00:00:00	235	500	654
PMC	30SEP2024:00:00:00	238	234	222
PMC	31OCT2024:00:00:00	0	0	0
PMC	29NOV2024:00:00:00	0	0	0
PMC	28FEB2025:00:00:00	0	0	0
PMC	31MAR2027:00:00:00	0	0	0
PMC	31AUG2028:00:00:00	0	0	0
PMC	29DEC2028:00:00:00	0	0	0
ERC	31JAN2023:00:00:00	109	546	230
ERC	28FEB2023:00:00:00	234	320	777
ERC	31MAR2023:00:00:00	223	111	435
ERC	29SEP2023:00:00:00	187	325	120
ERC	31May2024:00:00:00	0	0	0
ERC	28JUN2024:00:00:00	0	0	0
ERC	31JUL2023:00:00:00	0	0	0
ERC	31JAN2024:00:00:00	0	0	0
ERC	31JUL2024:00:00:00	0	0	0
ERC	30SEP2024:00:00:00	0	0	0
ERC	31OCT2024:00:00:00	0	0	0
ERC	29NOV2024:00:00:00	0	0	0
ERC	28FEB2025:00:00:00	0	0	0
ERC	31MAR2027:00:00:00	0	0	0
ERC	31AUG2028:00:00:00	0	0	0
ERC	29DEC2028:00:00:00	0	0	0
PCP	31JAN2023:00:00:00	200	200	200
PCP	28FEB2023:00:00:00	235	244	33
PCP	31MAR2023:00:00:00	235	500	654
PCP	29SEP2023:00:00:00	238	234	222
PCP	29DEC2028:00:00:00	109	546	230
PCP	31DEC2034:00::00	234	320	777

Re: Extend Dates to the existing SAS Dataset

Tom — Fri, 13 Jan 2023 19:27:58 GMT

Why did you use

do date= ...

If the variable you had was named TIMESTAMP?

Re: Extend Dates to the existing SAS Dataset

jhh197 — Fri, 13 Jan 2023 19:41:01 GMT

I took datepart because everything is 00:00:00

Thank you

Re: Extend Dates to the existing SAS Dataset

Tom — Fri, 13 Jan 2023 19:51:05 GMT

@jhh197 wrote:
I took datepart because everything is 00:00:00

Thank you

The DATEPART() function will convert a datetime value (number of seconds) into a date value (number of days).

But it cannot change the name of a variable, which is the problem that you posted code had.

You created a variable named TIMESTAMP that should have had date values because you used the DATE informat. But in the later code you did not use that variable. Instead you used some other variable named DATE.

In a SAS dataset if you use a variable that does not exist then SAS will happily make the variable for you. But if there is no source of values then its value is going to be missing. Hence the error message about invalid values for the range referenced in the DO loop.

Re: Extend Dates to the existing SAS Dataset

jhh197 — Fri, 13 Jan 2023 20:02:05 GMT

Hi ,

Thank you for responding

How do i get the output like above . You can ignore timestamp for now . Think the data has only date and no time

Thanks .

Re: Extend Dates to the existing SAS Dataset

Tom — Fri, 13 Jan 2023 20:27:54 GMT

Just make sure to sort the source data by CONTYPE and TIMESTAMP.

proc sort data=have;
  by contype timestamp;
run;

And change the DO loop to use your actual variable name:

do timestamp=timestamp+1 to enddate;

NOTE: If you do not want an observation for every day then you will need to change the DO loop to increment the date by a different interval. What interval is it that you want?

NOTE: If you do want every day then do you also want all of the days from 31JAN2023 to 28FEB2023?

Here are the first 30 observations.

You can probably add a DROP statement to get rid of ENDDATE.

 Obs      enddate    contype    TimeStamp     CP     PP     MM

   1    29DEC2028      ERC      31JAN2023    109    546    230
   2    29DEC2028      ERC      28FEB2023    234    320    777
   3    29DEC2028      ERC      31MAR2023    223    111    435
   4    29DEC2028      ERC      29SEP2023    187    325    120
   5    29DEC2028      ERC      30SEP2023      0      0      0
   6    29DEC2028      ERC      01OCT2023      0      0      0
   7    29DEC2028      ERC      02OCT2023      0      0      0
   8    29DEC2028      ERC      03OCT2023      0      0      0
   9    29DEC2028      ERC      04OCT2023      0      0      0
  10    29DEC2028      ERC      05OCT2023      0      0      0
  11    29DEC2028      ERC      06OCT2023      0      0      0
  12    29DEC2028      ERC      07OCT2023      0      0      0
  13    29DEC2028      ERC      08OCT2023      0      0      0
  14    29DEC2028      ERC      09OCT2023      0      0      0
  15    29DEC2028      ERC      10OCT2023      0      0      0
  16    29DEC2028      ERC      11OCT2023      0      0      0
  17    29DEC2028      ERC      12OCT2023      0      0      0
  18    29DEC2028      ERC      13OCT2023      0      0      0
  19    29DEC2028      ERC      14OCT2023      0      0      0
  20    29DEC2028      ERC      15OCT2023      0      0      0
  21    29DEC2028      ERC      16OCT2023      0      0      0
  22    29DEC2028      ERC      17OCT2023      0      0      0
  23    29DEC2028      ERC      18OCT2023      0      0      0
  24    29DEC2028      ERC      19OCT2023      0      0      0
  25    29DEC2028      ERC      20OCT2023      0      0      0
  26    29DEC2028      ERC      21OCT2023      0      0      0
  27    29DEC2028      ERC      22OCT2023      0      0      0
  28    29DEC2028      ERC      23OCT2023      0      0      0
  29    29DEC2028      ERC      24OCT2023      0      0      0
  30    29DEC2028      ERC      25OCT2023      0      0      0

Re: Extend Dates to the existing SAS Dataset

Tom — Fri, 13 Jan 2023 20:45:55 GMT

If you just want to make sure that all of the dates appear for all on the CONTYPE values then it is much easier.

Just use a simple SQL query to make a skeleton structure that has all CONTYPE and all dates and then merge the two datasets.

proc sql;
create table skeleton as 
  select contype,timestamp,0 as CP,0 as PP,0 as MM
  from (select distinct contype from have)
     , (select distinct enddate as timestamp from dates)
  order by contype,timestamp
;
quit;

data want;
  merge skeleton have;
  by contype timestamp;
run;

Result

Obs    contype    timestamp     CP     PP     MM

  1      ERC      30JUL2022      0      0      0
  2      ERC      31JAN2023    109    546    230
  3      ERC      28FEB2023    234    320    777
  4      ERC      31MAR2023    223    111    435
  5      ERC      31JUL2023      0      0      0
  6      ERC      29SEP2023    187    325    120
  7      ERC      31JAN2024      0      0      0
  8      ERC      31MAY2024      0      0      0
  9      ERC      28JUN2024      0      0      0
 10      ERC      31JUL2024      0      0      0
 11      ERC      30SEP2024      0      0      0
 12      ERC      31OCT2024      0      0      0
 13      ERC      29NOV2024      0      0      0
 14      ERC      28FEB2025      0      0      0
 15      ERC      31MAR2027      0      0      0
 16      ERC      31AUG2028      0      0      0
 17      ERC      29DEC2028      0      0      0
 18      PCP      30JUL2022      0      0      0
 19      PCP      31JAN2023    200    200    200
 20      PCP      28FEB2023    235    244     33
 21      PCP      31MAR2023    235    500    654
 22      PCP      31JUL2023      0      0      0
 23      PCP      29SEP2023    238    234    222
 24      PCP      31JAN2024      0      0      0
 25      PCP      31MAY2024      0      0      0
 26      PCP      28JUN2024      0      0      0
 27      PCP      31JUL2024      0      0      0
 28      PCP      30SEP2024      0      0      0
 29      PCP      31OCT2024      0      0      0
 30      PCP      29NOV2024      0      0      0
 31      PCP      28FEB2025      0      0      0
 32      PCP      31MAR2027      0      0      0
 33      PCP      31AUG2028      0      0      0
 34      PCP      29DEC2028    109    546    230
 35      PCP      31DEC2034    234    320    777
 36      PMC      30JUL2022      0      0      0
...

Re: Extend Dates to the existing SAS Dataset

jhh197 — Fri, 13 Jan 2023 20:54:34 GMT

thank you so much for the response . Thanks a lot i am running it now

Re: Extend Dates to the existing SAS Dataset

jhh197 — Fri, 13 Jan 2023 20:55:59 GMT

Thank you so much . Thanks a lot

Re: Extend Dates to the existing SAS Dataset

jhh197 — Tue, 17 Jan 2023 07:55:06 GMT

Hi ,
I had an issue with my SAS profile they are fixing it should be ready by tomorrow will run and update . Thank you so much

Re: Extend Dates to the existing SAS Dataset

jhh197 — Fri, 20 Jan 2023 07:36:54 GMT

Thank you so much for all the help