Help using Base SAS procedures

Query Dataset (to Excel?)

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 14
Accepted Solution

Query Dataset (to Excel?)

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?


Accepted Solutions
Solution
‎04-08-2014 07:18 PM
Super User
Posts: 11,343

Re: Query Dataset (to Excel?)

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.

View solution in original post


All Replies
Solution
‎04-08-2014 07:18 PM
Super User
Posts: 11,343

Re: Query Dataset (to Excel?)

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.

Occasional Contributor
Posts: 14

Re: Query Dataset (to Excel?)

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.

Super User
Posts: 11,343

Re: Query Dataset (to Excel?)

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;

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 659 views
  • 0 likes
  • 2 in conversation