Re: help SAS/MS ACCESS, general database questions
Without having enough time to analyze excatly, what are your needs: if there are no important reasons why the data must be keept in MS Access: why you don't keep it all in SAS? If you are analyzing in SAS, there is no real reason to have any other DB to hold the data. For users you have many possibilities to give them comfortable possibilities to enter data, in or outside SAS. In SAS, the most easy idea is to use SAS/FSP. But that is not necessary. You can use nearly any tool for data entry and bring the (standard formatted) data into SAS. To get a consultant for realizing a proper solution might be a good idea. It sometime is not easy trying to learn to program in SAS by developing a productive solution, because it might be not easy to get it in time with all the obstacles, there might be in your way. If you have time (and money) enough, you should take the chance to learn how to do some steps further in SAS programming together with a good consultant. On Tue, 2 Mar 2004 10:26:59 -0500, Rob Cheshire <rob.cheshire@NOAA.GOV> wrote: >Hi to all, > I need some help deciding on the best database system for our >specific application. >OPTIONAL INFO: We currently have 2 separate projects that need to be >updated from dos-based dBase. I'll only describe one of these but the >other is comparable in size. The project currently has about 32 >files. The 2 largest are about 30,000 records and 5 columns include >data from the last 30 yea...

Re: help SAS/MS ACCESS, general database questions #2
You're describing my project with the Department of Education! We had a dbase database and used a Clipper application to massage it into 'presentable' form. And I thought I was on the bleeding edge (back edge, that is) 8 years ago. As with everything, it depends on what your main purpose is. In my case, it was to disseminate the information to as many people as possible in a format that could be read by/into as many applications as possible. So, we converted our data to flat files and supplied an 'application' that generated sas code that would read the flat files into a sas dataset (complete with formats and labels), so the researcher could write their own code to run against the sas dataset. It sounds like your project isn't quite as ambitious, but the reason I include this example is that it's hard to predict what form data will take, say, 5 years from now. I'll bet you've already run into difficulties in using Clipper, and you'd like to avoid these kind of problems the next time you update/analyze your data. Sas has had some changes in going from one version to another--generally, upgrading to the next version involves updating your dataset to the next version's format. So, if you're purpose is to archive your data for the next iteration of your survey, unless you're going to be active in the data, sas might not be the best choice for data storage. Somewhat more stable would be something like SQL server (cheap, ...

Re: How to access automatically in SAS a MS Access table name #3
Jean - Unfortunately you are stuck - SAS only allows dataset names up to 32 bytes long - using name literals or conventional names. Defining short-named queries of your tables within MSAccess is a very good idea. The SAS libname engine will read the queries as tables and it doesn't necessitate changing your table names. Paul Choate DDS Data Extraction (916) 654-2160 -----Original Message----- From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of JKamgang@Yahoo.com Sent: Wednesday, January 16, 2008 9:32 AM To: SAS-L@LISTSERV.UGA.EDU Subject: How to access automatically in SAS a MS Access table name which has more than 32 characters? Dear All, Happy New Year , Please, I need your help in figuring out how to access in SAS a MS Access table name which has more than 32 characters. As this program merges 5 databases, I need to have almost everything automated. That's why I don't want to rename manually the table names. Please find the program below. Thanks in advance for your kind assistance, Jean Baptiste The program: - - - options VALIDVARNAME=ANY ; libname DBsource access 'S:\Data Management\External\Projects\MTCT-Plus\MTCT-Plus November 2007 Data\Programs\Data\MTCT-PlusData All.mdb'; /* Creating the Pediatric HIV result information additional */ data* pedResultadd ; set dbsource.'tblInfantHIVTestResultsAdditional'n (keep = testid studyid labid i2 i2_da i2_mo i2_yr i2a i2b i2text ); * run...

