BookmarkSubscribeRSS Feed
monalisa
Calcite | Level 5

I have a below sample SAS query which i need to convert ot SQl.

Can any one please help me in understanding the query, which is:

Merge Dataset1 (In=data1 keep col1, col2....)

           DataSet2

           DataSet3

           Dataset4

By id;

if data1=1 

%ran

4 REPLIES 4
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Hi,

 

Not much I could really add to what is in the documentation:

http://support.sas.com/documentation/cdl/en/basess/58133/HTML/default/viewer.htm#a001304305.htm

 

At a quick look, the one I would start with is A full join B on A.ID=B.ID.

SQL joins are:

Capture.PNG

Reeza
Super User

What's in %ran?

 

Dataset1 is left joined with 2,3,4 on key field ID. 

monalisa
Calcite | Level 5

Am new to SAS. What i understood till now is SAS merges 2 dataset at a time (not sure though). If this is the case, then here Dataset1 left join dataset2, then output left join dataset3 and so on.Is this correct?

Reeza
Super User

@monalisa wrote:

Am new to SAS. What i understood till now is SAS merges 2 dataset at a time (not sure though). If this is the case, then here Dataset1 left join dataset2, then output left join dataset3 and so on.Is this correct?


No, I don't think that's accurate. 

 

If you have multiple IDs in each dataset you won't get the results you expect compared to SQL, ie many to many match. 

 

The IF IN statement is what's used to filter the join and create a left join. 

Your best bet is to create a sample dataset and compare the results between the two Procs until you get your desired result. 

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!

How to Concatenate Values

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.

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
  • 4 replies
  • 1078 views
  • 0 likes
  • 3 in conversation