DATA Step, Macro, Functions and more

Need help for so proc sql step

Accepted Solution Solved
Reply
Contributor
Posts: 27
Accepted Solution

Need help for so proc sql step

[ Edited ]

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
 


Accepted Solutions
Solution
‎02-18-2017 10:46 AM
Super Contributor
Posts: 250

Re: Need help for so proc sql step

Posted in reply to adityaa9z

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:

Screen Shot 2017-02-18 at 10.27.51 AM.png

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!

View solution in original post


All Replies
Super Contributor
Posts: 250

Re: Need help for so proc sql step

Posted in reply to adityaa9z

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!
Contributor
Posts: 27

Re: Need help for so proc sql step

Posted in reply to DarthPathos
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.
Super Contributor
Posts: 250

Re: Need help for so proc sql step

Posted in reply to adityaa9z

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!
Contributor
Posts: 27

Re: Need help for so proc sql step

Posted in reply to DarthPathos
hey Chris. That didnt work. Smiley Sad
Solution
‎02-18-2017 10:46 AM
Super Contributor
Posts: 250

Re: Need help for so proc sql step

Posted in reply to adityaa9z

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:

Screen Shot 2017-02-18 at 10.27.51 AM.png

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!
Contributor
Posts: 27

Re: Need help for so proc sql step

Posted in reply to DarthPathos
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.
Super Contributor
Posts: 250

Re: Need help for so proc sql step

Posted in reply to adityaa9z

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!
Trusted Advisor
Posts: 1,586

Re: Need help for so proc sql step

Posted in reply to adityaa9z

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;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 241 views
  • 1 like
  • 3 in conversation