Re: SAS and MS Access #3
DDE works. Here is an example: filename ecw dde "msaccess|ecw;view For Publication!all" lrecl=2000 notab; data ecw; infile ecw firstobs=2 dsd dlm='09'x missover; input country : $60. [etc.] ; [etc.] A serious problem arises if there are newlines within text fields. If they are not masked somehow, they are interpreted as end-of-record indicators, which makes a real mess of things.. If there are simple ways to mask newlines, in either direction (Access to SAS or SAS to Access), I don't know them. In the example above, I am reading a view which I created solely to drop the fields containing newlines (fortunately I did not need those fields on the SAS side). On Wed, 5 May 2004 23:33:43 -0400, Peter Hruby <hrubyp@SYMPATICO.CA> wrote: >Hi SAS-L, > >I am wondering what choice do I have to access tables in MS Access datbase. >Can I use SAS/Access to ODBC or SAS/Access to PC Files or DDE or WB scripts? >I appreciate any experience and examples. > >Thanks >Peter. ...

Re: old SAS guy with new SAS question #3
On Sun, 16 Nov 2008 20:33:21 -0800, Daniel Nordlund <djnordlund@VERIZON.NET> wrote: >> -----Original Message----- >> From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On >> Behalf Of John F. Regus >> Sent: Sunday, November 16, 2008 7:19 PM >> To: SAS-L@LISTSERV.UGA.EDU >> Subject: old SAS guy with new SAS question >> >> You three were of the most help to an old man in simplifying >> how to get output from my PC file into a SAS dataset. >> However, when I did use the INPUT statement I coded INPUT $. >> thinking this would put everything into its natural place in >> the output SAS table. Wrong. It only got the first variable >> of 5 characters. >> >> I went through my "Little SAS Book" trying to find the answer >> to this but the "Little SAS Book" has an appropriate >> title...it is little and does not cover a lot of things. >> SAS help and documentation takes you over the hills and >> through the woods without getting to the point or even a >> concise answer. >> >> Here is my problem. >> >> I am reading a .csv PC flat file (I conquered the problem of >> reading the PC file into a SAS dataset by adding the INPUT >> statement...only not all the data goes through because I need >> to use columnar input instead of list input). >> >> The layout of the .csv PC flat file is so...

Re: MS Access and SAS datasets #3
Thanks for the input. Unfortunately, the datasets are quite wide. One of them, for example, has over 1000 variables (columns). Had I been involved with the creation of the initial SAS datasets, I would have suggested a slightly different approach. Since this wasn't the case, I'm stuck working with things the way they are. To complicate things, ACCESS won't handle tables that wide so I'm having to mess with he data anyway. Your (and Howard's) suggestions will at least get me going on the right path. (Maybe I'll just round up some "slave" labor, AKA a graduate student, to set up all the lookup tables in ACCESS.) Thanks again David Neal Choate, Paul@DDS wrote: > Hi David - > > I agree with Howard that CNTLOUT will give you your formats to develop > lookup tables in Access. Alternately if your data isn't large and you > don't care about normal form, you could process your variables through your > formats with put statements and create parallel formatted data. > > In v9 there's VLABEL, VLABELX, and VARLABEL, etc. You can use VLABEL and > array all your _numeric_ and then _character_ columns and create a label > dataset. > > There's also the data dictionary which you can query and use the into: > command to load a macro, or just dump the labels in a dataset. > > Personally, I like to use "options VALIDVARNAME=ANY;" and rename my > variables as their labels before...

Re: Excel to SAS, general questions #3
Bernie I believe that the archive reference was to converting SAS files to Excel. My advice is to save the Excel files in csv format. You can then either cut and paste them into one large text file (if they are all alike in format) or, if you need to keep them separate, you can move them individually to the mainframe by whatever means you have available. Once on the mainframe, you can have SAS read the data set(s) with a comma specified as the delimiter. The compexity of the task will depend on how uniform the excel sheets are. Nat The Zimmerman's <beezee@EROLS.COM To: SAS-L@LISTSERV.UGA.EDU > cc: Sent by: "SAS(r) Subject: Re: Excel to SAS, general questions Discussion" <SAS-L@LISTSERV.U GA.EDU> 01/26/05 12:55 PM Please respond to The Zimmerman's Folks: To answer Nat's question with less than certainty, to the best of my limited knowledge, we're talking mainframe SAS and not SAS/PC. BTW, I did scan the archives but didn't think what I found could solve this particular problem. Thanks again, Bernie Zimmerman beezee@erols.com -----Original Message----- From: Nathaniel_Wooding@dom.com [mailto:Nathaniel_Wooding@dom.com] Sent: Wednesday, January 26, 2005 12:...

