peatjohnston Tracker

Merge or append?

peatjohnston — Wed, 12 Aug 2015 15:34:21 GMT

Hi, I have a simple question, I think, but have not found an elegant way to implement it.

I have datasetA with 70 rows and a number of variables. I used PROC UNIVARIATE to find percentiles for some of these variables and OUTPUT these percentiles into a new datasetB,which only has the percentiles (one row of data). I want to merge these so that I have the percentile values represented for every row in datasetA so that I can write some simple equations in a new data step. However, when I try to merge these it only joins the percentiles of datasetB to the first row of datasetA. Is there an easy way to do this?

Thanks in advance,

peat

Re: Best-subset instead of stepwise

peatjohnston — Mon, 15 Jul 2013 20:05:41 GMT

Thanks Duke, I will look into it.

peat

Re: Best-subset instead of stepwise

peatjohnston — Mon, 15 Jul 2013 11:29:15 GMT

Hi PG, and thanks for the response. I actually have 4 groups (sometimes more). It looks like I can just use:

proc logistic data=training;

class group;

model group= VAR1 -- VAR25 / selection=score best=3 stop=5;

run;

This is very helpful. However, is there a way to compare the output models for overfitting? e.g. are four preditors really better than three.

Cheers,

peat

Best-subset instead of stepwise

peatjohnston — Mon, 15 Jul 2013 00:56:33 GMT

Best-subset instead of stepwise question.

Hello, I have classes of individuals grouped together from cluster analysis. I want to use discriminant analysis to determine group membership of new individuals based on a set of predictors. Normally, I use PROC STEPDISC to find a subset of predictors that go into the discriminant analysis, something like:

proc stepdisc data=training sle=0.05 singular=0.1;

class group;

var VAR1--VAR25

run;

However, recent literature indicates stepwise selection is not as good as evaluating all possible subsets of predictors. Is there a procedure, or otherwise, that can do this? I have looked at PHREG REG and LOGISTIC procedures, but they all seem to be based on numerical data rather than classes. Have I missed something? or should I just convert the group data from text to numerical?

Thanks in advance.

peat

Re: Mistake with the distance procedure

peatjohnston — Sun, 15 Apr 2012 17:03:43 GMT

Thanks for the insights PG. I ran the test program with both raw and log10 transformed and ended in the same resut:

Obs transform location _1 _2 _3 _4 _5

1 Floor(log10) 1 0.00000 . . . .

2 Floor(log10) 2 0.16667 0.00000 . . .

3 Floor(log10) 3 0.16667 0.33333 0.00000 . .

4 Floor(log10) 4 0.00000 0.16667 0.16667 0.00000 .

5 Floor(log10) 5 0.16667 0.00000 0.33333 0.16667 0

6 Log10 1 0.00000 . . . .

7 Log10 2 0.50000 0.00000 . . .

8 Log10 3 0.50000 0.50000 0.00000 . .

9 Log10 4 0.50000 0.50000 0.50000 0.00000 .

10 Log10 5 0.50000 0.50000 0.50000 0.50000 0

This may very well be a question for tech support. I have used the distance procedure for other calculations and it appears to be fine. However, any method I try with 'anominal' type data, I have a problem similar to this, with or without transformation prior to running the procedure.

Cheers,

Ely

Mistake with the distance procedure

peatjohnston — Fri, 13 Apr 2012 23:07:38 GMT

Hello all,

I have been trying to produce a Bray-Curtis dissimilarity matrix for cluster analysis, but the output from proc distance does not appear to be giving me accurate values; the dissimilarities are either 0.5 or very close to that. I have calculated the matrix 'by hand' using the formula:

...and the results I get from the hand calculations are the same as produced in the R package vegan.

Can anyone see a mistake with my SAS code or lend any other insight to help me out?

proc distance data=rawdat out=dist method=braycurtis;

var anominal (var1--var22);

id location;

run;

The rawdat are log10 transformed count data, ranging from 0 to 3.4 after transformation.

Thanks in advance.

finding the sum over intervals of time

peatjohnston — Wed, 15 Feb 2012 16:10:18 GMT

Comparison of methods:

