BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Obsidian | Level 7

## Need help for so proc sql step

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

1 ACCEPTED SOLUTION

Accepted Solutions
Lapis Lazuli | Level 10

## Re: Need help for so proc sql step

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

Has my article or post helped? Please mark as Solution or Like the article!
8 REPLIES 8
Lapis Lazuli | Level 10

## Re: Need help for so proc sql step

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

Has my article or post helped? Please mark as Solution or Like the article!
Obsidian | Level 7

## Re: Need help for so proc sql step

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.
Lapis Lazuli | Level 10

## Re: Need help for so proc sql step

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

Has my article or post helped? Please mark as Solution or Like the article!
Obsidian | Level 7

## Re: Need help for so proc sql step

hey Chris. That didnt work. 😞
Lapis Lazuli | Level 10

## Re: Need help for so proc sql step

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

Has my article or post helped? Please mark as Solution or Like the article!
Obsidian | Level 7

## Re: Need help for so proc sql step

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.
Lapis Lazuli | Level 10

## Re: Need help for so proc sql step

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

Has my article or post helped? Please mark as Solution or Like the article!
Garnet | Level 18

## Re: Need help for so proc sql step

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;
Discussion stats
• 8 replies
• 1561 views
• 1 like
• 3 in conversation