cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
ScottBass
‎03-11-2021
  • 808 Posts
  • 184 Likes Given
  • 28 Solutions
  • 326 Likes Received
This widget could not be displayed.

Re: Start a remote SAS session with the same libraries as a workspace ...

by Rhodochrosite | Level 12 in SAS Programming
‎02-17-2013 05:14 PM
‎02-17-2013 05:14 PM
I've got this working now. ... View more

Re: Macro and where

by Rhodochrosite | Level 12 in SAS Programming
‎02-17-2013 05:24 AM
‎02-17-2013 05:24 AM
Hi, You need to make it clearer:  is it one, two, or "multiple values".  If two, just use two parameters as above.  If it's "multiple values", where the number could vary, review the doc for parmbuff. SAS(R) 9.2 Macro Language: Reference Scott ... View more

Re: Macro and where

by Rhodochrosite | Level 12 in SAS Programming
‎02-17-2013 03:43 AM
‎02-17-2013 03:43 AM
I'm a big fan of Richard DeVenezia's %seplist macro (and pretty much all things DeVenezia...really smart dude).  The %seplist macro makes it really easy to move between data step and SQL syntax.  Just keep your parameters as space delimited, and use %seplist when you need comma separated values. See SAS Macros - by Richard A. DeVenezia and SAS Macros - by Richard A. DeVenezia - seplist.sas I think the syntax would be (untested): %macro test(var1=);     sql--statements;    where id in (%seplist(&var1,nest=QQ)); %mend; %test(var1=A B C); Download the macro, read the header, and test with %put %seplist(...). HTH, Scott ... View more

Start a remote SAS session with the same libraries as a workspace serv...

by Rhodochrosite | Level 12 in SAS Programming
‎02-07-2013 10:17 PM
‎02-07-2013 10:17 PM
Hi, We have this environment: Server: Windows Server 2008 Enterprise x64 SAS 9.3 in a managed environment (SAS Metadata Server, Lev1/Lev2, etc). Desktop: Windows XP Professional x32 (bleeding edge, huh?) SAS 9.3 Some staff still use SAS DMS on their desktop, rather than EG connecting to the workspace server on the server.  I need to configure a desktop environment that instantiates a SAS/Connect session as identical as possible to an EG workspace server session, esp. with respect to the allocated libraries.  The libraries are defined as pre-assigned in the metadata.  Some of these libraries use the Oracle engine.  The Oracle client is installed on the server. I tried this: %let conport = 7551; %let sa01=our.remote.server.com &conport; options comamid=tcp remote=sa01; options sascmd='E:\SAS\Config\Lev1\SASApp\WorkspaceServer\WorkspaceServer.bat'; signon; thinking that would launch the WorkspaceServer.bat file on the remote server.  But, a command window flashed on my local machine, and I got an error. On the remote server (RDP session), I did this from a command window: D:\Program Files\SASHome\SASFoundation\9.3>sas -metaserver "remote.server.com" -metaport 8561 -metarepository "Foundation" -metaprotocol BRIDGE -metaautoresources "SASApp" All the libraries were properly allocated.  I then submitted this in SAS: filename set pipe "set"; data _null_;   infile set;   input;   put _infile_; run; Partial output: ORACLE_HOME=d:\oracle\11.2.0\client_1 Path=D:\Oracle\11.2.0\client_1\bin;D:\Oracle\11.2.0\client_1;C:\Windows\system32;C:\Windows;C:\Win dows\System32\Wbem;C:\Windows\System32\WindowsPowerShell\v1.0\;C:\Program Files (x86)\Java\jre1.6. 0_24\bin;D:\Program Files (x86)\sysinternals I submitted this on my local SAS session: %let conport = 7551; %let sa01=our.remote.server.com &conport; options comamid=tcp remote=sa01; options sascmd='sas -metaserver "remote.server.com" -metaport 8561 -metarepository "Foundation" -metaprotocol BRIDGE -metaautoresources "SASApp"'; signon; rsubmit; filename set pipe "set"; data _null_;   infile set;   input;   put _infile_; run; endrsubmit; I won't list the results, but ORACLE_HOME wasn't set, nor was Oracle in the path.  I got errors in the log for all the Oracle libraries that were pre-assigned via the metadata. Any idea how I can instantiate a SAS/Connect session which mirrors a workspace server session instantiated by EG? Thanks, Scott ... View more

