topic SAS beginer - Issues faced when creating tables using proc sql in New SAS User

SAS beginer - Issues faced when creating tables using proc sql

Deepak13 — Wed, 17 Jul 2019 17:51:09 GMT

proc sql;
create table cars
as select * from SASHELP.cars;
quit;


PG 1:
proc sql;
create table cars
as select * from SASHELP.cars;
quit;

log: 
 1          OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 72         
 73         proc sql;
 74         create table cars2
 75         as select * from SASHELP.cars
 76         where type in ("sports");
 NOTE: Table WORK.CARS2 created, with 0 rows and 15 columns.
 
 77         quit;
 NOTE: PROCEDURE SQL used (Total process time):
       real time           0.00 seconds
       cpu time            0.00 seconds
       
 
 78         
 79         OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 92         

PG 2:
proc sql;
create table cars6
as select * from sashelp.cars
where model like 'T%';
quit;

Log:
 1          OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 72         
 73         proc sql;
 74         create table cars6
 75         as select * from sashelp.cars
 76         where model like 'T%';
 NOTE: Table WORK.CARS6 created, with 0 rows and 15 columns.
 
 77         quit;
 NOTE: PROCEDURE SQL used (Total process time):
       real time           0.00 seconds
       cpu time            0.02 seconds
       
 
 78         
 79         
 80         OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 93         

PG 3:
proc sql;
create table cars7
as select * from sashelp.cars
where make like '%A';
quit;

LOG:
 1          OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 72         
 73         proc sql;
 74         create table cars7
 75         as select * from sashelp.cars
 76         where make like '%A';
 NOTE: Table WORK.CARS7 created, with 0 rows and 15 columns.
 
 77         quit;
 NOTE: PROCEDURE SQL used (Total process time):
       real time           0.00 seconds
       cpu time            0.01 seconds
       
 
 78         
 79         OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 92

Hi,

When I tried to create table from a external file using "where" and "Like" statements, the program tipped no glitch but no results were generated, In simple words no error shown in log but no results were created.

Kindly make use of the highlighted program and attached source file.

NOTE: I use university edition

Re: SAS beginer - Issues faced when creating tables using proc sql

Reeza — Wed, 17 Jul 2019 18:08:40 GMT

where type in ("sports");

Text comparisons are case sensitive. Fix the case to match the data and this should work.

You have similar issues with the rest of your queries, cars6 does not have any models that start with T.

 71                  proc sql;
 72                 create table cars2
 73                 as select * from SASHELP.cars
 74                 where type in ("Sports");
 NOTE: Table WORK.CARS2 created, with 49 rows and 15 columns.
 
 75                 create table cars6 as select * from sashelp.cars where model like 'T%';
 NOTE: Table WORK.CARS6 created, with 0 rows and 15 columns.

EDIT: It appears the Model has a leading space in the field in the SASHELP table.

So the second character is actually T. If you add a space it works correctly as expected.

@Deepak13 wrote:

proc sql;
create table cars
as select * from SASHELP.cars;
quit;


PG 1:
proc sql;
create table cars
as select * from SASHELP.cars;
quit;

log: 
 1          OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 72         
 73         proc sql;
 74         create table cars2
 75         as select * from SASHELP.cars
 76         where type in ("sports");
 NOTE: Table WORK.CARS2 created, with 0 rows and 15 columns.
 
 77         quit;
 NOTE: PROCEDURE SQL used (Total process time):
       real time           0.00 seconds
       cpu time            0.00 seconds
       
 
 78         
 79         OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 92         

PG 2:
proc sql;
create table cars6
as select * from sashelp.cars
where model like 'T%';
quit;

Log:
 1          OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 72         
 73         proc sql;
 74         create table cars6
 75         as select * from sashelp.cars
 76         where model like 'T%';
 NOTE: Table WORK.CARS6 created, with 0 rows and 15 columns.
 
 77         quit;
 NOTE: PROCEDURE SQL used (Total process time):
       real time           0.00 seconds
       cpu time            0.02 seconds
       
 
 78         
 79         
 80         OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 93         

PG 3:
proc sql;
create table cars7
as select * from sashelp.cars
where make like '%A';
quit;

LOG:
 1          OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 72         
 73         proc sql;
 74         create table cars7
 75         as select * from sashelp.cars
 76         where make like '%A';
 NOTE: Table WORK.CARS7 created, with 0 rows and 15 columns.
 
 77         quit;
 NOTE: PROCEDURE SQL used (Total process time):
       real time           0.00 seconds
       cpu time            0.01 seconds
       
 
 78         
 79         OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 92

Hi,

Kindly make use of the highlighted program and attached source file.

NOTE: I use university edition

Re: SAS beginer - Issues faced when creating tables using proc sql

Deepak13 — Thu, 18 Jul 2019 01:57:29 GMT

Many thanks for your constant support Reeza!