topic Re: Proc sql in SAS Programming

Proc sql

mona4u — Thu, 01 Mar 2018 15:51:47 GMT

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

can you help me to do that??

Re: Proc sql

Kurt_Bremser — Thu, 01 Mar 2018 15:54:39 GMT

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).

Re: Proc sql

mona4u — Thu, 01 Mar 2018 16:00:18 GMT

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;

Re: Proc sql

Kurt_Bremser — Thu, 01 Mar 2018 16:08:50 GMT

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.

Re: Proc sql

mona4u — Thu, 01 Mar 2018 16:12:04 GMT

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;

Re: Proc sql

Reeza — Thu, 01 Mar 2018 16:15:26 GMT

*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.

Re: Proc sql

mona4u — Thu, 01 Mar 2018 16:28:27 GMT

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

Re: Proc sql

Kurt_Bremser — Thu, 01 Mar 2018 16:28:36 GMT

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=en

Re: Proc sql

Kurt_Bremser — Thu, 01 Mar 2018 16:29:45 GMT

@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.

Re: Proc sql

mona4u — Thu, 01 Mar 2018 16:30:34 GMT

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

Re: Proc sql

mona4u — Thu, 01 Mar 2018 16:31:43 GMT

there is obs bc I checked it by proc print

Re: Proc sql

Kurt_Bremser — Thu, 01 Mar 2018 16:34:08 GMT

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).

Re: Proc sql

Kurt_Bremser — Thu, 01 Mar 2018 16:35:26 GMT

@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

Re: Proc sql

mona4u — Thu, 01 Mar 2018 16:36:03 GMT

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

Re: Proc sql

Reeza — Thu, 01 Mar 2018 16:45:29 GMT

@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.