Need help for so proc sql step

[adityaa9z]

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
 

[DarthPathos]

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

[adityaa9z]

Hey Chris,

Thanks for reaching out. Actually if you see in the output dataset , 6th and 9th transactions dont exist. this is because on the same day another transaction happened which was earlier then them.

Eg :
5 trn3 13FEB2017 2.30am
6 trn3 13FEB2017 3.30pm

for the combination of trn3 and date-13FEB2017, I only want to pick the one with 2.30am since that happened earlier than 3.30 pm.

Hope this helps.

I also added the pay variable to the output DS since I missed it earlier.

[DarthPathos]

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

[adityaa9z]

hey Chris. That didnt work. 😞

[DarthPathos · Accepted solution]

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

[adityaa9z]

Output looks great Chris. Thanks.

Id is the primary key. Pay can be same or different. But wondering why we have to use min and max with Id and pay. If you could help me understand it please.

Thanks again.

[DarthPathos]

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

[Shmuel]

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;