topic Re: cross table arithmetic? in SAS Studio

cross table arithmetic?

schlepro — Thu, 23 Jan 2020 04:12:36 GMT

Hi SAS Helpers!

Very basic question for you about using variables in one table with variables in another table. Using SAS university edition. I'm trying to do arithmetic with the mean and standard deviation of a column in order to set another column as a one or zero depending on whether a different column is >= mean() plus std() in order to do some feature engineering, but my code doesn't work as intended. Please assist!

proc import datafile='/folders/myfolders/cross_table_example.xlsx'
	out = new_table
	dbms = xlsx
	replace;

proc sql;
	create table want as
		select mean(Watch)+std(Watch) as OneDev
  		from new_table;
quit;

* Doesn't work as intended. Get column of all ones;
data new_table;
	set new_table want; * sets the row after the last row of data with what's in want?;
	freqWatcher = 1*(Watch>=OneDev);
run;

Thanks for your help ya'll!

Re: cross table arithmetic?

Reeza — Thu, 23 Jan 2020 04:16:04 GMT

SET stacks the two data sets on top of each other. You want a merge of types but it's different because you want the value to merge with all other rows you have.

I show two methods of doing this here:
https://github.com/statgeek/SAS-Tutorials/blob/master/add_average_value_to_dataset.sas

Re: cross table arithmetic?

PGStats — Thu, 23 Jan 2020 04:16:50 GMT

I don't see what is the purpose of table mean_dev. But you seem to want:

proc sql;
	create table want as
		select *, Watch >= mean(Watch)+std(Watch) as freqWatcher
  		from new_table;
quit;

Re: cross table arithmetic?

schlepro — Thu, 23 Jan 2020 05:04:11 GMT

I didn't even think of trying to do it all in SQL. I feel a little sheepish. My bad on mean_dev. I used old junk data to build the post and included more than I needed. I edited the post, but without explanation, so it might have been a little confusing.

Follow Up question. How would you put freqWatcher in new_table using native/base SAS? Is that where merge would come in?

Cheers!

Re: cross table arithmetic?

schlepro — Thu, 23 Jan 2020 05:14:03 GMT

Ah, I just tried:

data new_table;
	merge new_table want;
run;

and got exactly what I was looking for. Cool. Thanks for your help ya'll!

Cheers!

Re: cross table arithmetic?

Reeza — Thu, 23 Jan 2020 17:22:38 GMT

No, that won't work. Check the link I posted to explain how you would need to do it. That would only bring the value to the first row of the data set but if you only have one I guess that's fine.