Extend Dates to the existing SAS Dataset

[Tom · Accepted solution]

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
...

[jhh197]

Thank you so much . Thanks a lot 

[jhh197]

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

[jhh197]

Thank you so much for all the help 

[jhh197]

hi ,

I did run like that but have an issue 

For example for ERC contype in input data 31Jan

Test1 

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 distinct dates in Test2 is 

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;

Output should be like below dates should only be taken only after 29sep2023 but we are getting 30jul2022 also 

ConType	TimeStamp	CP	PP	MM
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	31Jan2024:00:00:00	0	0	0
ERC	31MAR2024:00:00:00	0	0	0
ERC	28JUN2024: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

Can you please help thank you so much 

[jhh197]

Can you please help ?

[jhh197]

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