The method by Ksharp appears to be more direct as the run time is much faster (0.38 sec compated to the method from PGStats = 4.25 sec) and also transposes easily into the form I am looking for. One thing that I did not intend to discover from this post is that Kshap's method also works if the X1, X2... VALUES are numbers instead of text place holders as I presented in this post. As it turns out I have a separate set of data that I want to perform a similar operation on and then make calculations based on observed numbers to these metrics calculated by proc sql and in this way the data is already without transposing. I just have to add another 'or X is not missing' to the statement for each of the variables (12 in all) which is just fine with me.

Thank you both, this has turned out to be highly educational for me.

Peat

finding the sum over intervals of time

peatjohnston — Tue, 14 Feb 2012 14:13:29 GMT

Hello Ksharp,

I did not run this, but I will look at it later and study it as an alternative as I start learning more about proc sql. My question/comment is with the WHERE statement in the second to last line:

where x1 is not missing or x2 is not missing;

If there is an X1 and X2 ocurring on the same date, would it only select one variable, but not both?

Thanks for the insight.

Peat

finding the sum over intervals of time

peatjohnston — Mon, 13 Feb 2012 23:30:03 GMT

This was most helpful and it looks like I need to start learning the SQL Procedure. I can see that you put three 'tables' together and it appears that proc sql understands the temporal values (WEEK6, etc...). I will read up but there are a few things I would like to follow up on.

In the data step:

input d date9. x1 $ 10-15 x2 $ 16-21 precip 22-30;

- I by pased this portion of the code since my date was already formated as a SAS date. But my question is why did you specify the spacings for each variable, is that style or does proc sql want strict spacing at times?

In proc sql:

where d between intnx(interval, refDate-1, -1, "SAME") and refDate

- This ended up including an extra day in the summation and included the refDate itself in the summation (which was not something I discussed). I modified the code to:

where d between intnx(interval, refDate, -1, "SAME") and refDate-1

This gave me the exact number of days up to but not including the sample date(WEEK2 = 14 days before the reDate not including the reDate). With the sample date aside, was there a reason for using refDate-1 in the expression? as it appears that this increases the time interval by 1.

Also, adding the ':' at the end of a variable or unwanted character was not a trick I learned. Is there a reference somewhere where I can find more of the basic programming syntax? I am pretty much self taught and things like this make it a lot easier for me. Thanks again, I have learned a lot from your answer.

finding the sum over intervals of time

peatjohnston — Mon, 13 Feb 2012 18:53:12 GMT

Thanks for asking for the clarification.

DATE is a SAS date in DATE9 format. The DATE1 is just a number. Originally I was thinking that I could reference a range based on DATE1. I am willing to format these variables to any format necessary.

Thanks

finding the sum over intervals of time

peatjohnston — Mon, 13 Feb 2012 16:55:23 GMT

Hello all,

I have pulled data from various sorces and merged it into the format below. I want to calculate total precipitation (var=precip) over different time intervals (e.g 2 weeks, 6 weeks, 9 months, etc...) before the reference point displayed by the variables 'X1' and 'X2' (I have more than just these two variables). These reference points are sites that were visited repeatedly for other measurements; the reference points occur several times for each variable several times a year over several years. My original thought was to create a new variable for each metric (2wkprecp, 6wkprecp) and each reference point and use proc means to take sums over those assinged intervals but I think I am heading down the wrong path.

Obs date X1 X2 precip date1

1 02AUG1994 8 19940802

2 03AUG1994 56 19940803

3 04AUG1994 18 19940804

4 05AUG1994 15 19940805

5 06AUG1994 36 19940806

6 07AUG1994 0 19940807

7 08AUG1994 0 19940808

8 09AUG1994 0 19940809

9 10AUG1994 0 19940810

10 11AUG1994 76 19940811

11 12AUG1994 0 19940812

12 13AUG1994 33 19940813

13 14AUG1994 79 19940814

14 15AUG1994 0 19940815

15 16AUG1994 13 19940816

16 17AUG1994 5 19940817

17 18AUG1994 0 19940818

18 19AUG1994 X2 61 19940819

19 20AUG1994 0 19940820

20 21AUG1994 33 19940821

21 22AUG1994 231 19940822

22 23AUG1994 X1 0 19940823

23 24AUG1994 0 19940824

24 25AUG1994 5 19940825

