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 |
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 ...
Please post the data in usable form.
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;
Can anyone please help
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;
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
hi ,
Both queries are giving error not working .
Second query we dont have contype in Test2 table
@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?
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 |
Why did you use
do date= ...
If the variable you had was named TIMESTAMP?
@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.
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 .
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.