Help using Base SAS procedures

How to create drop down buttons in excel by using sas

Reply
N/A
Posts: 0

How to create drop down buttons in excel by using sas

Hi,

I am wondering that whethr we can create drop cdown buttons in excel by using sas. if yes what is the procedure for creating?

usually we create vlookup and drop down buttons in excel manually.
here i would like to create drop down buttons an vlookup table by using sas datasets in excle

please let me know if there a way.

thanks
sudhir Message was edited by: sudhir
SAS Super FREQ
Posts: 8,744

Re: How to create drop down buttons in excel by using sas

Hi:
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:
[pre]
K Kermit
E Elmo
O Oscar the Grouch
B Big Bird
[/pre]

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.

cynthia
[pre]
proc format;
value agef 11='Still a Kid'
12='Early Teenager'
13='Just Starting as a Teenager'
14='Really a Teenager'
15='Trying to Drive'
16='Driving and still a Teenager';
run;

ods tagsets.excelxp file='c:\temp\students.xls' style=sasweb;
proc freq data=sashelp.class;
tables age;
format age agef.;
run;

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.
style(column)={just=l cellwidth=1.5in};
run;
ods tagsets.excelxp close;
[/pre]
N/A
Posts: 0

Re: How to create drop down buttons in excel by using sas

Hi Cynthia,

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?\

thanks
sudhir
SAS Super FREQ
Posts: 8,744

Re: How to create drop down buttons in excel by using sas

Hi:
I'm not sure I know what you mean when you say that "this will only work on excel xp version" -- what do you mean by "this" -- PROC FORMAT?? Autofiltering??

cynthia
N/A
Posts: 0

Re: How to create drop down buttons in excel by using sas

I mean regarding ODS tagsets.
It will not work on msoffice 2k version.

you are auto filtering by using ods tagsets which will not work on msoffice2k.
it works on msoffice xp and aove versions.

please let me know if we can do auto filtering in msoffice2k?
if yes psease give me an example?
PROC Star
Posts: 7,366

Re: How to create drop down buttons in excel by using sas

Can't you accomplish that by generating and calling some vba code? E.g.,
http://www.contextures.com/xlautofilter03.html

HTH,
Art
N/A
Posts: 0

Re: How to create drop down buttons in excel by using sas

HI Can we do this thing in unix platform?
PROC Star
Posts: 7,366

Re: How to create drop down buttons in excel by using sas

Take a look at: http://wiki.services.openoffice.org/wiki/VBA or simply to a Google search for run vba unix

HTH,
Art
Contributor
Posts: 32

Re: How to create drop down buttons in excel by using sas

Sudhir,
Just curious, why are you still using Office 2000? You are 4 versions downlevel.
SAS Super FREQ
Posts: 8,744

Re: How to create drop down buttons in excel by using sas

Hi:
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.)

cynthia
[pre]
** 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
options(doc='Help');
proc print data=sashelp.class;
run;
ods tagsets.msoffice2k_x close;
[pre]
Ask a Question
Discussion stats
  • 9 replies
  • 1095 views
  • 0 likes
  • 4 in conversation