VLOOKUP is generally when you have information on one sheet that is used to "expand" or "translate" information on another sheet, right???
So let's say on one sheet, I had this:
O Oscar the Grouch
B Big Bird
and then on another sheet, I wanted to translate the K, E, O, B to their expanded values.
In SAS, you do this kind of translation with a user-defined format....and you would use the format BEFORE you sent the results to Excel. So, for example, if I had a format for the AGE variable in SASHELP.CLASS, I can use that format in SAS procedures to expand the value of AGE before the output is opened with Excel. In the program below, the PROC FORMAT step builds the format and then in the PROC FREQ and the PROC REPORT, the format is used in each step. The PROC REPORT step shows the unformatted value for age and the formatted value for age in the same report.
You can even build your format from a SAS dataset or external file, if you look up the FORMAT documentation on using CNTLIN.
value agef 11='Still a Kid'
13='Just Starting as a Teenager'
14='Really a Teenager'
15='Trying to Drive'
16='Driving and still a Teenager';
ods tagsets.excelxp file='c:\temp\students.xls' style=sasweb;
proc freq data=sashelp.class;
format age agef.;
proc report data=sashelp.class nowd;
column name age age=agefmt sex height weight;
define name / display;
define age / display;
define agefmt / display 'Format Applied' f=agef.
ods tagsets.excelxp close;
yes i worked on this previously on this. This will work only on excel xp version.
But i want to do this ms excel 2000 version. the code which you have mentioned below will not work in office 2k. Is there any way to do this on msofice 2k?
if yes can you please gimme the detalied example?\
I believe the new TAGSETS.MSOFFICE2K_X tagset does do autofiltering (note the _X suffix to the name of the destination). For more information, look at the help by running the code below (after you install and download the new tagset template): http://support.sas.com/rnd/base/ods/odsmarkup/msoffice2k/index.html
(the Introduction on the above site contains the link to the TS note for how to update tagset templates.)
** First you need to download the new tagset template and install.;
** Then look at the available options in the SAS log;
ods tagsets.msoffice2k_x file='c:\temp\students.xls' style=sasweb
proc print data=sashelp.class;
ods tagsets.msoffice2k_x close;