topic Re: Query Dataset (to Excel?) in SAS Procedures

Query Dataset (to Excel?)

Tegan — Tue, 08 Apr 2014 23:02:02 GMT

I have a dataset ("Dataset 1") with multiple variables.

Key VAR2 VAR3

1 abc xyz

2 123 456

3 def ghi

I would like to devise a simple query to select rows which contain specified content and export them into an excel file to be named "Dataset 2".

For instance, "put all rows into an excel file where VAR2 contains 'abc' and VAR3 contains '456'" or "put all rows into an excel file where VAR2 contains 'abc' OR VAR3 contains '456'"

A) What is the script for this query on Dataset1? and B) Can it be exported as an Excel file or can it only be a sas7bdat? Can it be HTML where I can drag it to Excel?

Re: Query Dataset (to Excel?)

ballardw — Tue, 08 Apr 2014 23:18:13 GMT

There's probably several dozen ways to accomplish this depending on what you want in the end.

One way, Proc export if you have PC Files module licensed with dataset options to select the records you want

Proc export data=dataset1 (where=(var2='abc' and var3='456'))

dbmx=excel file="c:\your path\data.xls";

run;

If you have more complex selection criteria other than equals or in a list you may need more programming in a data step or Proc sql to create the desired data or a procedure that allows more complex Where statements than the data set options allow.

If you want to control order of variables as they appear in the output you may want to use an ODS destination that excel can read and a procedure such as proc print;

But before moving data to Excel I would ask, "What is going to be done in Excel" as often I can do that in SAS and provide the output instead of exporting data and then going through Excel formulae, cell copying/pasting/ formatting to create a pivot table whose results are charted.

Re: Query Dataset (to Excel?)

Tegan — Tue, 08 Apr 2014 23:24:42 GMT

Thanks, Ballardw.

Can I substitute the "and" with an "or" in "Proc export data=dataset1 (where=(var2='abc' and var3='456'))"

And how can I switch the equals to a contains? If the row says "this is a dog" I want to query "contains 'dog'"

The excel file is, you're exactly right, to copy/paste, pivot, chart, etc. As much as I would like to learn SAS scripting enough to do all of that in the program, I'm not the one who decides this. Practice practice.

Re: Query Dataset (to Excel?)

ballardw — Thu, 10 Apr 2014 17:31:02 GMT

All of the logical operators And, Or, Not and In are supported in the data set option where clause.

However the data set option where clause does not support searching functions. To subset the data

where you need a string to be within another string you will need to use a data step or proc sql to create the desired output.

There are a number of string searching functions depending on what type of string value you want to search for. FIND is one of the generic but FINDW, INDEX, INDEXW.

data want;

set have;

if find(var2,'abc')>0 and find(var3,'456')>0; /* this is a subsetting if, all records kept must meet this requirement*/;

/* if you have multiple requirements and sticking them all together with a bunch of or statments gets too

long you might try:

if find(var2,'abc')>0 and find(var3,'456')>0 then output;

if find(var2,'pdq')>0 and find(var3,'789')>0 then output;

though this approach has a possibility of creating duplicate records*/

run;