f



Re: MS Access and SAS datasets #6

You are right, the "slightly" was an understatement on my part.(I guess I
was being nice.)
I believe ACCESS is limited to 256 (or 255 I forget which) columns so I am
required to do a bit of reshaping anyway.  I will be working with(reshaping)
the data in SAS and then move it into ACCESS.  I feel much more comfortable
tweaking it in SAS and then moving it to ACCESS.

David

-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Howard
Schreier <hs AT dc-sug DOT org>
Sent: Tuesday, May 24, 2005 5:26 PM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Re: MS Access and SAS datasets

I suspect that you don't need a "slightly" different approach. You probably
need a significantly different one.

Wide tables are clumsy to work with in SAS, but I think in Access the
problems will be much more severe. I would think that the occasion of a
port from one platform to another provides the opportunity to reshape and
streamline. You will probably find it easier to do this before porting,
rather than after.

On Tue, 24 May 2005 09:26:21 -0800, David Neal <afdbn@UAA.ALASKA.EDU> wrote:

>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 I port data to external databases, but
I'm
>> an analyst, not a programmer, and I can hear several mavens groaning in
the
>> distance as I write this. ;-)
>>
>> Good luck and hth -
>>
>> Paul Choate
>> DDS Data Extraction
>> (916) 654-2160
>>
>>
>> On Mon, 23 May 2005 14:01:18 -0800, David Neal <afdbn@UAA.ALASKA.EDU>
wrote:
>>
>>
>>>I'm trying to move several SAS datasets to a single MS Access database.
>>>When I use proc copy to move the data, I get the following note:
>>>
>>>
>>>
>>>NOTE: Copying SCFSAS.PT_MISC to SCFFAS.PT_MISC (memtype=DATA).
>>>
>>>NOTE: SAS variable labels, formats, and lengths are not written to DBMS
>>>tables.
>>>
>>>
>>>
>>>My problem is that I'm interested in keeping the variable labels and the
>>>formats as well.  Is it possible for SAS to automatically create
>>
>> the "lookup
>>
>>>tables" from the formats?  Also, is there a way to keep the variable
labels
>>>when I move from SAS to Access?
>>>
>>>
>>>
>>>David Neal
0
afdbn (102)
5/25/2005 1:54:04 AM
comp.soft-sys.sas 142828 articles. 3 followers. Post Follow

0 Replies
436 Views

Similar Articles

[PageSpeed] 11

Reply:

Similar Artilces:

Re: Deleting SAS Data from a SAS DATASET #6
I have been watching this thread today and I just now recall an example that Paul Dorfman gave at a RUG several years ago and no, I don't recall exactly where or when other than it was NESUG or SESUG in the past 5 years. Anyway, Paul had a client who was storing something like the past 12 months of transactions as variables andat the start of each month needed to stick the just ended month on the end and drop the oldest month. Paul used Peek to read the last 11 months of each obs as a single chunk of data, append the current's month, and then used Poke to write the whole new obs as a unit. The time reduction was dramatic. It probably would not work here unless you knew a whole lot about the structure of the data set and it has a very fixed structure but I was so impressed with the solution that I like to mention it whenever it seems even remotely useful. Nat Wooding Environmental Specialist III Dominion, Environmental Biology 4111 Castlewood Rd Richmond, VA 23234 Phone:804-271-5313, Fax: 804-271-2977 CONFIDENTIALITY NOTICE: This electronic message contains information which may be legally confidential and/or privileged and does not in any case represent a firm ENERGY COMMODITY bid or offer relating thereto which binds the sender without an additional express written confirmation to that effect. The information is intended solely for the individual or entity named above and access by anyone else is unauthorized. If you are not the intended recipient, any disclos...

Re: MS Access and SAS datasets
Use PROC FORMAT with the CNTLOUT option to dump your formats. I don't know about the labels. Access has a "Description" field in its metadata structure, so it's reasonable to expect the labels to end up there. On Mon, 23 May 2005 14:01:18 -0800, David Neal <afdbn@UAA.ALASKA.EDU> wrote: >I'm trying to move several SAS datasets to a single MS Access database. >When I use proc copy to move the data, I get the following note: > > > >NOTE: Copying SCFSAS.PT_MISC to SCFFAS.PT_MISC (memtype=DATA). > >NOTE: SAS variable labels, formats, and lengths are not written to DBMS >tables. > > > >My problem is that I'm interested in keeping the variable labels and the >formats as well. Is it possible for SAS to automatically create the "lookup >tables" from the formats? Also, is there a way to keep the variable labels >when I move from SAS to Access? > > > >David Neal ...

