Proc sql

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 104
Accepted Solution

Proc sql

I'm new to proc sql. I want to print the results of proc sql table 

 

can you help me to do that??


Accepted Solutions
Solution
‎03-01-2018 11:47 AM
Super User
Posts: 22,525

Re: Proc sql


mona4u wrote:

I want to see all the rows that's match the criteria 


No rows match that criteria so somethings wrong with the criteria or your data. 

 

View solution in original post


All Replies
Super User
Posts: 9,333

Re: Proc sql

A simple select without a create table before it will write the result to the currently open ODS destination.

 

Describe your issue in a more specific manner, and don't forget to post the code you already tried (see https://communities.sas.com/t5/help/faqpage/faq-category-id/posting?nobounce).

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Frequent Contributor
Posts: 104

Re: Proc sql

Posted in reply to KurtBremser

this is the code that's I'm using. I don't see the results in the result viewer it's only print 

The SAS System without a table 

 

proc sql ;
select empid, jobcode, salary,
salary*.06 as bonus
from sasuser.payrollmaster
where salary <3200
order by jobcode;

Super User
Posts: 9,333

Re: Proc sql

What do you mean by 'it's only print'?

 

When you do

proc sql;
select * from sashelp.class;
quit;

In Enterprise Guide you will see the resulting output in the formats you set in the EG options, in their respective tabs.

In SAS Studio, you see the contents of sashelp.class in the Results tab.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Frequent Contributor
Posts: 104

Re: Proc sql

Posted in reply to KurtBremser
I use window and I don't see a table with my code
proc sql ;

select empid, jobcode, salary,
salary*.06 as bonus
from sasuser.payrollmaster
where salary <3200
order by jobcode;
quit;
Super User
Posts: 22,525

Re: Proc sql

*Creates a data set named WANT in the work library;

proc sql ;
create table want as
select empid, jobcode, salary,
salary*.06 as bonus
from sasuser.payrollmaster
where salary <3200
order by jobcode;
quit;

 

 

*Prints the output to the current ODS open destinations, ie listing or html;

*note the lack of a create table statement, which I've used strikethrough.

proc sql ;
/*create table want as*/ 
select empid, jobcode, salary,
salary*.06 as bonus
from sasuser.payrollmaster
where salary <3200
order by jobcode;
quit;

 

One of these should be what you're looking for.

 

Frequent Contributor
Posts: 104

Re: Proc sql

that's the message that I got  

 

9306 proc sql ;
9307 create table want as
9308 select empid, jobcode, salary,
9309 salary*.06 as bonus
9310 from sasuser.payrollmaster
9311 where salary <3200
9312 order by jobcode;
NOTE: Table WORK.WANT created, with 0 rows and 4 columns.

9313 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.03 seconds
cpu time 0.03 seconds

Super User
Posts: 9,333

Re: Proc sql


mona4u wrote:

that's the message that I got  

 

9306 proc sql ;
9307 create table want as
9308 select empid, jobcode, salary,
9309 salary*.06 as bonus
9310 from sasuser.payrollmaster
9311 where salary <3200
9312 order by jobcode;
NOTE: Table WORK.WANT created, with 0 rows and 4 columns.

9313 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.03 seconds
cpu time 0.03 seconds


This simply means that no observations in the sasuser.payrollmaster dataset match your where condition.

Inspect your dataset.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Frequent Contributor
Posts: 104

Re: Proc sql

Posted in reply to KurtBremser

there is obs bc I checked it by proc print 

Super User
Posts: 9,333

Re: Proc sql


mona4u wrote:

there is obs bc I checked it by proc print 


NO.

Quote from your previous post:

9312 order by jobcode;
NOTE: Table WORK.WANT created, with 0 rows and 4 columns.

9313 quit;

Zero means zero, period

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super User
Posts: 9,333

Re: Proc sql

If you want to create a new table, you have to tell SQL to do that:

proc sql;
create table want as
select *
from sashelp.class;
quit;

Consult the documentation: http://documentation.sas.com/?docsetId=sqlproc&docsetTarget=titlepage.htm&docsetVersion=9.4&locale=e...

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Frequent Contributor
Posts: 104

Re: Proc sql

Posted in reply to KurtBremser

it is wired I was able to print your table without creating a table but with mine I can't 

Super User
Posts: 9,333

Re: Proc sql

If you run that:

proc sql ;
create table want as
select empid, jobcode, salary,
salary*.06 as bonus
from sasuser.payrollmaster
where salary <3200
order by jobcode;
quit;

and no rows are selected, then

proc sql ;
select empid, jobcode, salary,
salary*.06 as bonus
from sasuser.payrollmaster
where salary <3200
order by jobcode;
quit;

will create no output (you get an empty results tab).

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Frequent Contributor
Posts: 104

Re: Proc sql

Posted in reply to KurtBremser

I want to see all the rows that's match the criteria 

Solution
‎03-01-2018 11:47 AM
Super User
Posts: 22,525

Re: Proc sql


mona4u wrote:

I want to see all the rows that's match the criteria 


No rows match that criteria so somethings wrong with the criteria or your data. 

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 14 replies
  • 180 views
  • 5 likes
  • 3 in conversation