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

Hello:

 

How to generate a SAS file after proc sql?  Thanks.

 

proc sql;
select score from test1
intersect all
select score from test2;
quit;

1 ACCEPTED SOLUTION

Accepted Solutions
mkeintz
PROC Star

Precede the SELECT clause with a CREATE TABLE clause:

 

proc sql;

  create table mytable as

  select score from test1

  intersect all

  select score from test2;

quit;

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------

View solution in original post

5 REPLIES 5
mkeintz
PROC Star

Precede the SELECT clause with a CREATE TABLE clause:

 

proc sql;

  create table mytable as

  select score from test1

  intersect all

  select score from test2;

quit;

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
Reeza
Super User

The documentation is pretty good for SQL - see the section titled 'Creating Tables'

 

https://support.sas.com/documentation/cdl/en/sqlproc/69822/HTML/default/viewer.htm#titlepage.htm

ybz12003
Rhodochrosite | Level 12

My SAS version is 9.3, not 9.4

ballardw
Super User

@ybz12003 wrote:

My SAS version is 9.3, not 9.4


It won't hurt to look at basics of SQL documentation. The basic elements such as Create Table , Select, From, Group By and where clauses haven't changed drastically since SAS 6.8. There may be as few additional features here and there but just as the data step, except for adding new functions, changes slowly so does the base SQL definition.

 

And if you hunt around you will find the version 9.3 documentation on the SAS support website as well.

 

I would also ask why the online help was not installed with your installation. Does the F1 key bring up anything for SAS?

Reeza
Super User

@ybz12003 wrote:

My SAS version is 9.3, not 9.4


It really doesn't matter for this topic, the major difference is with FEDSQL and MEDIAN function, neither of which you appear to be using. And you can find the same section in the 9.3 documentation, if you choose to look. 

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 5 replies
  • 435 views
  • 1 like
  • 4 in conversation