topic Re: Comparing date variables in New SAS User

Comparing date variables

Pandu2 — Mon, 09 May 2022 10:29:45 GMT

Hi All,

Could anyone please help me in comparing two sas date variables which are in date9 formats only in data step.
For instance, sas_date >= old_date.

Any help would be appreciated, Thankyou.

Re: Comparing date variables

andreas_lds — Mon, 09 May 2022 10:44:05 GMT

If the variables are true date variables, the comparison operators will work as expected.

Re: Comparing date variables

Pandu2 — Mon, 09 May 2022 10:47:06 GMT

Could you please provide me an example. When I try it on my side it gave me the blank data.

Re: Comparing date variables

andreas_lds — Mon, 09 May 2022 10:51:20 GMT

Please post the step you are using along with data in usable form.

Re: Comparing date variables

Quentin — Mon, 09 May 2022 10:52:18 GMT

Please share the code you tried, and also tell us if there were any notes / warnings / errors in the log.

The code in your question, sas_date >= old_date , will work if these two variables exist in your data. Actually, it will work even if one or both of the variables doesn't exist, because SAS will create the variables for you.

So if it's not working, the problem is somewhere else in your code, or you're not understanding the values in the data.

Re: Comparing date variables

Pandu2 — Mon, 09 May 2022 11:09:24 GMT

Data want;
Set have;
by id roll num;
If (first.id and first.roll and first.num)=1 and sas_date >=old_date
Then output;
run;
I sorted the dataset by using those 3 variables and using first keyword to get the 1st variable which should meet the condition sas_date>=old_date but it isn't working instead it is giving me a blank data

Re: Comparing date variables

PaigeMiller — Mon, 09 May 2022 11:28:30 GMT

People asked that you share (a portion of) the data as well. But you haven't done that. We're trying to help you but you have to help us too. We can't help you if we don't have your data.

Please provide the data in usable form, by typing it into SAS data step code, or by following these instructions.

Re: Comparing date variables

Quentin — Mon, 09 May 2022 11:28:20 GMT

Hi,

The code is syntactically correct, so it's likely a problem in your data, or your understanding of the code.

Your current code will select records with both first.num=1 and sas_date>old_date. If the first record for a group does not have sas_date>old_date, then no records will be selected from that group.

If your goal is to select the subset of records that have sas_date>old_date, and then select the first.num record from that subset, that would require different logic:

data want ;
  set have ;
  by id roll num ;
  If first.num=1 ;
  where sas_date >=old_date ;
run ;

The WHERE filtering happens before the data are read into the PDV and identifies the subset you are interested in, then first.num filtering is applied to that subset.

Note that the expression first.num=1 is exactly the same as (first.id and first.roll and first.num)=1 because if first.num=1, then both first.id and first.roll must be 1.

Re: Comparing date variables

Pandu2 — Mon, 09 May 2022 12:07:32 GMT

Thankyou for your assistance, this is the way I want to do. I used your code but it got an error like by variables are not properly sorted Even though they are sorted. Please advise me

Re: Comparing date variables

PaigeMiller — Mon, 09 May 2022 12:09:27 GMT

@Pandu2 wrote:
Thankyou for your assistance, this is the way I want to do. I used your code but it got an error like by variables are not properly sorted Even though they are sorted. Please advise me

@Pandu2

Show us a portion of your data. We have advised you to do this several times already, we can't help you if we can't see your data. Please provide the data following these instructions.

Re: Comparing date variables

Quentin — Mon, 09 May 2022 12:17:24 GMT

You should trust that error message and add a PROC SORT step immediately
before your DATA step that sorts the data by all 3 variables, in the same
order as listed on your BY statement.

Re: Comparing date variables

Pandu2 — Mon, 09 May 2022 12:48:44 GMT

Thank you veryyy muchh, it worked nicely. I would like to add some more info to where condition in your code.
My data table has 6 variables named sas_date,old_date,id,roll,num, amount. I'm unable to add the dataset here, I strongly apologize for that.
After using your code, I closely monitored the output dataset, it met the where condition but it didn't take first.num value so for those cases Please do keep an or condition where if sas_date ge old_date fails and it unable to take the first.num then when there's a increase in amount field that row should be picked and the sas_date>=old_date should also match. Thankyou.

Re: Comparing date variables

Pandu2 — Mon, 09 May 2022 13:00:38 GMT

In this the if condition should apply first then after where condition should apply but it applied oppositely that's what I don't require. Please do make changes accordingly. Thankyou

Re: Comparing date variables

Kurt_Bremser — Mon, 09 May 2022 14:18:16 GMT

Of course you can add a data step with datalines here, everyone can.

If data is sensitive, make up some fake values; what is important is the structure (variable types, lengths, formats), and a description of the expected result from the example dataset.

This is not rocket science, but a very valid skill which every SAS coder MUST have.