Re: Using SAS with large datasets (linking SAS and Access)
I have very little experience with using this but I have found that if you have SAS Access for PC Products licensed, just as you can issue a libname statement pointing to an Excel workbook, you can also issue one for an Access database. Try doing this and then look at your SAS explorer window where you will see an icon for the Access file and will then be able to look at the Access tables as if they were SAS data sets. Nat Wooding Environmental Specialist III Dominion, Environmental Biology 4111 Castlewood Rd Richmond, VA 23234 Phone:804-271-5313, Fax: 804-271-2977 ben.powell@CLA.CO .UK Sent by: "SAS(r) To Discussion" SAS-L@LISTSERV.UGA.EDU <SAS-L@LISTSERV.U cc GA.EDU> Subject Re: Using SAS with large datasets 06/23/2008 06:01 AM Please respond to ben.powell@CLA.CO .UK If you haven't already, export the data out of Access asap as mdb operations tend to be several orders of magnitude slower than SAS. Export to tab or csv and import into SAS. The export procedure from Access is relatively painless. If you've already done that and are still facing problems you should provide more info on variabl...

Re: MS Access and SAS datasets #8
Does SQL Server Express have a lot of proprietary extensions to the SQL language which provide array processing and the like? If not, the column capacity may be more a curse than a blessing. I don't know the specifics of David's project, but based on experience I suspect that a lot of normalization is possible, and that if it is done, even the capacity of Access will be far more than adequate. On Tue, 24 May 2005 20:07:03 -0600, Alan Churchill <SASL001@SAVIAN.NET> wrote: >David, > >I believe SQL Server Express (which is free) can handle 1024 columns. You >may want to consider that approach if you have to move it anyway. > >Thanks, >Alan > >Savian >"Bridging SAS and Microsoft Technologies" > >-----Original Message----- >From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of David >Neal >Sent: Tuesday, May 24, 2005 6:54 PM >To: SAS-L@LISTSERV.UGA.EDU >Subject: Re: MS Access and SAS datasets > >You are right, the "slightly" was an understatement on my part.(I guess I >was being nice.) >I believe ACCESS is limited to 256 (or 255 I forget which) columns so I am >required to do a bit of reshaping anyway. I will be working with (reshaping) >the data in SAS and then move it into ACCESS. I feel much more comfortable >tweaking it in SAS and then moving it to ACCESS. > >David > >-----Original Message----- >From: SAS(r) Discussion [mailto:SAS-L@LISTS...

Re: MS Access and SAS datasets #4
MS Access also has a CAPTION field that behaves more like a SAS variable label. However, I have not found a way to write to that field from SAS. Ed Edward Heaton, SAS Senior Systems Analyst, Westat (An Employee-Owned Research Corporation), 1600 Research Boulevard, RW-3541, Rockville, MD 20850-3195 Voice: (301) 610-4818 Fax: (301) 610-5128 mailto:EdHeaton@Westat.com http://www.Westat.com -----Original Message----- From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Howard Schreier <hs AT dc-sug DOT org> Sent: Tuesday, May 24, 2005 11:21 AM To: SAS-L@LISTSERV.UGA.EDU Subject: Re: MS Access and SAS datasets Use PROC FORMAT with the CNTLOUT option to dump your formats. I don't know about the labels. Access has a "Description" field in its metadata structure, so it's reasonable to expect the labels to end up there. On Mon, 23 May 2005 14:01:18 -0800, David Neal <afdbn@UAA.ALASKA.EDU> wrote: >I'm trying to move several SAS datasets to a single MS Access database. >When I use proc copy to move the data, I get the following note: > > > >NOTE: Copying SCFSAS.PT_MISC to SCFFAS.PT_MISC (memtype=DATA). > >NOTE: SAS variable labels, formats, and lengths are not written to DBMS >tables. > > > >My problem is that I'm interested in keeping the variable labels and >the formats as well. Is it possible for SAS to automatically create the "lookup >ta...

Re: MS Access and SAS datasets #2
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 I port data to external databases, but I'm an analyst, not a programmer, and I can hear several mavens groaning in the distance as I write this. ;-) Good luck and hth - Paul Choate DDS Data Extraction (916) 654-2160 On Mon, 23 May 2005 14:01:18 -0800, David Neal <afdbn@UAA.ALASKA.EDU> wrote: >I'm trying to move several SAS datasets to a single MS Access database. >When I use proc copy to move the data, I get the following note: > > > >NOTE: Copying SCFSAS.PT_MISC to SCFFAS.PT_MISC (memtype=DATA). > >NOTE: SAS variable labels, formats, and lengths are not written to DBMS >tables. > > > >My problem is that I'm interested in keeping the variable labels and the >formats as well. Is it possible for SAS to automatic...

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: MS Access and SAS datasets #5
I suspect that you don't need a "slightly" different approach. You probably need a significantly different one. Wide tables are clumsy to work with in SAS, but I think in Access the problems will be much more severe. I would think that the occasion of a port from one platform to another provides the opportunity to reshape and streamline. You will probably find it easier to do this before porting, rather than after. On Tue, 24 May 2005 09:26:21 -0800, David Neal <afdbn@UAA.ALASKA.EDU> wrote: >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'...

Re: MS Access and SAS datasets #7
David, I believe SQL Server Express (which is free) can handle 1024 columns. You may want to consider that approach if you have to move it anyway. Thanks, Alan Savian "Bridging SAS and Microsoft Technologies" -----Original Message----- From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of David Neal Sent: Tuesday, May 24, 2005 6:54 PM To: SAS-L@LISTSERV.UGA.EDU Subject: Re: MS Access and SAS datasets You are right, the "slightly" was an understatement on my part.(I guess I was being nice.) I believe ACCESS is limited to 256 (or 255 I forget which) columns so I am required to do a bit of reshaping anyway. I will be working with(reshaping) the data in SAS and then move it into ACCESS. I feel much more comfortable tweaking it in SAS and then moving it to ACCESS. David -----Original Message----- From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Howard Schreier <hs AT dc-sug DOT org> Sent: Tuesday, May 24, 2005 5:26 PM To: SAS-L@LISTSERV.UGA.EDU Subject: Re: MS Access and SAS datasets I suspect that you don't need a "slightly" different approach. You probably need a significantly different one. Wide tables are clumsy to work with in SAS, but I think in Access the problems will be much more severe. I would think that the occasion of a port from one platform to another provides the opportunity to reshape and streamline. You will probably find it easier to do this before porting, rather than after. ...

Re: MS Access and SAS datasets #9
Howard, I completely agree on fixing the normalization issue. Oftentimes though it is something that has to be done later. SQL Server Express has the .NET CLR built-in which means that you can use most .NET code in the SQL expression. Regardless of whether this is a proprietary extension (which it is...a LOT), Access is a less than optimal database. However, it will probably work as you suggest with some work on normalizing the structure. Thanks, Alan Savian "Bridging SAS and Microsoft Technologies" -----Original Message----- From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Howard Schreier <hs AT dc-sug DOT org> Sent: Tuesday, May 24, 2005 8:48 PM To: SAS-L@LISTSERV.UGA.EDU Subject: Re: MS Access and SAS datasets Does SQL Server Express have a lot of proprietary extensions to the SQL language which provide array processing and the like? If not, the column capacity may be more a curse than a blessing. I don't know the specifics of David's project, but based on experience I suspect that a lot of normalization is possible, and that if it is done, even the capacity of Access will be far more than adequate. On Tue, 24 May 2005 20:07:03 -0600, Alan Churchill <SASL001@SAVIAN.NET> wrote: >David, > >I believe SQL Server Express (which is free) can handle 1024 columns. You >may want to consider that approach if you have to move it anyway. > >Thanks, >Alan > >Savian >"Bridging SAS and Microsoft...

Re: SAS/ACCESS
Does the DBMAX_TEXT option do what you want? http://support.sas.com/onlinedoc/913/getDoc/en/acreldb.hlp/a003113591.htm -- Jack Hamilton jfh@alumni.stanford.org Videtis illam spirare libertatis auram On Jan 22, 2009, at 8:57 am, Matthew Pettis wrote: > Hi, > > > > I have a CLOB coming back from an Oracle passthrough query that is > >1024 > in length. How do I specify that my receiving variable in a SAS > dataset > be longer than 1024 characters to accommodate this? By default, I get > SAS thinking that this is 1024 characters, when it is really a CLOB > and > I want to set some large default length on this variable length... > > > > Thanks, > Matt ...

Re: SAS/ACCESS
DBMAX_TEXT does the trick; thank you all who helped! Matt -----Original Message----- From: Jack Hamilton [mailto:jfh@stanfordalumni.org] Sent: Sat 1/24/2009 1:42 AM To: Pettis, Matthew (Prof II&RS) Cc: SAS-L@LISTSERV.UGA.EDU Subject: Re: [SAS-L] SAS/ACCESS - Oracle: setting field lengths in SAS datasets returned from passthrough Does the DBMAX_TEXT option do what you want? http://support.sas.com/onlinedoc/913/getDoc/en/acreldb.hlp/a003113591.htm -- Jack Hamilton jfh@alumni.stanford.org Videtis illam spirare libertatis auram On Jan 22, 2009, at 8:57 am, Matthew Pettis wrote: > Hi, > > > > I have a CLOB coming back from an Oracle passthrough query that is > >1024 > in length. How do I specify that my receiving variable in a SAS > dataset > be longer than 1024 characters to accommodate this? By default, I get > SAS thinking that this is 1024 characters, when it is really a CLOB > and > I want to set some large default length on this variable length... > > > > Thanks, > Matt ...

Re: combine n SAS datasets in to one SAS dataset.
If you use the dictionary tables try Proc SQL - it will run much more quickly than a datastep: %let lib=YourLib; %let mem=file2007; proc sql noprint; select MemName into :MemList separated by " &lib.." from dictionary.tables where LibName=upcase("&lib") and MemType='DATA' and MemName eqt upcase("&mem"); quit; data &lib.File_History; set &lib.&MemList; run; hth Paul Choate DDS Data Extraction (916) 654-2160 -----Original Message----- From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of stulkem@YAHOO.COM Sent: Thursday, January 17, 2008 12:06 PM To: SAS-L@LISTSERV.UGA.EDU Subject: combine n SAS datasets in to one SAS dataset. Thanks for your help in advance! I have hundreds of SAS datasets that I want to combine into one SAS dataset. For example, I want to set file20070101.sas7bdat - file20071231.sas7bdat (365 total files) into file_history.sas7bdat. How can I write this into a macro or array or whatever will work so I don't have to write each individual file name?? Thanks again! Mark ...

Re: from SAS to MS Access without Proc Export #6
To do it with SAS Base software alone, and without an intermediate file, try DDE. 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 ...

Re: SAS Advanced Programming Exam for SAS 9: SAS Joke of the year. #6
I interviewed at a company that gave a test where I had a half hour to produce a very simple report using PROC REPORT. I was provided all the manuals I wanted, but because I had used PROC REPORT before, I wanted none of them. I finished in five minutes, and most people on SAS-L would be able to beat that time. Bob Abelson HGSI 240 314 4400 x1374 bob_abelson@hgsi.com "toby dunn" <tobydunn@HOTMAIL.COM> Sent by: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU> 09/04/2007 05:15 PM Please respond to "toby dunn" <tobydunn@HOTMAIL.COM> To SAS-L@LISTSERV.UGA.EDU cc Subject Re: SAS Advanced Programming Exam for SAS 9: SAS Joke of the year. Ed , I still contend and stick with they should both be able to program and know how SAS works. I prefer the intervewing company give a test, were the person being interviewed is sat in front a laptop or desktop and told to write code to solve some problems. No online help no books just the persona nd the computer. This weeds out those who can code and those who cant, from those who can you then talk to them about the code they wrote and you can deduce those who understand how SAS works and thos who dont. The pool you are left with are the qualified candidates atleast from a SAS perspective and you can make your choice from there. Toby Dunn Comprimise is like telling a lie, it gets easier and easier. Each comprimise you make, that becomes your standard. Perfection doesnt exist, once...

Re: combine n SAS datasets in to one SAS dataset. #2
On Thu, 17 Jan 2008 12:06:01 -0800, stulkem@YAHOO.COM wrote: >Thanks for your help in advance! > >I have hundreds of SAS datasets that I want to combine into one SAS >dataset. > >For example, I want to set file20070101.sas7bdat - >file20071231.sas7bdat (365 total files) into >file_history.sas7bdat. > >How can I write this into a macro or array or whatever will work so I >don't have to write each individual file name?? > >Thanks again! > >Mark You must provide some rule or process which SAS can use to discover the names of the data sets to be processed. Here is how I might deal with the example (one file for each day of a calendar year): data _null_; call execute('proc datasets library=mylib nolist;'); call execute('delete file_history;'); call execute('run;'); do day = '01jan2007'd to '31dec2007'd; call execute('append base=mylib.file_history data=mylib.file' || put(day,yymmddn8.) || ';'); end; call execute('quit;'); run; ...

Re: combine n SAS datasets in to one SAS dataset. #4
<html> <body> <br> Filename allfiles ('d:\conv\2008*.asc');&nbsp; * Filenames can use wildcards;<br><br> infile allfiles;<br><br> <br><br> <br> At 10:53 AM 1/14/2009, b.djaby@gmail.com wrote:<br> <blockquote type=cite class=cite cite="">Hi,<br> I have a similar problem in my SAS macro;<br> I have many ascii files where name are&nbsp; 2008011.asc to 2008123<br> &nbsp;year = 2008, month=01 to 12 and dekad = 1 to 3;<br> To import all my files, I have writed 2 similars&nbsp; macro contain with<br> those code:<br> %LET month=9;<br> %LET dekad=3;<br> <x-tab>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</x-tab>%Do i= 1 %to &amp;month;<br> <x-tab>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</x-tab>%DO j = 1 %to &amp;dekad;<br> %let dataname =&quot;d:\conv\20080&amp;i&amp;j.asc&quot;;<br> infile &amp;dataname;<br> ****<br> and for second<br><br> %LET month=12;<br> %LET dekad=3;<br> <x-tab>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</x-tab>%Do i= 10 %to &amp;month;<br> <x-tab>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</x-tab>%DO j = 1 %to &amp;dekad;...

Re: combine n SAS datasets in to one SAS dataset. #3
Here's another option using julian dates: %macro julday(year); %do i=1 %to 365; data _null_; day=datejul(&year*1000+&i); date=put(day,yymmddn8.); call symput("yymmdd",date); put day= date=; run; %put yymmdd=&yymmdd; proc append base=file_history new=file&yymmdd.;run; %end; %mend; %julday(2007); At 11:44 AM 1/18/2008, Howard Schreier <hs AT dc-sug DOT org> wrote: >On Thu, 17 Jan 2008 12:06:01 -0800, stulkem@YAHOO.COM wrote: > > >Thanks for your help in advance! > > > >I have hundreds of SAS datasets that I want to combine into one SAS > >dataset. > > > >For example, I want to set file20070101.sas7bdat - > >file20071231.sas7bdat (365 total files) into > >file_history.sas7bdat. > > > >How can I write this into a macro or array or whatever will work so I > >don't have to write each individual file name?? > > > >Thanks again! > > > >Mark > >You must provide some rule or process which SAS can use to discover the >names of the data sets to be processed. > >Here is how I might deal with the example (one file for each day of a >calendar year): > > data _null_; > call execute('proc datasets library=mylib nolist;'); > call execute('delete file_history;'); > call execute('run;'); > do day = '01jan2007'd to '31dec2007'd; > call execute('ap...

Re: SAS/access for Oracle and MS SQL server are different license? #6
Given that you have SAS/Access to Oracle, how about buying Oracle :-) Seriously, it is not that expensive anymore. The other option I suppose would be to export SQL Server files as Excel spreadsheets or tab-delimited files, then run your SAS from those, but it would be tricky to do that. One trick might be to see if you could do a data link into an Excel spreadsheet to make that your front-end to the SQL tables that you want to use, then use the Excel methods of importing to SAS. -Mary ----- Original Message ----- From: "Ya Huang" <ya.huang@AMYLIN.COM> To: <SAS-L@LISTSERV.UGA.EDU> Sent: Tuesday, April 28, 2009 3:00 PM Subject: Re: SAS/access for Oracle and MS SQL server are different license? > 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 ...

SAS 6 dataset on MVS to SAS 9.1 dataset on Windows 2003
Hi! All members, I'm quite new to SAS. I created a sas dataset using SAS 6 on MVS and FTP to Windows environment. The physicall file name is LossNot (no file extention). When I tried to bring in this dataset using SAS 9.1 on Windows 2003 Server, I got the error: NOTE: Input library MYXPORT2 is sequential. ERROR: File MYXPORT2.ALL. is not a SAS data set. Below are the codes on MVS SAS 6, Windows SAS 9.1 and the error message. Please advise!!! Thanks!! ==== Codes on MVS SAS 6 ============================================= //MyIDA JOB (318,ILS,999,999,,RJE),abc,CLASS=C, ...

Re: SAS System Viewer 9.1 unable to open SAS datasets ( SAS 9.1.3)
A question in return: is your table from windows? compressed from another system? and specially with personnel formats hard coded inside? Andre msiddu2000 a �crit : > SAS System Viewer 9.1 was able to open datasets that were created by > V8 engine (SAS 8.2), but is unable to open datasets that are created > with V9 engine (SAS 9.1.3). > > Does this need SAS System Viewer to be upgraded ? > or > Something else needs to be done ? > > -- Andr� WIELKI INED (Institut National d'Etudes D�mographiques) Service Informatique 133 Boulevard Davout 75980 Paris Cedex 20 m�l : wielki@ined.fr t�l : 33 (0) 1 56 06 21 54 ...

Re: How to access automatically in SAS a MS Access table name
i am not sure if SAS will support long file name if you use libname. a workaround is to use passthrough. On Jan 16, 2008 12:31 PM, JKamgang@Yahoo.com <JBKamgang@gmail.com> wrote: > 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*; > > - - - The log - - > > 1636 > > 1637 /* Creating the Pediatric HIV result information additional */ > > 1638 > > 1639 data pedResultadd ; > > 1640 set dbsource.'tblInfantHIVTestResultsAdditional...

Re: Deleting SAS Data from a SAS DATASET
One thing you might do is to add an index on the snap_dt to the dataset; = if that's there then you should be able to delete the records in place: proc sql; delete from prod.master_date; where snap_dt =3D "&end_dt"d; quit; run; In both the ways you are trying now you are creating new data sets = rather than deleting records from the current data set; it would seem to = me that a SQL delete statement would be faster than creating new = datasets even if there isn't an index on the date.=20 -Mary ----- Original Message -----=20 From: SUBSCRIBE SAS-L Chandra Gadde=20 To: SAS-L@LISTSERV.UGA.EDU=20 Sent: Friday, August 15, 2008 12:14 PM Subject: Deleting SAS Data from a SAS DATASET Hi All I have several SAS datasets that are very very big. (50GB of size). = Every month, the data is being appended to these datasets. I need to deleted = the data which is greater than 24 months. What is the best method to do = this? Please help me. I tried PROC SQS and DATA STEP. But these two are taking very long = time. Data prod.Master_data; set prod.master_date; if snap_dt =3D "&end_dt"d then delete; run; proc sql; crete table prod.master_date from prod.master_date where snap_dt ne "&end_dt"d; quit; Hi You've got good ideas how to delete the old observations. My modify proposal was wrong. Sorry about that! I still think that changing the data model would be a good idea. Regards Patrick ...

Re: How to read a SAS log into a SAS dataset.
lazybone2k@GMAIL.COM wrote: > >Dear SAS-L listers, >This may be the millionth time somebody is asking this question " how to >read a SAS log into a SAS dataset?". I looked at previous posts and >gathered info from various SUGI papers and found a way to get the log and >read it. But i dont know for some reason its not working. Please help me. >I used a Proc printto procedure to print the log to a external file >location and try to read in the log and create a SAS dataset which will >contain info about Filename, Owner name, Group name, Elapsed time, CPU >time, Dataset name. >***************Sample log file******************************** > data Purchmeta_US (keep=Name Start Length Description); >SYMBOLGEN: Macro variable METAPATH resolves >to /export/home/anjapara/838594.meta >41 infile "&metapath" ; >42 input @ 'Field name="' name1 :$17. @; >43 a=index(name1,'" '); >44 name=substr(name1,1,a-1); >45 input @'col="' Start :$3. @ 'len="' length :$1.@; >46 input @'<Description>' Desc &$100.; >47 i=index(Desc,'</'); >48 description=substr(Desc,1,i-1); >49 run; > >NOTE: The infile "/export/home/anjapara/838594.meta" is: > File Name=/export/home/anjapara/838594.meta, > Owner Name=anjapara,Group Name=rg...