topic Re: Simple creation of a table with SQL? in SAS Programming

Simple creation of a table with SQL?

SallyS — Thu, 12 Nov 2015 18:57:11 GMT

I was joining two simple tables that reside on an SQL server and thought I would check if there was a difference if I named the parent table first or second. There was and I can't figure out why. Can anyone help please?

I am also wondering what happens when variables have the same name?

Here is the log:

proc sql noprint;
create table cmd1 as
select b.*, a.*
from cmdclaim.claim a, cmdclaim.claimdetail b
where b.claimid=a.claimid
order by a.claimid;
WARNING: Variable ClaimID already exists on file WORK.CMD1.
WARNING: Variable DiagnosticCode already exists on file WORK.CMD1.
WARNING: Variable ModifiedDate already exists on file WORK.CMD1.
WARNING: Variable CreatedDate already exists on file WORK.CMD1.
NOTE: Table WORK.CMD1 created, with 50788 rows and 48 columns.

16 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 15.14 seconds
cpu time 3.41 seconds

proc sql noprint;
create table cmd1 as
select b.*, a.*
from cmdclaim.claim a, cmdclaim.claimdetail b
where a.claimid=b.claimid
order by a.claimid;
WARNING: Variable ClaimID already exists on file WORK.CMD1.
WARNING: Variable DiagnosticCode already exists on file WORK.CMD1.
WARNING: Variable ModifiedDate already exists on file WORK.CMD1.
WARNING: Variable CreatedDate already exists on file WORK.CMD1.
NOTE: Table WORK.CMD1 created, with 50811 rows and 48 columns.

23 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 14.73 seconds
cpu time 3.54 seconds

I would appreciate any help understanding the difference in the results.

Sally

Re: Simple creation of a table with SQL?

Jagadishkatam — Thu, 12 Nov 2015 19:48:39 GMT

ok to explain , please try to pull the required variables only from one dataset. if there are common variables between two datasets then try to pull required variables from only one dataset. since you kep b.* and a.* even the common variables are pulled from both the datasets. So try a.* and b.specific variable names from other datasets. So that you will not get those warnings.

Re: Simple creation of a table with SQL?

Reeza — Thu, 12 Nov 2015 20:47:04 GMT

What kind of join are you doing and what kind did you intend to do?

Re: Simple creation of a table with SQL?

Patrick — Thu, 12 Nov 2015 22:04:52 GMT

To add to what has been said already:

1. Use option "options sastrace=',,,d' sastraceloc=saslog nostsuffix;" to see in the log what SQL syntax can get sent to the DBMS.

2. Use option "fullstimer" to get a bit more timing information in the log.

3. Use "feedback" to see in the log if SAS changes the syntax: Proc SQL feedback; ....

5. That the two queries return a different number of rows is concerning and the only explanation I'm having is that you had a different number of rows in your source tables when you executed them

5. CPU and Real Times are not always the same between two runs even for exactly the same syntax. To really see if there is a difference between two SQL code versions you would have to execute them multiple times.

6. If you want to dig deeper then copy the SQL syntax sent to the DBMS from the log and analyze it further using sql server management studio https://technet.microsoft.com/en-us/library/ms191227(v=sql.105).aspx

Re: Simple creation of a table with SQL?

PGStats — Thu, 12 Nov 2015 21:41:52 GMT

This is unexpected. Both results should have the same number of obs. Unless the tables were modified between the two queries. Did you try running the first query again?

The query is most likely run on the server. So you may be looking at ~~peculiarities~~ features of SQL Server.

Repeated names are removed from the select list. That should not affect the number of obs, unless you are using DISTINCT.