Re: Comparing date variables

Kurt_Bremser — Mon, 09 May 2022 14:29:28 GMT

@Pandu2 wrote:
Data want;
Set have;
by id roll num;
If (first.id and first.roll and first.num)=1 and sas_date >=old_date
Then output;
run;
I sorted the dataset by using those 3 variables and using first keyword to get the 1st variable which should meet the condition sas_date>=old_date but it isn't working instead it is giving me a blank data

FIRST. does not indicate variables, it indicates observations. Please be very clear with your choice of words, clarity is the lynchpin of working with software.

Your condition

first.id and first.roll and first.num

is not needed in this form; since it can only be true when first.id is true, and first.id implies first. for all other variables, you only need the first part fo the condition.

The fact that you got no output means that the condition

sas_date >=old_date

is never true for any first observation in a ID group.

If you want to retrieve the first observation of a ID group that fulfills the date condition, you need to do this:

data want;
set have (where=(sas_date >= old_date));
by id roll num; /* roll and num are only here to ensure that the dataset is properly sorted */
if first.id;
run;

Since only observations meeting the date condition make it into the data step, the subsequent FIRST. gets what you want.

If this is not what you want, then you MUST POST EXAMPLE DATA AND THE EXPECTED RESULT.

Re: Comparing date variables

Quentin — Mon, 09 May 2022 19:58:06 GMT

Agree with others. Please post some sample data that illustrates the problem. Ideally post the code with a DATA step with a cards statement that has maybe 10 records.

If you post sample input data, along with the code, and describe how the results you want differ from the results you are getting, people will be able to help you.

And often when you take the time to create a small sample input dataset, you end up solving the problem yourself. Because you immediately have an manageable example you can work with.

Re: Comparing date variables

Pandu2 — Tue, 10 May 2022 01:33:40 GMT

data WORK.have;
input DATE1 :DATE9. DATE2 :DATE9. ID:4. ROLL:2. NUM:3. AMOUNT:2.;
datalines;
01JAN2022 20APR2022 1411 69 115 1
01FEB2022 30JAN2022 1411 69 115 2
01FEB2022 30JAN2022 1411 69 115 3
03MAR2022 21FEB2022 1412 70 116 1
04MAR2022 29JAN2022 1412 70 116 2
;;;;

Here's the data step code of the dataset. The records were sorted based on date1, DATE2, id, roll, num and the primary keys for this dataset is id, roll, num. Based on these 3 variables the data is inter linked with eachother.

When comes to what I require is
I require the rows of 1st appearance of Id, roll, num only when the DATE1 is >= DATE2. This DATE1 >= DATE2 should apply only when the 1st appearance of Id, roll, num. If this fails, please keep an OR condition by saying the amount values should be compared to prior and next value of eachother and when there's an increase in the amount variable values then the row which has max amount value should be picked and it must meet the base condition which is DATE1 >= DATE2.
Example :
01JAN2022 20APR2022 1411 69 115 1
01FEB2022 30JAN2022 1411 69 115 2
01FEB2022 30JAN2022 1411 69 115 3
03MAR2022 21FEB2022 1412 70 116 1
04MAR2022 29JAN2022 1412 70 116 2

In the given example above, the DATE1 value is not >= DATE2 for the 1st appearance of Id, roll, num so, it fails then for the next two values the amount is increased i.e 1,2,3 so, for this case the max amount value which is 3 that row should be picked and also the base condition is met 01FEB2022 >= 30JAN2022. This is one iteration. For the 2nd iteration i.e as the id, roll, num is changed that's why I said it as 2nd iteration. So, in the 2nd iteration the 1st appearance of Id, roll, num(i.e 1412 70 116) met the basic condition i.e 03MAR2022 >= 21FEB2022 this row should be picked. once this condition is pass, it shouldn't go to OR condition.

I hope this makes sense to y'all. Thankyou.

Re: Comparing date variables

Pandu2 — Tue, 10 May 2022 02:54:50 GMT

I forgot to add something previously.
Sometimes the num and roll values will change and the rest ( id) remains same.
For example :
1411 69 115
1411 70 116
1411 71 117
.
.
. Likewise so on.

Re: Comparing date variables

Kurt_Bremser — Tue, 10 May 2022 10:18:20 GMT

@Pandu2 wrote:
I forgot to add something previously.
Sometimes the num and roll values will change and the rest ( id) remains same.
For example :
1411 69 115
1411 70 116
1411 71 117
.
.
. Likewise so on.

And what shall we do in this case? Consider each id/roll/num combination a single group, or each id a group? If the latter, then we do not need to consider roll and num for groups anyway. But are they important for a certain order, or should the order within a group be based on one of the dates?

Re: Comparing date variables

Pandu2 — Tue, 10 May 2022 10:25:09 GMT

In that case, we should consider each id, roll, num combination a single group.