Re: How to filter sas data sets into separate sas data sets #3
Instead of trying to merge the two data sets, I would probably try to create
a SAS format from the values of VAR1 in data set 1. Then, NODE1, NODE2 and
NODE3 could be compared against the format for a match.
The example below is a simplified version of what you could do and shows a
printout of how it works. It has 5 observations in data set 1 and only 3
variables in data set 2, but I think the logic should hold for the example
you gave. After the example is code that could be used to actually split
the data as you had requested. Hope this helps.
* create sas data set 1 ;
data ...Re: How to filter sas data sets into separate sas data sets #4
Something like this is the old way. You could use a proc sql if you
have a new enough version. Increase your buffersize and if you have
enough memory you may get it into a hash routine.
MERGE small (IN=A OBS=500) big ;
From: SAS(r) Discussion [mailto:SAS-L@listserv.vt.edu] On Behalf Of
Sent: Monday, April 02, 2007 10:53 AM
Subject: How to filter sas data sets into separate sas data sets
This is my failing point in coding SAS. T...Re: Reading & Writing SAS data sets without SAS #3
You're correct in that a number of companies have done it. I believe SPSS
can do it, WPS, Stat Transfer, dbmscopy, and perhaps others have also done
it. But what I think is unique about this is that Alan is talking about
offering plug-ins so you can roll-your-own so to speak. How cool would it be
to have some type of driver/plugin for R?
SAS & WPS Consulting and WPS Reseller
Tel: (614) 457-3714
From: Chang Chung [mailto:chang_y_chung@HOTMAIL.COM]
Sent: Monday...Re: Exporting a SAS data set to Text file on SAS unix #3
hi ... actually, what I posted earlier was too much code (sorry)
this is enough (a bit more succinct)
* variable names into a macro variable (tab separated);
proc sql noprint;
select name into :vars separated by '09'x
where libname eq 'SASHELP' and memname eq 'CLASS'
file 'z:\class.txt' dsd dlm='09'x ;
if _n_ eq 1 then put "&vars";
put (_all_) (:);
U@Albany School of Public Health
One University Place
Rensselaer, New York 12144-3456
P/518-402...Re: How to filter sas data sets into separate sas data s ets
I think that both Ron's (as he mentioned) and Richard solutions require that
VAR1 is in both datasets.
But from the original post, it seemed to me that VAR1 is only in data set 1,
and it must be matched to 1 of 3 variables in data set 2 (NODE1, NODE2 or
NODE3) to be output to the NEWLIST data set. For this reason, I think a
format is one possible approach.
Maybe the original poster can clarify this point. Thanks.
Center for Health Program Development and Management
University of Maryland, Baltimore County
From: SAS(r) Discussio...Re: search SAS data set from SAS code
> From: Rose
> Hi All,
> Suppose I have a sas permanent data set which was created
> early, I know
> the library path but I couldn't remember in which sas program code I
> created it. how can I search from so many sas program files in
> different folders and find it.
a problem familiar to all of us delayed-housekeeping folks.
Libname Libref '<directory-specification>';
use your system utilities to search for the dir-spec
of your libref.
containing text: <dir-spec>
once you have found the libname...Re: Deleting SAS Data from a SAS DATASET #3
The disadvantages of using SQL delete are (1) NOBS= is no longer
accurate, and (2) POINT= may behave unexpectedly.
If those are not issues, then the delete would be faster, with or
without an index, than recreating the data set (especially if the data
set has indexes that would need to be rebuilt).
On Aug 15, 2008, at 11:17 am, Mary wrote:
> 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 = "&end_dt"d;
> 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.
> ----- Original Message -----
> From: SUBSCRIBE SAS-L Chandra Gadde
> To: SAS-L@LISTSERV.UGA.EDU
> 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).
> 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
> Please help me.
...Re: sas data sets to excel
I like to use Excel Tagsets for this; it will actually write an XML file but
you can open an XML file in either Excel 2003 or 2007. Setting the sheet
name switches sheets.
Here is an example:
Excel Tagsets give you a lot of formatting control; here is an example:
ods listing close;
ods tagsets.excelxp file="c:\temp\file1.xml'
ods tagsets.excelxp options(sheet_name="Sheet1");
proc print label data=set1;
var var1 var2
label var1='Variable 1'
ods tagsets.excelxp options(sheet_name="Sheet2");
proc print label data=set2;
var var1 var2
label var1='Variable 1'
ods tagsets.excelxp close;
You can also change the styles that you use, such as to modify the analysis
style you might do this (you only have to do this once, then it should be
there in your templates folder for you to use).
define style Styles.analysis2;
parent = styles.analysis;
replace fonts /
'TitleFont2' = ("Times New Roman",10pt,Bold)
'TitleFont' = ("Times New Roman",10pt,Bold)
'StrongFont' = ("Times New Roman",10pt,Bold)
'EmphasisFont' = ("Times New Roman",10pt,It...Re: search SAS data set from SAS code #2
The answer to your question depends on your operating system. In Windows,
there's the Search tool. In Unix/Linux, you can use grep
240 314 4400 x1374
Sent by: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
04/19/2005 11:13 AM
Please respond to myr_rose
Subject: search SAS data set from SAS code
Suppose I have a sas permanent data set which was created early, I know
the library path but I couldn't remember in which s...Re: search SAS data set from SAS code #5
You have some good advice on search techniques, but they may beinadequate.
I hope your LIBNAME wasn't something like
libname lib "&dir" ;
Perhaps you should also search for ".member", but that also couldhave the same problem. You might also look for key variablenames or values, or procedures that you know created the data.The date from a PROC CONTENTs might provide useful information,or an old report created by the same program with a footnote,"Source code: ...".
data lib.w ( label="created by ..." ) ;
would be a good habit to ...Re: sas data sets to excel #2
SAS treats datasets as values from all variables in each record (or across
a row of Excel) belong to each other, so to maintain data integrity, if it
is possible the two files should first be merged into one by one or more
common id values:
MERGE a b;
and then run PROC EXPORT on the merged file.
If that isn't the situation, then you should send them to separate
worksheets (one handy option run with ODBC is shown below). If absolutely
necessary, you can "copy" one of them and paste it into the other (since
Excel is particular about such things) but then columns across rows in the
new combined worksheet may not have their respective identiies if you want
to read it back into SAS.
DATA cls; set sashelp.class; RUN;
a=1; b=2; output;
a=2; b=3; output;
LIBNAME _xls odbc noprompt="dsn=Excel Files; dbq=c:\test3.xls";
PROC COPY in=work out=_xls;
SELECT cls new / memtype=data;
LIBNAME _xls clear;
Sent by: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
10/15/2008 08:36 AM
Please respond to
sas data sets to excel
I need to put two different SAS data sets into one weeksheet in a
excel file. I don't know how to put them together and define the title
of the tab. thanks,
...Re: How to convert Excel data to sas #3
In addition to what has already been said, I agree that these columns probably are blank for some number of rows. A simple solution is to insert periods in blank cells in the first row so SAS will see them as missing numeric values.
When you say that you are "importing" the Excel file, are you using Proc Import or are you using the SAS Excel libname engine?
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of jn mao
Sent: Wednesday, February 24, 2010 3:28 PM
Subject: How to convert Excel data to sas
H...Re: sas data sets to excel #4
SaviCells can do this (yes, it is free). You can get it here:
Specifically, look at:
<Data sasLibrary="c:\temp" sasDataset="Class" formatHandling="1"
<Range start="B1" end="B3" name="testRange"></Range>
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Mary
Sent: Wednesday, October 15, 2008 9:55 AM
Subject: Re: sas data sets to excel
Maybe talk your client out of this? Or just do "passive-aggressive" and send
it on two sheets? It is a really, really bad idea to put two different data
sets on the same sheet.
----- Original Message -----
From: Qiang Fu
Sent: Wednesday, October 15, 2008 10:08 AM
Subject: Re: sas data sets to excel
Thanks for your quick reply. I think, however, I didn't express me clearly
in the MSG.
What I want is to export two different SAS data sets into ONE weeksheet
with one tab name instead of two weeksheets with two different tab names.
The example you gave will export the data sets into two different tabs with
names "sheet1" and "sheet2" respectively even they are in same workbook. I
know this task sounds insane, but client needs. Any idea...Re: Importing Excel data to SAS #3
I think "DDE" may be the best way!
...Re: Reading SAS data sets on UNIX by non-SAS apps #2
Following on Richard's thoughtful suggestions, the Affinium system would
likely capture data from csv files. SAS PROC EXPORT produces them quickly,
and loading them into external systems works faster for relatively basic
data structures and data formats, in my experience, than xml parsing.
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of John
Sent: Monday, October 18, 2004 10:10 AM
Subject: Reading SAS data sets on UNIX by non-SAS apps
I have SAS data sets on AIX that we need to read with Unica's Affinium
campaign management software, also on a UNIX box. (Let's not get into why
we didn't go with the SAS Solution.) SAS Institute doesn't supply an ODBC
driver for the UNIX environment, and the Affinium implementors don't want to
use the SAS SQL Library for C and or deal with APIs. Other that dumping the
SAS data sets as flat files, can anyone suggest a solution?
Thanks in advance for the help.
...Re: What r the data types in SAS? in Base SAS , and SAS SQL
> From: Amar Mundankar
> Sent: Tuesday, July 21, 2009 8:10 AM
> To: email@example.com
> Subject: What r the data types in SAS? in Base SAS , and SAS SQL
> Hi all,
> What are the different data types in Base SAS and SAS SQL??
character, lengths from 1 to 32,000+
numeric: lengths from 2 to 8
dates are a subtype of numeric
and are identified by their date, datetime, or time formats
the new proc TSPL (Table Server Processing Language)
supports ANSI data types: bigint, tinyint, etc.
http://s...Re: SAS data-set index size #3
>Is a telling story if the size of the SAS index file (*.sas7bdnx) is
>75% the size
>of the SAS data-set itself?
>For instance, we have a data-set that is 533MB's and the index file
>Is this an indication of a poor data-set structure?
No, it is not an indication of a poor data structure. (You may *have*
a poor data structure, but the relative size of the index file is not
Since Mister Index himself has already chipped in, I'll just add a couple
Think conceptually of the i...Re: Excel to SAS import dropping data? #3
I would urge you to consider using the Excel object model and get the data
that way. In other words, use a push operation (from Excel) vs a pull
operation (from SAS). I'm not sure if it is necessary today based upon what
you have stated but it looks like you are moving towards more complexity,
An alternative to that would be to use the Excel libname engine. In my
opinion, you will never have the flexibility you need with Excel unless you
consider using Excel to move the data.
There are loads of ways to access Excel via Excel engines. Initially, it
will require a learnin...Re: SAS Data Set in Edit Mode #3
Assuming you are trying to edit the SAS table in ViewTable, click on the
column header for the date column you want to edit with your right mouse
button and choose Column Attributes from the resulting drop down list. You
can then change the INFORMAT to one that will accept dates in the form you
want to use.
On 10/13/07, Peter Crawford <firstname.lastname@example.org> wrote:
> On Sat, 13 Oct 2007 11:33:15 -0400, Anil Anand <anilkranand@GMAIL.COM>
> >Hello All,
> >I am trying to Edit a SAS datasetin Edit mode on Linux. W...Re: SAS with data sets of a billion+ records #3
Richard Reeves posted the following:
> I am working with some tables that have between 600 million
> and 1.2 billion records. In the end I will only need about
> 400 million of these records but to identify them I will have
> join several tables together (sorting or indexing each time)
> to get to this point. The files don't have a lot of fields as
> they come from a very normalized transactional system so even
> when everything is joined there will be <50 fields.
> Before I get too far down this road I would like to know if
> anyone re...Re: how to find sas sample data set #3
Thank all for your answers! It works now.
On Tue, Feb 24, 2009 at 5:22 AM, Gerhard Hellriegel <
> Something with "sample" if they are installed...
> It depends on the SAS module. For BASE it is ...\core\sample\
> On Mon, 23 Feb 2009 23:22:48 -0500, help ly <help.ly2005@GMAIL.COM> wrote:
> >Hi, I am trying to find SAS sample data set such as medical data set or
> >hospital data set because it has to be used in those excersize problems in
> >the sas books - Learning SAS by Example. There are supposed to be many
> >those sample data set from SAS itself, right?
> >Can anyone help me what's the directory for the data set?
> >Thanks so much!
Thanks so much!
I am facing the same problem you had before. How did you find the example data? I don't see them in either SASHELP or SASUSER library.
On Tuesday, February 24, 2009 1:29:11 PM UTC-5, help ly wrote:
> Thank all for your answers! It works now.
> On Tue, Feb 24, 2009 at 5:22 AM, Gerhard Hellriegel <
> email@example.com> wrote:
> > Something with "sample" if they are installed...
> > It depends on the SAS module. For BASE it is ...Re: removing labels in SAS data sets #3
Don't use data steps for jobs that just modify meta data. The
contents statements are included to show before and after, they would
not normally be needed.
label x='hey x' y='hey y';
proc datasets nolist;
attrib _all_ label=' ';
On Jan 16, 2008 7:57 AM, Darryl Putnam <firstname.lastname@example.org> wrote:
> On Jan 15, 4:56 pm, Mterje...@RUSSELL.COM ("Terjeson, Mark") wrote:
> > Hi Vincent,
> > Here is one way:
> &g...Re: How to Copy/Paste SAS data values into Excel? #3
A birdie stopped by and offered the following in answer to my question of
whether SAS/Access for PC Files is needed to accomplish Leslie's
suggestion for copying SAS files:
>>On windows open SAS explorer and right click on the desired dataset and
>>select "View in Excel". The data will magically export to an excel
According to the birdie:
"Yes, this will work without SAS/ACCESS for PC Files. It simply uses ODS
to create an HTML file, then opens that HTML file in Excel. The
papers on the right-hand side of the following page provide some...Re: How to export SAS data sets to Excel with multiple sheets
Hey Grace -
In v9 the Excel libname engine allows you to use Excel as a SAS library.
You won't be able to map multiple datasets to one sheet, but several sheets
in one book is a snap, and you get the power of the datastep while you do
Aside from that, ODS HTML is probably the quickest and most platform
independent method of moving data to Excel.
Proc Export is a good bullet-proof method for easy movement of data. If you
like Export, then look at Proc Access, which is Proc Export on steroids.
Here you can map to ranges and other such things.
DDE is great for processing data within ...