Re: Executing %sysget without generating an error?

by Rhodochrosite | Level 12 in SAS Programming
‎02-07-2013 09:28 PM
‎02-07-2013 09:28 PM
What is the "this" you're referring to above?  The suppression of the warning or the use of environment variables? ... View more

Re: Executing %sysget without generating an error?

by Rhodochrosite | Level 12 in SAS Programming
‎02-07-2013 04:13 PM
‎02-07-2013 04:13 PM
Doh!  I guess I should read the "What's New in SAS 9.3" documentation!  I was looking under the macro documentation for %sysexist to pair up with %sysget. To SAS:  I do think %sysexist would be useful, to add "symmetry" to these functions: Data Step: symexist --> symget sysexist --> sysget Macro: %symexist --> %symget doesn't make sense here, just reference the &macrovariable %sysexist --> %sysget Thanks a lot Tom, I always appreciate your insights. ... View more

Executing %sysget without generating an error?

by Rhodochrosite | Level 12 in SAS Programming
‎02-07-2013 06:55 AM
‎02-07-2013 06:55 AM
Hi, Is there a way to either conditionally execute %sysget, or unconditionally execute it without causing a warning message? For example, with macro variables: %macro foo;   %if (%symexist(foo)) %then %do;      %if (&foo eq BAR) %then         %put BAR;       %else          %put BLAH;      %end;   %end; %mend; Also, NOSERROR can come in handy sometimes to suppress warnings about unresolved macro variables. So, analogously: %macro foo;   %if %sysexist(foo) %then %do;  * the mythical %sysexist function ;      %let foo=%sysget(foo);      %if (&foo eq BAR) %then         %put BAR;       %else          %put BLAH;      %end;   %end; %mend; I'd kinda think a %sysexist function, paired with %sysget, would be a no-brainer for SAS by now??? Cheers, Scott ... View more

Re: Moving existing AMO content in Excel to a new location

by Rhodochrosite | Level 12 in Microsoft Integration with SAS
‎01-25-2013 08:23 AM
‎01-25-2013 08:23 AM
Bump.  Surely there must be a way to move SAS content after it's first created??? ... View more

Moving existing AMO content in Excel to a new location

by Rhodochrosite | Level 12 in Microsoft Integration with SAS
‎01-23-2013 05:45 PM
‎01-23-2013 05:45 PM
Hi, Apologies if I've missed this in the doc and/or user interface... Say I create SAS content in Excel via AMO, for example loading SASHELP.CLASS into $A$1.  Later I decide I want to move the location to $A$10 (for example to add some explanatory text above the content). Is there a way to move the existing content, or do I need to delete and recreate it? Thanks, Scott ... View more

Re: Creating 1000+ versions of an Excel workbook

by Rhodochrosite | Level 12 in SAS Programming
‎01-23-2013 01:45 AM
‎01-23-2013 01:45 AM
I don't think I can use ODS tagsets to derive the workbook "from scratch" since it wouldn't contain all the VBA code in the target workbook.  This is why I think I need to copy a template workbook. ... View more

Re: Creating 1000+ versions of an Excel workbook

by Rhodochrosite | Level 12 in SAS Programming
‎01-22-2013 05:30 PM
‎01-22-2013 05:30 PM
Hi, Thanks all.  We have SAS and Excel on the server machine.  Also EG but that's irrelevant.  We have local SAS (so DMS), EG, and Excel on the local machine.  We have SAS/Access to PC File Formats on the local machine only.  Weird I know, it came down to dollars in licenses fees for SAS/Access to PC File Formats on the server. So, we do have Excel on the server, but the end user is using local EG connecting to SAS on the server.  I'm not sure if EG can use DDE at all, regardless of whether Excel is on the server or not.  I've *never* used DDE. The other post mentioned generating a dynamic where clause using cells in an Excel workbook.  The purpose of this post is to intialize the values of those cells for the thousand workbooks.  That's the only relevance to the other post. If we limit the scope of this question to "how do I set the value of a cell in Excel to the value of a variable in a SAS dataset", I see three approaches so far: * DDE * PROC EXPORT to a named range.  I didn't know about that one - thanks Reeza -  but I assume I need SAPCFF on the server. * Export the SAS data to Excel and use VBA I'm thinking VBA may be the way to go in this scenario. Scott ... View more

Re: Creating 1000+ versions of an Excel workbook

