## Proc Freq and Sort

Solved
Occasional Contributor
Posts: 17

# Proc Freq and Sort

[ Edited ]

Hello SAS Experts,

I have created an SQL query that uses inner joint , however my output has two columns with repeating values. I was able to sort the first column by alphabetical order, however - not sure how to sort second in ascending order in each category ( blue and silver).

For an example:

Column  1      Column 2

blue                     3

blue                      5

blue                      1

silver                    15

silver                      6

silver                      0

Also, is it possible to convert the SQL query view into the actual data set?

Thank you

Accepted Solutions
Solution
‎05-21-2016 11:37 PM
Super User
Posts: 23,747

## Re: Proc Freq and Sort

That isn't an inner join, it's a cross join that's then filtered. I recommend an explicit inner join.

Since you've used asterisk notation I don't know what your column names are, so I'm assuming you get only the two columns you've displayed. Assuming that you can specify your order by using numerics which represent your column numbers.

I've also added the CREATE TABLE statement to create a table.

Here's what this might look like:

``````proc sql;
create table want as
select *
from Color
inner join OrderN
on Color.name=OrderN.name
order by 1, 2;
quit;proc print data=want;run;``````

All Replies
Super User
Posts: 23,747

## Re: Proc Freq and Sort

We don't know what you did to obtain the table so it's hard to comment. Please post your code.

You should be able to add an Order by clause to your SQL query. You say you've created a view, you should be able to change that to a table directly in your query.

Again, you need to post your code for optimal help.

Occasional Contributor
Posts: 17

## Re: Proc Freq and Sort

We assume that the Color table has only colors that we are interested to query from the bigger table (OrderN), that has various colors ( in column 1) and corresponding numbers ( in column 2).

The SQL query is written as follows:

proc sql;
select *
from Color, OrderN
where Color.name=OrderN.name
order by Color.name

The result is a query of both tables, Color and OrderN.

Thank you for looking into this.

Solution
‎05-21-2016 11:37 PM
Super User
Posts: 23,747

## Re: Proc Freq and Sort

That isn't an inner join, it's a cross join that's then filtered. I recommend an explicit inner join.

Since you've used asterisk notation I don't know what your column names are, so I'm assuming you get only the two columns you've displayed. Assuming that you can specify your order by using numerics which represent your column numbers.

I've also added the CREATE TABLE statement to create a table.

Here's what this might look like:

``````proc sql;
create table want as
select *
from Color
inner join OrderN
on Color.name=OrderN.name
order by 1, 2;
quit;proc print data=want;run;``````
Occasional Contributor
Posts: 17

## Re: Proc Freq and Sort

Thank you greatly for your help! The table is successfully created and sorted now.

🔒 This topic is solved and locked.