Ordering SQL output and formatting

jcis7 — Wed, 05 Jul 2017 18:42:39 GMT

Hi,

I have the following code.

proc sql;
select 'A' label='Variable',
 (select sum(A) from have) as All,
 (select sum(A) from have where SCHTYPE='PUBLIC') as Public,
 (select sum(A) from have where SCHTYPE='PRIVATE') as Private
from have(obs=1)
union 
select 'B' label='Variable',
 (select sum(B)from have) as All,
 (select sum(B)from have where SCHTYPE='PUBLIC') as Public,
 (select sum(B)from have where SCHTYPE='PRIVATE') as Private
from have(obs=1)
union
select 'C' label='Variable',
 (select sum(C) from have) as All,
 (select sum(C) from have where SCHTYPE='PUBLIC') as Public,
 (select sum(C) from have where SCHTYPE='PRIVATE') as Private
from have(obs=1)
union 
select 'D' label='Variable',
 (select sum(D)from have) as All,
 (select sum(D)from have where SCHTYPE='PUBLIC') as Public,
 (select sum(D)from have where SCHTYPE='PRIVATE') as Private
from have(obs=1)
union
select 'A/B' label='Variable',
 (select sum(A)/sum(B) from have) as All format=percent7.2, 
 (select sum(A)/sum(B)  from have where SCHTYPE='PUBLIC') as Public format=percent7.2,
 (select sum(A)/sum(B)  from have where SCHTYPE='PRIVATE') as Private format=percent7.2
from have(obs=1)
union 
select 'C/D' label='Variable',
  (select sum(C)/sum(D) from have) as All format=percent7.2,
 (select sum(C)/sum(D)  from have where SCHTYPE='PUBLIC') as Public format=percent7.2,
 (select sum(C)/sum(D)  from have where SCHTYPE='PRIVATE') as Private format=percent7.2
from have(obs=1)

;
quit;

I am getting the following output but need two things different:

1)Order of rows to be:

A/B

C/E

2) The values of A/B and C/E to be rounded to the nearest tenth of a percent.

How do I do that? Thanks!

Variable	All	Public	Private
A	9398	2758	5281
A/B	25.19571	15.85057	31.43452
B	373	174	168
C	9771	2932	5449
C/D	7.52194	18.44025	8.662957
D	1299	159	629

Re: Ordering SQL output and formatting

Reeza — Wed, 05 Jul 2017 18:44:36 GMT

Since this doesn't have a common order, ie alphabetical or numerical, you'll have to manually order it. This means creating another variable that will have the order you want that's either alphabetical or numerical and you can then sort accordingly.

Re: Ordering SQL output and formatting

jcis7 — Wed, 05 Jul 2017 19:09:39 GMT

Thank you!

topic Re: Ordering SQL output and formatting in SAS Procedures

Ordering SQL output and formatting

Re: Ordering SQL output and formatting

Re: Ordering SQL output and formatting