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

Hi all.

Does anyone have a way to convert the datastep below into an equivalent SQL statement?

/*** add a variable that can be used to find missing rows and exclueded rows ***/

data have;

      length row 8;

      set have;

row = _n_;

run;


Thank you.

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

The only way I am familiar with is the unsupported monotonic() function.  e.g.

:

proc sql;

  create table want as

    select monotonic() as row, *

      from sashelp.class

  ;

quit;

However, while I've never seen it fail, it ISN'T a supported function, thus might fail when you least expect.

View solution in original post

9 REPLIES 9
art297
Opal | Level 21

The only way I am familiar with is the unsupported monotonic() function.  e.g.

:

proc sql;

  create table want as

    select monotonic() as row, *

      from sashelp.class

  ;

quit;

However, while I've never seen it fail, it ISN'T a supported function, thus might fail when you least expect.

jwillis
Quartz | Level 8

Dear Arthur,

Thank you!  I learned a new function.  I have to run through millions of records and I would like to run through them in one SQL rather than a SQL and a datastep. I did not find the monotonic function in the SAS book SAS 9.3 Functions and CALL Routines, Reference.  If I have to check to see if the function failed,  I'm more comfortable running millions of records through the SQL and datastep routine.

http://support.sas.com/resources/papers/proceedings14/1277-2014.pdf

Smiley Happy

art297
Opal | Level 21

I totally agree with Howard and your decision to add a datastep to the process.  The "official" warning from SAS can be found at:

15138 - Support for the MONOTONIC() function in PROC SQL

Ksharp
Super User

So you don't need to jump into the risk of monotonic() by using ods .

ods listing close;
ods output sql_results=want;
proc sql number;
select * from sashelp.class;
quit;
ods listing;

Xia Keshan

jwillis
Quartz | Level 8

Dear Ksharp;

Please explain your code and logic.  It appears that I have to wrap my current sql in ODS but I do not follow your instructions.  I have

proc sql;

   drop table xxxxxxxx;

   connect to oledb as finance ( connection code here ));

   create table xxxxxxxx as

   select     I_want_row_number_here,

                  Q.variables

   from connection to finance

                                    (

                                    select  variables

                                     from  remote_connection_table

                                    where ( selection criteria here )

                                    ) as Q

;

quit;

Ksharp
Super User

If you can see the result at your destination (whatever it is, html listing. pdf), I guess you can use it . NUMBER option just add a count variable in the destination, so I believe it is a safe way . Can you don't create table ,that will not direct any output into destination.

ods listing close; /* <-------- close your destination which you  have already open*/

ods html close;

ods output sql_results=want;

proc sql number;

   connect to oledb as finance ( connection code here ));

   select       Q.variables

   from connection to finance

                                    (

                                    select  variables

                                     from  remote_connection_table

                                    where ( selection criteria here )

                                    ) as Q

;

quit;

ods html;

ods listing;

Xia Keshan

jwillis
Quartz | Level 8

Dear Ksharp;

Thank you for the code.  You taught me something I did not know.  Is there any reason why I cannot simply change my code from  "proc sql;" to "proc sql number"?  I have not found any posts on Google saying I should not use "proc sql number".

http://www2.sas.com/proceedings/sugi29/268-29.pdf

Thank you!

RW9
Diamond | Level 26 RW9
Diamond | Level 26

I believe the ods output is needed as it is taking the default "print" of the sql and re-directing it back to a dataset.  Much like if you do a proc print without specifying noobs.

KSharp, perhaps you could confirm, it would only do 1-number of observations, you couldn't for instance, do groupings on that?

I just wanted to add here on the OP, I notice that your using pass through.  I.e. the inner most SQL will be passed onto the database for processing.  You may then want to check if the database itself has an inbuilt function to assign row numbers - SQL Server/Oracle have ROW_NUMBER() with which you can create unique ID's per groupings.  OC doc: http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions137.htm

Unfortunately this wouldn't work if your not connected to a DB.

jwillis
Quartz | Level 8

RW9,

Thank you!

I am connecting to an Oracle database. 

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
  • 9 replies
  • 109266 views
  • 13 likes
  • 4 in conversation