Re: Easy SAS Question -- Please help me #3
Or if you need breaks between "J"is as J is then TRANSLATE() can be helpful: data t; input i $; cards; "this" "j"is a "crazy" "quo ote" ; data t; set t; j = dequote(i); k = compress(i,'"'); m = translate(i,' ','"'); proc print data = t; run; -----Original Message----- From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Terjeson, Mark (IM&R) Sent: Tuesday, June 28, 2005 9:34 AM To: SAS-L@LISTSERV.UGA.EDU Subject: Re: Easy SAS Question -- Please help me Hi Nitin, How about COMPRESS()? data t; input i $; cards; "this" "j"is a "crazy" "quo ote" ; data t; set t; j = dequote(i); k = compress(i,'"'); proc print data = t; run; Hope this is helpful. Mark Terjeson Senior Programmer Analyst, IM&R Russell Investment Group Russell Global Leaders in Multi-Manager Investing -----Original Message----- From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Nitin Sent: Tuesday, June 28, 2005 9:25 AM To: SAS-L@LISTSERV.UGA.EDU Subject: Easy SAS Question -- Please help me Sas-L, I am parsing an ASCII text file with unpredictable data. I want to "dequote" some of the variables. The...

Re: MS Access
Thanks Joe! Your posts always offer good info and they help me learn a lot. I only tried the data step update because it was something I read about in one of the many helpful SAS papers out there. That explains the log 'replace' note versus an 'update' note. So, here's what I did. I made a little temp data set with 2 obs and 3 variables: State_Project_Number Report_Month Payment_Disburse 0005-05-101 06-2009 111111 0008-14-110 06-2009 111111 Next, used SQL (my SQL knowledge being very limited): PROC SQL; UPDATE RADS.MONTHLY_S...

Re: Import MS Access data to SAS #3
In v9 with PC Files Access there's my favorite - the libname engine. http://support.sas.com/onlinedoc/913/getDoc/en/acpcref.hlp/pclibname.htm libname mymdb "c:\demo.mdb"; proc sql; select idnum, lname from mymdb.staff where state='NY' order by lname; quit; hth Paul Choate DDS Data Extraction (916) 654-2160 > -----Original Message----- > From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Guibo > Xing > Sent: Friday, March 24, 2006 11:10 AM > To: SAS-L@LISTSERV.UGA.EDU > Subject: Import MS Access data to SAS > > Hi all, > > What is a good way to transfer MA Access data to SAS. I used proc import > (or > import wizard), but some data formats were lost. Thanks! ...

Re: How to create index in Access database out of SAS? #3
I'm curious. If you already have data in SAS, why would you want to export it to Access? What can you do with it in Access that you cannot do in SAS? On Jan 31, 2008 10:17 AM, Green4Ever <bulcket@scarlet.be> wrote: > Hello, > > First I do an export of my SAS-dataset to an Access Database, then I > want to create an index on a field in my Access Database. Can I do > this with SAS and how? > > Thanks, > > Tom > ...

Re: General question about SAS version 9 #3 649217
Maura Unless you have a licensing agreement that is new to me, you have already paid for version 9. Updates are included in the annual licensing fees which SAS Institute charges. You will find that the institute works very hard to keep the system as compatible as possible as updates come. In the past, there were a couple major changes such as a requirement that strings be enclosed in quotes but these were brought on as SAS moved from the mainframe to multiple platforms and operating systems. One item to be aware of: V9 will read a V8 data set but V8 cannot read a V9 set. Hence, if you go th...

