Proc Freq and Sort

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 17
Accepted Solution

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: 19,769

Re: Proc Freq and Sort

Posted in reply to Learner25

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;

View solution in original post


All Replies
Super User
Posts: 19,769

Re: Proc Freq and Sort

Posted in reply to Learner25

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: 19,769

Re: Proc Freq and Sort

Posted in reply to Learner25

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.

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

Discussion stats
  • 4 replies
  • 361 views
  • 0 likes
  • 2 in conversation