SAS Office Analytics, SAS Add-In for Microsoft Office, and other integrations

recherchev and vlookup not accepted in excelxp tagsets . any reason?

Reply
Occasional Contributor
Posts: 6

recherchev and vlookup not accepted in excelxp tagsets . any reason?

Hi there. I am stuck with my code. anyone knows why my formula is not beeing accepted in this ods tagsets.excelxp? I have even tryed the english version (vlookup) of recherchev, but it is not recognized as a formula. ods listing close;               ods tagsets.excelxp               options(sheet_name="LISTE"               gridlines="yes"                 orientation="landscape" panelcols="1"                 embedded_titles="NO"                 frozen_headers="YES"                 frozen_headers="1" autofilter="yes"                 filter_colls="_all_"                 autofilter_table="1" row_heights="75"                 autofit_heights="no"                 );         proc report data=reftable.liste nowd ; column  actif Portefeuille  Conseiller  Poste majeure equipe ps Agregat Portefeuille kl ; define Portefeuille /style(header)=vertcl_header                                                                         style(column)=[just=center]; define equipe      /style(column)=[just=center] "Équipe"; define poste        /style(column)=[just=center] ;                                                 define majeure      /style(column)=[just=center]; define ps          /style(column)=[just=center] format=fm_num.; define Agregat      /style(column)=[just=center] "Agrégat"; define actif      /computed style(column) ={tagattr='formula:=RECHERCHEV(RC[1];STRUCTURE_CAISSE!R1C1:R20C1;1;FAUX)'};           run;  quit; ods tagsets.excelxp close; Thanks.

Grand Advisor
Posts: 10,210

Re: recherchev and vlookup not accepted in excelxp tagsets . any reason?

Excelxp writes XML and that may be the main issue.

To see what the XML might be, make a base spreadsheet in excel that does what you want. The SAVE AS XML.

Look in the resulting XML file with a text editor like Notepad.

I am not sure if XML supports any look up and that may be the reason that going through the XML translation it gets lost.

Grand Advisor
Posts: 17,325

Re: recherchev and vlookup not accepted in excelxp tagsets . any reason?

Your code isn't formatted and hard to read so I'm not sure I see the Formulas option specified in your tagset options. If not, try that.

Base SAS: Quick Reference for TAGSETS.EXCELXP Tagset

FORMULASyesData values that start with an '=' will become formulas
Ask a Question
Discussion stats
  • 2 replies
  • 314 views
  • 0 likes
  • 3 in conversation