BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Q1983
Lapis Lazuli | Level 10

proc sql;

create table setup_summ as

select cost_ctr,count(cnt) as cnt1

from setup

group by cost_ctr

order by cost_ctr

;quit;

 

Cost_ctr   Cnt

                   10

4320102    133

4320105    294

4320545    957

4327979    619

4327983    489

4327984    493

4327985    529

4327996    51

 

I tried defining cost_ctr as both numeric and text. As you can see if no cost_ctr exists the blank cost center goes to the top

I want the blank cost center to show at the bottom however I still want to sort by cost center from lowest to the highest. The sequence should be the same except the blank one should show at the bottom.  How can I accomplish this

1 ACCEPTED SOLUTION

Accepted Solutions
kiranv_
Rhodochrosite | Level 12

In sql you can do something like this. 

proc sql;
select a.cost_ctr, a.cnt from 
(select cost_ctr, cnt from have)a
cross join
(select  max(cost_ctr) +1 as nmax from have)b
order by coalesce(a.cost_ctr, nmax) ;
quit;

View solution in original post

2 REPLIES 2
novinosrin
Tourmaline | Level 20

Try this:

 

proc sql;

   title 'A UNION B';

 create table setup_summ as

select cost_ctr,count(cnt) as cnt1

from setup

where your_variable ne ' ' /*Please notice here and change accordingly*/

group by cost_ctr

order by cost_ctr

   union

  select cost_ctr,count(cnt) as cnt1

from setup

where your_variable=' ' /*Please notice here and change accordingly*/

group by cost_ctr;quit;

 

kiranv_
Rhodochrosite | Level 12

In sql you can do something like this. 

proc sql;
select a.cost_ctr, a.cnt from 
(select cost_ctr, cnt from have)a
cross join
(select  max(cost_ctr) +1 as nmax from have)b
order by coalesce(a.cost_ctr, nmax) ;
quit;

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 2 replies
  • 760 views
  • 1 like
  • 3 in conversation