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

Hi all,

at the moment, I've got a code which does the following:

1) sorts a DATASET1 by a VAR1 in ascending order, then by VAR2 in ascending order

2) selects removes duplicates of VAR1, but selects the highest value of VAR2

3) appends this to DATASET2 to another one by VAR1

Is it possible to combine these three steps into 1 using a proc sql?

so like

proc sql;

     create table NEWTABLE as select

     a.*, b.VAR2

     from DATASET1 as a

          left join DATASET2( [conditions which make 1-3 work]) as b on b.VAR1 = a.VAR1;

quit;

thanks,

Marco

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

So condition 1 and 2 are fulfilled by the subquery (select VAR1,MAX(VAR2) from DATASET1 group by VAR1).  This should return one VAR1 with the maximum VAR2 within that group.  Then the other dataset is left joined onto that.

View solution in original post

3 REPLIES 3
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Hi,

Could you clarify your request a bit.  A left join is not an append but a merge.  You have also put conditions 1-3 in the from of dataset 2 where 1 and 2 are applicable to only dataset 1 from your text.  As a rough guess:

proc sql;

     create table WANT as

     select     A.*,

                   B.VAR3

     from       (select VAR1,MAX(VAR2) from DATASET1 group by VAR1) A    

     left join   DATASET2 B

     on           A.VAR1=B.VAR1;

quit;

Lupacante
Calcite | Level 5

Thank you for your answer.

You're right, I got the datasets mixed up.


This is what I would have wanted:

proc sql;

     create table NEWTABLE as select

     a.*, b.VAR2

     from DATASET2 as a

          left join DATASET1( [conditions which make 1-3 work]) as b on b.VAR1 = a.VAR1;

quit;

thanks,

Marco

RW9
Diamond | Level 26 RW9
Diamond | Level 26

So condition 1 and 2 are fulfilled by the subquery (select VAR1,MAX(VAR2) from DATASET1 group by VAR1).  This should return one VAR1 with the maximum VAR2 within that group.  Then the other dataset is left joined onto that.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 2093 views
  • 0 likes
  • 2 in conversation