Re: Exporting Multiple SAS files to MS Access #3
thanks so much Paul, worked great! ...

Re: from SAS to MS Access without Proc Export #3
On Mon, 22 Nov 2004 15:03:59 -0500, Bill Droogendyk <bill_droogendyk@DOFASCO.CA> wrote: >Folks: > >I've an application running from my PC via SAS Connect to the Mainframe >that harvests some MF data, downloads the data and uses proc export to >build an MS Access file. Now and again, the app fails due to a glitch >on our PC network. I'm doing it this way because we don't have proc >export on the MF. > >I can produce xl files from the MF to the PC network with ODS, html and >ftp. MS Access imports xl files quite readily, but xl files do have a >size limit that we sometimes exceed.Is there also a simple "equivalent" >way to produce (directly) ms access files with only SAS Base? > >Thanks, Bill Hi, Bill, Consider producing .csv file on MF side, Then, later, move it to pc. MS Access should have no problem importing (or linking) .csv file into its table. Just my $.02 Cheers, Chang ...

Re: Need for speed: writing to a database with SAS/ACCESS #3
Also try the bulkload option Darryl Putnam IT, Corporate Credit & Risk darryl.putnam@constellation.com (o) 410-468-3637 (c) 301-788-3347 -----Original Message----- From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Pardee, Roy Sent: Thursday, December 21, 2006 11:53 AM To: SAS-L@LISTSERV.UGA.EDU Subject: Re: Need for speed: writing to a database with SAS/ACCESS Have you tried playing w/the DBCOMMIT option on the libname statement? Try bumping that up to say, 10k & see if it makes a difference. Other options to play with available here: http://support.sas.com/onlinedoc/913/getDoc/en/acreldb.hlp/a002915222.ht m HTH, -Roy -----Original Message----- From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Dimitri Shvorob Sent: Wednesday, December 20, 2006 10:53 PM To: SAS-L@LISTSERV.UGA.EDU Subject: Need for speed: writing to a database with SAS/ACCESS Occasionally, I have to transport a SAS dataset into a (local, single-user) MySQL database, and have used MySQL and ODBC engines of SAS/ACCESS for this task. In both cases, and regardless of whether I transfer data with something like 'data db.table; set sas.dataset; run;', or with PROC COPY, I have found writes to be *very* slow - a singe numeric column with 100,000 rows might well take 10 minutes to pass to MySQL. I was wondering if there are ways to improve the write speeds, and would appreciate a suggestion or a helpful link. Thank you. >>> This e-mail and any atta...

