Hi all,
Below are two datasets.
data a;
input id act_no$ date date9. time TIME10. ;
cards;
1 trn1 13FEB2017 2.30am
2 trn1 14FEB2017 3.30am
3 trn2 13FEB2017 2.30am
4 trn3 14FEB2017 2.30am
5 trn3 13FEB2017 2.30am
6 trn3 13FEB2017 3.30pm
7 trn4 13FEB2017 5.30am
8 trn5 13FEB2017 6.30am
9 trn5 13FEB2017 6.30pm
;
run;
data b;
input id pay dollar10.;
cards;
1 10
2 20
3 20
4 40
5 50
6 60
7 70
8 80
9 90
;
run;
I need to see an output like the one mentioned below.. So for the combination of act_no and date , only the first trn which happens within that date should be picked. I used the concept of left join along with group by but to no success. Can someone help out pls.
1 trn1 13FEB2017 2.30am 10
2 trn1 14FEB2017 3.30am 20
3 trn2 13FEB2017 2.30am 30
4 trn3 14FEB2017 2.30am 40
5 trn3 13FEB2017 2.30am 50
7 trn4 13FEB2017 5.30am 70
8 trn5 13FEB2017 6.30am 80
One reason why I don't like "dummy" code - you miss things that should be easy to catch!
I've run this code and I think it's what you're looking for;
proc sql;
select min(a.id), a.act_no, a.date format=date9., min(a.time) format=time., max(b.pay)
from work.A a, work.B b
where a.id = b.id
group by a.act_no, a.date
order by a.act_no, a.date;
quit;
Because the IDs and Pay are different for each of the act_no | date combinations, the MIN was picking up each one (because they were technically the minimum value for that grouping). I've tweaked the code so it gives you:
I realise 4 and 5 aren't in proper order; if you need the data sorted by ID, you can use PROC SQL; create table work.new_data as <SQL query>; quit; and then run PROC SORT on that table.
Keeping my fingers crossed this is what you're looking for!
Chris
Hi - It looks like your Output and your Data A are the same; I'm not sure what your goal is or what question you're trying to answer.
I'm happy to help out, so I will watch for your reply.
Chris
Apparently I've not had enough tea this morning - completely missed that!
I think if you used something like this it should work:
select var_a, var_b, var_c,
min(var_d) as FirstInstance
from work.have
group by var_a, var_b, var_c
order by var_a, var_b, var_c
that should give you what you need. If it still doesn't give you what you're expecting I'll log into my SAS and create your datasets.
Good luck
Chris
One reason why I don't like "dummy" code - you miss things that should be easy to catch!
I've run this code and I think it's what you're looking for;
proc sql;
select min(a.id), a.act_no, a.date format=date9., min(a.time) format=time., max(b.pay)
from work.A a, work.B b
where a.id = b.id
group by a.act_no, a.date
order by a.act_no, a.date;
quit;
Because the IDs and Pay are different for each of the act_no | date combinations, the MIN was picking up each one (because they were technically the minimum value for that grouping). I've tweaked the code so it gives you:
I realise 4 and 5 aren't in proper order; if you need the data sorted by ID, you can use PROC SQL; create table work.new_data as <SQL query>; quit; and then run PROC SORT on that table.
Keeping my fingers crossed this is what you're looking for!
Chris
The MAX(pay) is just to select one item; you could've used MIN(pay) as well. Play around with it and see I mean; if you need anything further, i'm around all day and will be checking in regularly 🙂
Good luck!
Chris
In your output desired data you have selected the first row per ACT_NO and DATE, ignoring the ID .
Assuming your data is sorted by ACT_NO DATE then select the first, by:
data temp;
set data_A;
by act_no date;
if first.date;
run;
that code will filter rows 6 and 9.
Then you need filtering same rows from second table. You can do it by:
proc sql;
crteate table want as select a.*, b.*
from temp as a
left join data_B as b
on a.id = b.id;
quit;
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.