topic Re: proc sql question in SAS Programming

proc sql question

MarkNicholas — Fri, 03 Apr 2015 05:13:57 GMT

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

Re: proc sql question

Kurt_Bremser — Fri, 03 Apr 2015 06:44:56 GMT

What keeps you from using proc sort with nodupkey?

Re: proc sql question

Patrick — Fri, 03 Apr 2015 08:15:28 GMT

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.

Re: proc sql question

MarkNicholas — Fri, 03 Apr 2015 08:25:44 GMT

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

Re: proc sql question

Patrick — Fri, 03 Apr 2015 08:37:56 GMT

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?

Re: proc sql question

Kurt_Bremser — Fri, 03 Apr 2015 09:18:11 GMT

Then you best do that step in the database itself. Get help from your DBA and use his code in SQL passthrough.

Re: proc sql question

Ksharp — Fri, 03 Apr 2015 13:55:34 GMT

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

Re: proc sql question

stat_sas — Fri, 03 Apr 2015 16:02:14 GMT

proc sql;

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

group by col1,col2;

quit;