topic Re: Create Table SQL, Round to Tenth, Export to Excel in SAS Procedures

Create Table SQL, Round to Tenth, Export to Excel

jcis7 — Wed, 05 Jul 2017 19:07:07 GMT

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!

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

Reeza — Wed, 05 Jul 2017 19:32:31 GMT

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?

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

jcis7 — Wed, 05 Jul 2017 20:50:39 GMT

How do you do it? Thanks.

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

Reeza — Wed, 05 Jul 2017 21:30:30 GMT

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)

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

jcis7 — Fri, 07 Jul 2017 18:16:44 GMT

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

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

Reeza — Fri, 07 Jul 2017 18:19:35 GMT

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);

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

Tom — Sat, 08 Jul 2017 14:47:53 GMT

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

select round(sum(C)/sum(D)*100 from have),0.1) as All  
     , ...

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

SASKiwi — Sat, 08 Jul 2017 22:14:23 GMT

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.

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

jcis7 — Wed, 07 Mar 2018 23:57:14 GMT

Thank you everyone!

Two questions:

I just need the value rounded to the tenth exported into an excel worksheet. I tried Tom's code and got the following error when I used the following code.

1. What am I doing wrong?

2. What does (obs=1) do?

Any help you can give will be most appreciated. Thank you!

proc sql;
create table want as
select 'C.' label='A-G',
  (select round(sum(A)/sum(F) from a.have),0.1) as All, 
 (select round(sum(A)/sum(F)   from a.have),0.1) where pub_priv='PUBLIC') as Public,
 (select round(sum(A)/sum(F)   from a.have),0.1) where pub_priv='PRIVATE') as Private
from a.have(obs=1);
quit;

771

772 proc sql;

773 create table want as

786 select 'C. label='A-G',

787 (select round(sum(A)/sum(F) from a.have),0.1) as All,

---- -

22 76

202

ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, ), *, **, +, ',', -, /, <,

<=, <>, =, >, >=, ?, AND, BETWEEN, CONTAINS, EQ, EQT, GE, GET, GT, GTT, IN, IS, LE,

LET, LIKE, LT, LTT, NE, NET, NOT, NOTIN, OR, ^, ^=, |, ||, ~, ~=.

ERROR 76-322: Syntax error, statement will be ignored.

ERROR 202-322: The option or parameter is not recognized and will be ignored.

787! (select round(sum(A)/sum(F) from a.have),0.1) as All,

ERROR 22-322: Syntax error, expecting one of the following: a quoted string, !, !!, &, *, **, +,

',', -, /, <, <=, <>, =, >, >=, ?, AND, AS, BETWEEN, CONTAINS, EQ, EQT, FORMAT, FROM,

GE, GET, GT, GTT, IN, INFORMAT, INTO, IS, LABEL, LE, LEN, LENGTH, LET, LIKE, LT, LTT,

NE, NET, NOT, NOTIN, OR, TRANSCODE, ^, ^=, |, ||, ~, ~=.

788 (select round(sum(A)/sum(F) from a.have),0.1) where pub_priv='PUBLIC') as

788! Public,

789 (select round(sum(A)/sum(F) from a.have),0.1) where pub_priv='PRIVATE') as

789! Private

790 from a.have(obs=1);

791 quit;

NOTE: The SAS System stopped processing this step because of errors.

NOTE: PROCEDURE SQL used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

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

Reeza — Thu, 08 Mar 2018 16:24:12 GMT

You need to add a n after the first name. Which is why there's a best practice to not allow spaces and symbols in the name.

select 'C.'n label='A-G',