@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.
@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?
@Pandu2 wrote:
In that case, we should consider each id, roll, num combination a single group.
Then we must change the BY and the FIRST. and LAST.:
data WORK.have;
input DATE1 :DATE9. DATE2 :DATE9. ID:4. ROLL:2. NUM:3. AMOUNT:2.;
format date1 date2 yymmdd10.;
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
;
data want;
set have;
by id roll num;
retain _flag _date1 _date2 _roll _num _amount;
if first.num
then do;
_flag = 1;
_amount = .;
if date1 >= date2
then do;
_flag = 0;
output;
end;
end;
if amount > _amount
then do;
_date1 = date1;
_date2 = date2;
_roll = roll;
_num = num;
_amount = amount;
end;
if last.num and _flag
then do;
date1 = _date1;
date2 = _date2;
roll = _roll;
num = _num;
amount = _amount;
output;
end;
drop _:;
run;
As a matter of courtesy and also to make this a useful learning experience, after being provided with code in an answer, it's probably a better idea for you to run the code on your data and see if works, and if you can understand how it works, rather than to ask someone else to run the code on your data for you and send you the results.
Post the complete log of the PROC SORT and the DATA step.
Can't be:
data WORK.have;
input DATE1 :DATE9. DATE2 :DATE9. ID:4. ROLL:2. NUM:3. AMOUNT:2.;
format date1 date2 yymmdd10.;
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
;
data want;
set have;
by id roll num;
retain _flag _date1 _date2 _roll _num _amount;
if first.num
then do;
_flag = 1;
_amount = .;
if date1 >= date2
then do;
_flag = 0;
output;
end;
end;
if amount > _amount
then do;
_date1 = date1;
_date2 = date2;
_roll = roll;
_num = num;
_amount = amount;
end;
if last.num and _flag
then do;
date1 = _date1;
date2 = _date2;
roll = _roll;
num = _num;
amount = _amount;
output;
end;
drop _:;
run;
proc print data=want noobs;
run;
Result:
DATE1 DATE2 ID ROLL NUM AMOUNT 2022-02-01 2022-01-30 1411 69 115 3 2022-03-03 2022-02-21 1412 70 116 1
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!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.