topic Re: ODS excel proc print variable format in ODS and Base Reporting

ODS excel proc print variable format

Kwok — Mon, 25 Feb 2008 13:58:15 GMT

Hi,

I have to output a dataset with 68 variables to ODS Excel tagset and one of the vaiable require the format style={"mso-number-format:$#,###,###.##"};

For example,
ODS Tagsets Excelxp..... ;
proc print;
run;
ODS close; run;

But if I need to format a variable( for example , the 23th) then I have to
ODS Tagsets Excelxp..... ;
proc print;
Var var1,var2,var3....var22;
var var23 / style(data)={tagattr="format:$###,###,###.00"};
var var24, var25... var68;
run;
ODS close; run;

Any tip or trick to avoid to write out all the variables, especially when the variable names are long.

Thanks

Re: ODS excel proc print variable format

Andre — Mon, 25 Feb 2008 14:20:02 GMT

Use this program before replace the field with correct upcase informations
go then on the log to copy/paste your list of variables

into you multiple var statements in your ods tagsets part

[pre]
proc sql noprint;
select name into :list separated by ' '
from dictionary.columns
where libname="SASHELP" and memname="CLASS";
run;
quit;
%put &list;

[/pre]

Re: ODS excel proc print variable format

Kwok — Mon, 25 Feb 2008 16:25:59 GMT

Hello Andre,

Thanks for your idea. I tested your code as is and it work very well. But I am still a newbie to SQL and when I substitute the libname and memname with a test sasdataset 'clean8' in c:\. The SQL indicated the &list is not resolved:

15 proc sql noprint;
16 select name into :list separated by ' ' from dictionary.columns
17 where libname="C:\"
18 and memname="clean8";
NOTE: No rows were selected.
19 run;
NOTE: PROC SQL statements are executed immediately; The RUN statement has no effect.
20 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds

WARNING: Apparent symbolic reference LIST not resolved.
21 %put &list;
&lis

Do you know what my error is ?

Thanks

Re: ODS excel proc print variable format

Andre — Mon, 25 Feb 2008 16:39:05 GMT

You were asking text generation corresponding to
a libname and a table to use inside a proc print

of course c:\ is not a libref which must be written in upcase
and clean8 is not in upcase and is perhaps a table name reachable only if you have passed a libname previous

so correct your input, please

Andre

Re: ODS excel proc print variable format

Cynthia_sas — Mon, 25 Feb 2008 17:02:39 GMT

Hi:
The LIBNAME and MEMNAME should always be in caps...when you're querying any of the dictionary tables (dictionary.columns, etc). So your memname should be CLEAN8. The libname is not C:\ ... remember that a libname is just a nickname for a directory/folder location on your disk. So in this case:
[pre]
libname wombat 'c:\temp\mydata';
[/pre]

WOMBAT is the nickname given to the directory path c:\temp\mydata. The 2 level name of a SAS dataset in this location will be WOMBAT.xxxxxxxx where the first level of the name is the nickname that is in the LIBNAME statement. The second level of the name (xxxxxxxx) will be the SAS dataset name.

Let's say that I go out to the directory and look in c:\temp\mydata folder and I find 3 SAS datasets -- I can tell they're SAS datasets (in version 8 and 9) because they will have a file extension of SAS7BDAT...so I have these files:
c:\temp\mydata\account.sas7bdat
c:\temp\mydata\members.sas7bdat
c:\temp\mydata\updates.sas7bdat

The 2 level name of each of these files would be:
WOMBAT.ACCOUNT
WOMBAT.MEMBERS
WOMBAT.UPDATES

Notice how the libname/nickname 'WOMBAT' takes the place of the high level directory path and the data set name, ACCOUNT or MEMBERS or UPDATES becomes the second level of the 2-level name.

If you are creating a temporary data set, and you refer to it in your program by a ONE level name...something like this:

[pre]
proc print data=clean8;
[/pre]

then the "official" LIBNAME of your data set is 'WORK' and the 'official" 2-level name of your file is WORK.CLEAN8. So in your case, you might have a query with:
where libname = 'WORK' and memname='CLEAN8';

You say that your test sas data set is in c:\ -- look in your program for a LIBNAME statement, or if you don't have one, try this:

[pre]
libname mydata 'c:\';

proc contents data=mydata._all_;
title 'What SAS datasets are in C:\';
run;
[/pre]

Now, look in the output from the PROC CONTENTS to make sure that your file, CLEAN8 is in that directory location. If it IS in the list, then your query would be:
[pre]
where libname = 'MYDATA' and memname='CLEAN8';
[/pre]

If your CLEAN8 file is not in the root directory, then you might try using Windows Explorer to search for it on your C: drive until you find it. The Windows name of your file would be: clean8.sas7bdat -- and, the directory location where you find it is the location you'd use in your LIBNAME statement.

For more help with this question, you might consider contacting Tech Support or posting to the non-ODS forum since you're now venturing into the world of more general SAS questions like using LIBNAME statements, using macro variables and/or using SQL.

cynthia

Re: ODS excel proc print variable format

Kwok — Mon, 25 Feb 2008 18:33:08 GMT

Thank you for your explanation. It works !