BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Tegan
Calcite | Level 5

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?

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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

3 REPLIES 3
ballardw
Super User

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.

Tegan
Calcite | Level 5

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.

ballardw
Super User

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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