Re: Can you create a new Access database from SAS? #3
PROC EXPORT will do so (create a new access database if it doesn't already exist). PROC EXPORT data=(whatever) outtable=(table name) dbms=access replace; database=(.mdb file); run; -Joe On Wed, Feb 18, 2009 at 2:04 PM, Norris, Paul <Paul.Norris@ucsf.edu> wrote: > Is it possible to create a new Access database from SAS? > > I have a client for whom I create a delivery in the form of an Access > database every month, and it would be helpful to be able to create the > delivery vehicle in SAS, as opposed to just loading data into it. > > Thanks, > > Paul Norris > UCSF > ...

Re: General question about SAS version 9 #3 1548094
Maura - SAS9 is backwards compatible with SAS8 so nearly everything will work exactly as it did under v8. On the output side ODS has been expanded and proc document especially comes to mind. http://xrl.us/ggyo As far as syntax goes a couple big changes were the addition of PERL regular expressions and the hash object in the datastep. http://xrl.us/ggyq & http://xrl.us/ggyp SAS9 adds a lot of new functionality, so it was a very favorable upgrade for me. hth Paul Choate DDS Data Extraction (916) 654-2160 -----Original Message----- From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Maura Iversen Sent: Monday, June 20, 2005 6:39 AM To: SAS-L@LISTSERV.UGA.EDU Subject: General question about SAS version 9 Hello, This is my first time to the listserve. Sorry to ask such a basic question. I am currently using version 8 SAS and the office is thinking of purchasing version 9. Are there any major changes in the format of teh output or in command syntax with this new version? Thank you. Maura ...

Re: Mainframe SAS to PC located MS ACCESS database
As is so common in this language, the answer is 'it depends on what you're trying to do'. You can establish an odbc connection from a host job to your SQL Server db (fairly sure it can do Access, too), but 1) your login info is exposed in open code (and while you can do some things to hide it, it's still out there (it's not encrypted)), and 2) if you're transmitting large volumes of data, odbc is not very efficient. If you're only going after a few rows of data, and you don't mind a potential exposure of your credentials, then go the odbc route (this also assumes that you don't have to jump through too many hoops to connect to a PC/server--some shops have restricted access to only machines on the network that they have control over--in which case you either need to move your data onto one of those machines, or have your machine put on the mapped/approved list). If you want to move large volumes, or run a complex join/stored procedure on the server side (or can't easily gain access to your server from the host), then you might do a push up to the host instead of a pull. Create an export file that can be ftp'd, and then go fetch it with your MVS batch job. If you give us a few more details, maybe we can give you a more precise answer. CH On Fri, 7 Oct 2005 15:36:11 -0500, Davis Kriss P <kpdavis@ILSTU.EDU> wrote: >Dear SAS-L members, > >Is there a "SAS" or "other" set of products that would a...

Re: SAS on a Unix machine accessing a SQL Server database (on a #3
Charles, The OleDB interface will be more versatile since it can run across a number of sources. Alan Alan Churchill Savian www.savian.net -----Original Message----- From: Charles Chambers [mailto:ayo6@CDC.GOV] Sent: Wednesday, August 27, 2008 5:45 AM To: SAS-L@LISTSERV.UGA.EDU; Alan Churchill Cc: Charles Chambers Subject: Re: SAS on a Unix machine accessing a SQL Server database (on a Windows machine): how? SAS/Access has a Interface to Microsoft SQL Server for Unix. Refer to page 67 and 74 of the online SAS/Access pdf. http://support.sas.com/documentation/onlinedoc/91pdf/sasdoc_913/access_rdbr ef_10385.pdf ...

Re: R vs. SAS (was Replacement for SAS (SPSS vs. SAS, redux)) #3
Wow, that was a leap. Better not use Apache or PHP then. Andy >-----Original Message----- >From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On >Behalf Of ben.powell@CLA.CO.UK >Sent: Thursday, February 05, 2004 10:37 AM >To: SAS-L@LISTSERV.UGA.EDU >Subject: Re: R vs. SAS (was Replacement for SAS (SPSS vs. SAS, redux)) > >Sounds like R is the SAS equivalent of Linux .. who are >incidentally in all >sorts of a copy left/right mess at the moment. > >On Thu, 5 Feb 2004 09:39:37 -0500, Peter Flom <flom@NDRI.ORG> wrote: > >>I use both SAS a...

Re: SAS Advanced Programming Exam for SAS 9: SAS Joke of the year. #3
Okay, I think I need to weigh-in on this. First, the exam is designed to test what you know about how SAS works. I think it does a fair job at that. (I say so partly because I only scored 89 on the exam. Now, I didn't prepare for the exam; I was really testing the exam when I took it as opposed to using the exam to test my skills.) The exam doesn't know how well you can program. There is a big difference between having a large vocabulary and knowing the rules of grammar and knowing how to write. Similarly, there's a big difference between knowing how SAS works and knowing how to program. The exam does not test how well you can program. That said, when I look for someone to hire that I don't know, I need everything I can get to evaluate the candidate. Sure, it would be nice if they brought a portfolio of their code so I could see what they can write. That seldom happens, and when it does the code is often not really written by the applicant. So, I look for other things. An applicant often tells what they worked on, but that too can be exaggerated. What am I left with? Well, someone can program who doesn't know SAS, but probably they won't be a very good SAS programmer. I'd rather have the exam to judge than to not have the exam. If I were to apply for a job where the employer didn't know me, I would also want the certification to help them make the decision. Oh, as for the version 9 questions, I remember finding several. Just d...

Re: SAS/access for Oracle and MS SQL server are different license? #3
Thanks Michael, But SAS/Access to ODBC or SAS/Access to OLE DB are also need to be licensed right? Looks like we don't have them either. Are there other options? On Tue, 28 Apr 2009 15:37:28 -0400, Michael Raithel <michaelraithel@WESTAT.COM> wrote: >Dear SAS-L-ers, > >Ya Huang posted this interesting question: > >> Hi there, >> >> I ran proc setinit and see we have SAS/ACCESS Interface to >> ORACLE, but not MS SQL server. Does it mean we need to pay >> the license of SQL server in order to access SQL server? I >> see people sometimes simply say "SAS/access for RDBMS", does >> it mean all RDBMS (Oracle, DB2, SQL Server, MySQL etc.) are >> under one umbrella, so that you only need to pay SAS/access for RDBMS? >> >Ya, here at SAS Mecca a number of folks use SAS/Access to ODBC or SAS/Access to OLE DB to access and crunch data stored in SQL Server databases. Seeing as you do not have SAS/Access to MS SQL, perhaps you have one of the aforementioned products... If so, then you should be good- to-go. If not, then it is time for Plan B... which will require the presence of your company's accountants:-( > >Ya, best of luck in all of your SAS endeavors! > > >I hope that this suggestion proves helpful now, and in the future! > >Of course, all of these opinions and insights are my own, and do not reflect those of my organization or my associates. All SAS code and/or meth...

Re: SAS Sample 1728 Was ---> Re: SAS Sample 1727 #3
First, let me admit/acknowledge that I have no knowledge of the history of this thread. Been tied up catching up post SAS Global Forum and post the announcement of sasCommunity.org. But I would like to offer some thoughts regarding Toby's comment: > No as for the sascommunity.org, do we really want a macro library. Well I > would say yes and no. I would like to have one and there are those who > have > paced some Macros on there already. I believe Don and Ron both have, > actually they were on there before the Wiki went public. However, since > people tend to get Ma...

Re: Sas Question (Re-organizing dataset) #3
Ward , If you have a whole bunch of vars wth no standard nameing scheme I would recommend proc transpose. But you them all starting with 'code' and ending with asequential number. Easy enough: Data Have ; Infile Cards ; Input Name $ Code1 $ Code2 $ Code3 $ Amount $ ; Cards ; Leroy X13 H42 G91 $4,900 Mary D14 X42 X41 $8,100 Lynn C45 X41 H99 $3,000 ; Run ; Data Need ( Drop = Code1 Code2 Code3 I ) ; Set Have ; Do I = 1 To 3 ; Code = VValueX( 'Code'||Put(I , 8. -L) ) ; Output ; End ; Run ; Proc Print Data = Need ; Run ; Toby Dunn When everything is coming at you all at once, your in the wrong lane. A truly happy person is someone who can smile and enjoy the scenery on a detour. From: wardnine@HOTMAIL.COM Reply-To: wardnine@HOTMAIL.COM To: SAS-L@LISTSERV.UGA.EDU Subject: Sas Question (Re-organizing dataset) Date: Mon, 18 Sep 2006 16:25:27 -0700 Suppose I have a dataset with the variables name, code1, code2, code3, and amount. The dataset is thousands of lines long, but here is a 3-line sample (the order of the 5 variables of data is the same as the order of the variables I list in the first sentence of the post)... Leroy X13 H42 G91 $4,900 Mary D14 X42 X41 $8,100 Lynn C45 X41 H99 $3,000 I need to re-arrange the data so that the name and each code on each row is assigned the amount on that row and for each variable and corresponding amount to appear on a separate line. So, I would need the following dataset: Leroy X13 $4,...