Simple SAS Questions

[ADoubleDot]

proc sort data = table1;
	by pol iteration;
run;

data table2;
	set table1;
	by pol iteration;
	if last.pol then output;
run;

[BassFreq]

Adoubledot's got the right answer. Another way is

proc sql;
create table table2 as
select *
from table 1
group by pol
having iteration=max(iteration);
quit;

but with 1M+ rows, this will be really inefficient.

[Gareth Keenan]

Say I have a source body of data.



And I have a SAS query, where I am interested in all records that have "Lunch" as the meal, but I'm only interested in Lunches that are at or equal to Indicator 4, is there a way to do this in SAS and minimize CPU cycles or whatever?

Specifically, I want my oracle search to not even worry about lunches, until it gets to an indicator with a value of 4 then to worry about lunches. I also know that my source data is sorted by indicator, so that the values of the indicator are from smallest to largest.

Is there a way to do this? In the very large data set I am looking at so if I could get the query to start only at the precise indicator, the query would take less time to execute.

I have this statement executing in an elementary data step, but if there is a more efficient way to do this, I would love to hear it. Thanks!

Gareth Keenan

[ADoubleDot]

Try creating indexes on your data. The index tells SAS where the unique values of the data exist on the table. This means that it only needs to search the correct partition.

proc datasets library = library_name;
	modify table_name;
	index create indicator;
	index create lunch;
run;

data table_name2;
	set table_name;
	where indicator >= 4 AND meal = 'lunch';
run;

[ZeroUrashima]

Currently reading Learning SAS by Example: A Programmer's Guide and doing the questions...

I am doing Chapter 21's Question 10 and I wrote the thing that way:

data SALES INVENTORY;
input @16 Type 1. @;

if Type=1 then do;
	input @1 Date mmddyy10.
		  @12 Amount 4.;
	output Sales;
end;

else if Type=2 then do;
	input @1 PartNumber $6.
		  @8 Quantity 3.;
	output Inventory;
end;

drop type;

datalines;
10/21/2005  1001
11/15/2005  2001
A13688 250     2
B11112 300     2
01/03/2005 50001
A88778 19      2
;
run;

title "Listing of SALES";
proc print data=Sales;
run;

title "Listing of INVENTORY";
proc print data=inventory;
run;

Question asks to create two SAS data sets in one DATA Step and the result they want only have the columns that have non-missing Data.

Currently, Sales have a column of PartNumber and Quantity and Inventory has a column of Date and Amount. I suppose I could use var in the procedure to get rid of those but I don't think that's what the question wants since they are still in the Data Set but we just don't see them. Anyone can tell me if there is a way to create 2 Data Sets in one Data Step and the columns with missing values are not there?

[Olrich]

I'm sure a lot of people are already aware of them, but I just recently learned about hash tables (as an alternative to sort and merge for joining tables together) and they are making a big difference in the run time of a lot of stuff I use.

They aren't perfect for every situation, but anytime you're merging a relatively small table to a very large dataset (pulling mortality rates into an inforce file or whatever), it dramatically cuts runtime.

The following is a good introduction, there's some other documentation floating around how to some fancier things with hash tables.

http://www2.sas.com/proceedings/forum2007/039-2007.pdf

[Gareth Keenan]

Is there a way in SAS to take the sum of a character field?

Lets say I have a field called "field" in a table in my work library called "table". I've visually confirmed by looking up distinct values that for all of my rows in "field" are values that are numbers.

Is there a way in Sas I could take the some of "field"? Normally, If I wanted to quickly spot check something, I might just use PROC Means, and review the output, but it looks to me like PROC means does not work on character fields.

suggestions?

Thanks,

Gareth Keenan

[BassFreq]

This would do it...

 
data table;
input field $2.;
datalines;
10
21
68
;
run;
proc sql;select sum(input(field,best32.)) from table;quit;

...but typically I would just convert the character field into numeric using the input funtion and then you could use proc means on that.

[BassFreq]

Actually, not convert the character field, but rather, create a numeric version of it.

[Gareth Keenan]

Is there a logic for when to use if vs else if vs else ?

I'm creating a new variable in a data step based on another variable I all ready have. I feel like you should end with else, which is like saying, "if none of the above criteria matched, just do whatever the else tells you to do."

I also imagine, you have to begin with an if, otherwise beginning with an else if doesn't make logical sense. It probably doesn't make syntax sense, but if I understood this situation, I would not be posting here for help.

when after your first "if" do you use "if" or "if else" or "else"

Cheers,

Gareth Keenan