BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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
9 REPLIES 9
Cynthia_sas
SAS Super FREQ
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]
deleted_user
Not applicable
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
Cynthia_sas
SAS Super FREQ
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
deleted_user
Not applicable
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?
art297
Opal | Level 21
Can't you accomplish that by generating and calling some vba code? E.g.,
http://www.contextures.com/xlautofilter03.html

HTH,
Art
deleted_user
Not applicable
HI Can we do this thing in unix platform?
art297
Opal | Level 21
Take a look at: http://wiki.services.openoffice.org/wiki/VBA or simply to a Google search for run vba unix

HTH,
Art
DavidJ
Calcite | Level 5
Sudhir,
Just curious, why are you still using Office 2000? You are 4 versions downlevel.
Cynthia_sas
SAS Super FREQ
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]

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 5074 views
  • 0 likes
  • 4 in conversation