In the end I would like to have a dataset in the form (~ 100 records):

date	refpoint	2wkprecp	6wkprecp	9moprecp
19-Aug-94	X2	336	789	2120
23-Aug-94	X1	531	1053	2201
17-Sep-97	X1	344	718	1938
18-Sep-97	X2	344	718	1905

It should be noted that sometimes X1 and X2 (X3, X4, etc...) are on the same dates.

Any suggestions are welcome.

Thanks in advance.

average of consecutive numbers in a column

peatjohnston — Sun, 06 Feb 2011 16:19:48 GMT

Hello,

I have a set of data given to me by a student who entered the data in the following format:

sub$ d v
a 5 2
a 19 3
a 20 2
a 21 5
a 23 2
a 18 3
a 9 .
b 6 2
b 18 1
b 19 3
b 18 4
b 11 2
b 3 .

I need to take the average of two consecutive numbers in column 'd' and put them in a new column so that I can multiply them by column 'v' for each of the subjects (column 'sub$').
The result of taking the average (ave) should look like this:

sub$ d v ave
a 5 2 12
a 19 3 19.5
a 20 2 20.5
a 21 5 22
a 23 2 20.5
a 18 3 13.5
a 9 . 0
b 6 2 12
b 18 1 18.5
b 19 3 18.5
b 18 4 14.5
b 11 2 7
b 3 . 0

My first thought was to transpose the data (I have other variables that can be used to transpose each row into a column for all subjects), and apply the operations on the newly created variables. But if there is an easier way, I would be interested to learn; I have spent a day looking through online documentation.

Thanks in advance.

Re: power function

peatjohnston — Fri, 09 Jul 2010 22:48:49 GMT

Steve,

I think this might work, but how would I output the coefficients into a new data set? I would be looking at getting the coefficients from hundreds of these measures, then plot the mean and + - 1 standard deviation for them.

Thanks,

peat

Re: SAS to Excel

peatjohnston — Sun, 04 Jul 2010 11:58:50 GMT

Hi Mark,

If you are using pc SAS, you should be able to go to File --> Export Data
and a new window will open up and walk you through the process of creating and naming the Excel file and where to save it.

peat

Re: power function

peatjohnston — Sun, 04 Jul 2010 11:49:27 GMT

Thanks Art,

I am not talking about the power of a test, though I am interested in sample size. These are cumulative species richness curves for which I want to associate a power function in the form:

y=a*x^b

For the original set of numbers I posted, the function is:

y=29.37x^0.358

I want to compare a series of these curves, by looking at the variation of the coefficients with each added sample 'x'. My question is how do I find these coefficients 'a' and 'b' from the given data.

Thanks again,

peat

power function

peatjohnston — Fri, 02 Jul 2010 13:39:52 GMT

Hello all, I am new to this forum.

I am an ecologist who has been using sas for years to do basic statistics and data manipulations. I am interested in calculating a power function from a set of data in the form:

x y
1 28
2 40
3 45
4 48
5 52
6 54

Is there a way I can do this in proc reg? or is there a different procedure to calculate this? I have spend many hours wading through online documentation.

Thanks in advance.
peat

Re: which procedure can help me here?

peatjohnston — Wed, 30 Jun 2010 11:24:39 GMT

Hello,

My coding is crude, but this is how I would address your question:

data orig;
input ID date brand;
datalines;
1 1 1
1 1 2
1 1 3
1 2 1
2 1 1
2 1 2
2 2 1
2 2 1
3 1 1
3 2 2
3 3 1
4 1 1
4 2 2
4 2 1
;
proc sort data=orig out=new1 nodup;
by id date brand;
run;
proc means data=new1 noprint;
by ID date;
var brand;
output out=new n=brands;
run;
data joined; merge orig new;
by ID date;
run;
proc print data=joined;
run;

you can use the nodup statement in proc sort to strip out duplicate records by data and id and a new data set (new1) is created. The means procedure is then used to count the unique brands by id and date and creates another data set (new) with the variable 'brands' representing the # of different brands that were purchased by id and date. Then you merge the original data set with the one created in the means procedure.

There may be an easier way to do this, but this is a method I have been using for years. If someone knows a more elegant way, I would be interested to learn.

peat