by Rhodochrosite | Level 12 in SAS Programming
‎01-22-2013 03:53 PM
‎01-22-2013 03:53 PM
Hi All, Thanks for the replies so far... I can see now that my reference to the other posting was a red herring and only confused the issue.  It was mentioned merely to set context.  So forget that reference; let me restate the problem. * I have a data set containing records and variables.  In the case of SASHELP.CLASS, I have 19 records and two variables of interest, NAME and SEX. * I have a template Excel file, template.xlsm. * I want to create 19 copies of this template Excel file, named according to the data in my SAS data set.  If this was all I wanted to do, the problem would be trivial.  For example, I could easily generate a simple batch file with "copy template.xlsm <name>_<sex>.xlsm" statements, and then just execute it. * However, I also want to set a particular cell in the copied Excel file to a particular value.  In the case of SASHELP.CLASS, it is the value of NAME.  This is my sticking point. I should also point out, while I have full access to DMS, EG, etc, the end user only has access to EG.  So, DDE may not be a viable option.  I could generate the output files for her, but I would prefer she be self-sufficient. So, in summary: * Given a template Excel file (heck for a POC it can just be an empty file), can I set a particular cell to the value of a SAS variable?  Using EG or VBA? * If not, then same question, without the restriction that it be EG. * If VBA is my only option then just let me know.  I can export the data set to either a file or Excel worksheet, then write VBA to spin through that data and create the output workbooks (with a lot of referencing to VBA books/websites!) I posted here because AFAIK DDE falls under Base programming. Apologies for the confusing initial message. Thanks, Scott ... View more

Creating 1000+ versions of an Excel workbook

by Rhodochrosite | Level 12 in SAS Programming
‎01-22-2013 07:34 AM
‎01-22-2013 07:34 AM
Hi, Not sure which discussion board to post this in???  Hopefully this is a good spot.  This post is also related to I have a dataset with 1000+ provider (hospital) numbers and matching hospital name.  I need to create an Excel workbook named like <hospital_number>_<hospital_name>.xlsm.  So this will be 1000+ workbooks. I also need to set a data cell in each copy of the workbook to the value of the hospital number. Say we use SASHELP.CLASS as a proof of concept.  Say I create the template workbook as template.xlsm.  Say I want to create Alfred_M.xlsm, Alice_F.xlsm, etc (NAME_SEX.xlsm).  And say I want to set Sheet1, $B$1 = <name>. Can you suggest an approach?  Although I've rarely used DDE, perhaps that could be used?  Or, I could load SASHELP.CLASS into a worksheet, then write a VBA macro to loop over the rows, set the cell in template.xlsm, then Save As <name>_<sex>.xlsm.  I'll have to hit the VBA books for that approach.  Or perhaps there's a better approach? Thanks, Scott ... View more

Re: Find out is a csv file is in PC or UNIX format?

by Rhodochrosite | Level 12 in SAS Programming
‎01-18-2013 07:09 PM
‎01-18-2013 07:09 PM
What data _null_ said. As the saying goes "If the only tool you own is a hammer, everything starts to look like a nail".  Other than as an academic exercise in SAS, why don't you just ensure that your input files are in Unix format? Google "sed convert dos to unix".  This was the first hit:  HowTo: UNIX / Linux Convert DOS Newlines CR-LF to Unix/Linux Format Also, if you're FTPing the files from Windows to Unix and vice versa, make sure you transfer text files as TEXT not BINARY.  The FTP protocol will handle converting the line terminators to the correct value for the given operating system.  If you're using an FTP client, add .CSV, .SAS, .LOG, .LST, etc to the list of filename extensions that are considered text files. Hope this helps, Scott ... View more

Re: Dynamic queries using Addin for Microsoft Office

by Rhodochrosite | Level 12 in Microsoft Integration with SAS
‎01-17-2013 06:26 AM
‎01-17-2013 06:26 AM
Hi, My questions above are answered in this SGF paper:  http://support.sas.com/resources/papers/proceedings11/012-2011.pdf I've also learned that there isn't any online documentation (i.e. http://support.sas.com documentation) for automating AMO.  The best resource is the help file in AMO. Thanks, Scott ... View more
Contact Me
Online Status
Offline
Date Last Visited
‎03-11-2021 01:34 AM
Group Hubs for ScottBass
View All
Likes given to
View All
Likes from
Quick links