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 ...
Thank you so much . Thanks a lot
Thank you so much for all the help
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
thank you so much for the response . Thanks a lot i am running it now
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: