Help using Base SAS procedures

Create Table SQL, Round to Tenth, Export to Excel

Reply
Regular Contributor
Posts: 194

Create Table SQL, Round to Tenth, Export to Excel

[ Edited ]
proc sql;
create table want as
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 'E' label='Variable',
 (select (sum(A)/sum(B))*100 from have) as All, 
 (select (sum(A)/sum(B))*100 from have where SCHTYPE='PUBLIC') as Public,
 (select (sum(A)/sum(B))*100  from have where SCHTYPE='PRIVATE') as Private
from have(obs=1)
union 
select 'F' label='Variable',
  (select (sum(C)/sum(D)*100 from have) as All,
 (select (sum(C)/sum(D)*100  from have where SCHTYPE='PUBLIC') as Public,
 (select (sum(C)/sum(D)*100  from have where SCHTYPE='PRIVATE') as Private
from have(obs=1)

;
quit;

Hi,

 

 

I have the code above and want to export the table into Excel with the percentage values for F and G rounded to the tenths. Is it possible to do so?

 

Thank you!

Super User
Posts: 19,855

Re: Create Table SQL, Round to Tenth, Export to Excel

 


jcis7 wrote: 

I have the code above and want to export the table into Excel with the percentage values for F and G rounded to the tenths. Is it possible to do so?

 

Thank you!


Yes, it's possible. 

 

What exactly is your question?

Regular Contributor
Posts: 194

Re: Create Table SQL, Round to Tenth, Export to Excel

How do you do it? Thanks.
Super User
Posts: 19,855

Re: Create Table SQL, Round to Tenth, Export to Excel

http://blogs.sas.com/content/sasdummy/2012/02/11/export-excel-methods/

 

For the percents, apply a PERCENT format. 

 

select 'A' label='Variable',
 (select sum(A) from have) as All,
 (select sum(A) from have where SCHTYPE='PUBLIC') as Public format=percent8.1,
 (select sum(A) from have where SCHTYPE='PRIVATE') as Private format=percent8.1
from have(obs=1)

 

 

Regular Contributor
Posts: 194

Re: Create Table SQL, Round to Tenth, Export to Excel

Thanks.  The formatting doesn't stay when the table is exported. 

Super User
Posts: 19,855

Re: Create Table SQL, Round to Tenth, Export to Excel

That depends on how you export it. 

Use ODS EXCEL to maintain the formats. 

Or use PUT to convert the data to a character variable with the format.

 

char_var = put(old_var, percent8.1);
Super User
Posts: 3,260

Re: Create Table SQL, Round to Tenth, Export to Excel

Please clarify: do you want to maintain your data at full precision but just display the values as rounded, or do you want to actually round the data as stored? If it's the latter then use @Tom's solution.

Super User
Super User
Posts: 7,074

Re: Create Table SQL, Round to Tenth, Export to Excel

If you want to round the results use the ROUND() function.

 

select round(sum(C)/sum(D)*100 from have),0.1) as All  
     , ...
Ask a Question
Discussion stats
  • 7 replies
  • 227 views
  • 0 likes
  • 4 in conversation