BookmarkSubscribeRSS Feed
MarkNicholas
Obsidian | Level 7


Dear,

I have some data like below with 4 columns and values as below

col1 col2 col3 col4
A      1     X     Y
B     2      P     Q
A     2     A     B
A     1     A     B

By SQL in SAS,
I need duplicates based only on first two columns to be removed. That is, an I get the result as  2,3 rows and any of 1 and 4 rows ??

Expected result:

B     2      P     Q

A     2     A     B

A     1     A     B

(or)

A      1     X     Y

B     2      P     Q

A     2     A     B

I know its pretty simple in SAS, but need help in SQL

PS: all SQL commands do not work in proc SQL

Thanks

Mark

7 REPLIES 7
Patrick
Opal | Level 21

If your data resides in a data base then use pass-through SQL and you will have access to all native functionality of this data base (eg. analytical functions like rownum() and then select the first record in the window). If it's a SAS table then why try and use SAS SQL if this can be done so easily using PROC SORT.

MarkNicholas
Obsidian | Level 7

It is not SAS dataset.  I just gave it as example for your reference. It is a database table containing around 20 million records. So we chose native functionality to get the result, but unfortunately stuck at this moment and are looking for any chance to complete this step as well using sql itself

Patrick
Opal | Level 21

Well, if it's a database then you should try and reduce the volumes directly on the database before transferring the data to SAS. What's the database?

Ksharp
Super User

SQL in SAS is standard SQL . therefore it is hard for SAS version SQL. But you could try this one :

data have;
input col1 $ col2 col3 $ col4 $;
cards;
A      1     X     Y
B     2      P     Q
A     2     A     B
A     1     A     B
;
run;
proc sql;
 create table want as
  select * 
   from have
    group by col1,col2
      having cats(col1,col2,col3,col4) =max(cats(col1,col2,col3,col4));
     quit;

XIa Keshan

stat_sas
Ammonite | Level 13

proc sql;

select col1,col2,max(col3) as col3,max(col4) as col4 from have

group by col1,col2;

quit;

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
  • 7 replies
  • 1377 views
  • 6 likes
  • 5 in conversation