Date: Mon, 10 Jan 2011 12:58:10 -0600
Reply-To: Bhupinder Farmaha <bhupi80singh@YAHOO.CO.IN>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Bhupinder Farmaha <bhupi80singh@YAHOO.CO.IN>
Subject: Re: Creating subset of data and calculate weighted means for depth
In-Reply-To: <201101101834.p0ABoqKT021819@wasabi.cc.uga.edu>
Content-Type: text/plain; charset="us-ascii"
Art,
I have four different datasets one pre-(start of 2007 or Spring07) and three
post-(end of 2007, 2008, and 2009 or Fall07, Fall08, and Fall09). In my
first analysis, I just want to concentrate only on Spring07 dataset.
Therefore want to somehow extract those values out of whole file and run
Proc MIXED. In this analysis, I would consider all the depths and see how
soil variables were different at each depth. In the same analysis, I am also
interested to know if and how soil variable differed for composite depth
0-18 and 18-50 cm.
In the second analysis I will repeat the first part (looking at all the
depths individually) as well as second part (composite depths) of the first
analysis. But would add another part to see if corresponding individual
depths differed in soil nutrients over the time (Spring07 vs Fall07,
Spring07 vs Fall08, Spring07 vs Fall09 and Fall07 vs Fall09) as well as for
composite depths (0-18 and 18-50).
You would also notice that for pre-treatment (spring07) data I have listed
"loc" as "." but for Fall analysis I have data from two locations "IR and
BR".
I have tried to use your code but it gave following error-
phos_weightd_40=(5*Phos1+5*Phos2+10*Phos3+20*Phos4)/40;
290 phos_weightd_20=(5*Phos1+5*Phos2+5*Phos3+5*Phos4)/20;
291 output;
292 end;
293 run;
NOTE: Character values have been converted to numeric values at the places
given by:
(Line):(Column).
284:8 285:9 286:8 287:8
NOTE: Invalid numeric data, depth='10-20', at line 284 column 8.
ERROR: Array subscript out of range at line 284 column 3.
I want to highlight one more point here that can be source of error. In my
first email I have considered
Phos1 refer to Phos values of depth 0-5,
Phos2 refer to Phos values of depth 5-10
Phos3 refer to Phos values of depth 10-18,
Phos4 refer to Phos values of depth 18-50,
But in the data itself, there is no any variable like phos1, phos2, phos3,
and phos 4. My purpose to write phos1, phos2, phos3, and phos 4 here is just
to mention that in the weighted calculations soil phos correspond to 0-5
should be multiplied by 5 and others depths as mentioned.
Thanks
Bhupinder
-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Arthur
Tabachneck
Sent: Monday, January 10, 2011 12:34 PM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Re: Creating subset of data and calculate weighted means for depth
Bhupinder,
Like Nat, I didn't look at your problem regarding your actual analysis.
However, since you basically have pre and post data for each of four depths
(that are coded as ranges in a character variable), but basically want 2
records from them (one pre and one post), I would suggest something like the
following (of course, absolutely no promises that I understood everything
correctly):
*recode depths;
proc format;
invalue $depths
'0-5'=1
'5-10'=2
'10-18'=3
'18-50'=4
;
run;
data soils (drop=_:);
set soils (rename=(depth=_depth));
depth=input(_depth,$depths.);
run;
* transpose to wide;
proc sort data=soils;
by year field crop sampling_stage plot
block tillplc prate krate fert loc
depth;
run;
data want (drop=in_:);
set soils (rename=(phos=in_phos
potas=in_potas
calc=in_calc
magn=in_magn)
where=(upcase(crop) ne 'CORN'));
array phos(4);
array potas(4);
array calc(4);
array magn(4);
retain phos potas calc magn;
by year field crop sampling_stage plot
block tillplc prate krate fert loc
;
phos(depth)=in_phos;
potas(depth)=in_potas;
calc(depth)=in_calc;
magn(depth)=in_magn;
if last.loc then do;
phos_weightd_40=(5*Phos1+5*Phos2+10*Phos3+20*Phos4)/40;
phos_weightd_20=(5*Phos1+5*Phos2+5*Phos3+5*Phos4)/20;
output;
end;
run;
Your import worked fine for me and, since like Nat I don't have Access for
pcfiles I simply had to change your dbms to xls, but you probably wouldn't
have to do that.
HTH,
Art
---------
On Mon, 10 Jan 2011 12:16:27 -0600, Bhupinder Farmaha
<bhupi80singh@YAHOO.CO.IN> wrote:
>Nat,
>
>The following variable are listed as character variable and their type
>in the form of "$" sign.
>
>Crop,
>Depth (it should not be character or num because it has values like
>0-5, 5-10,..), Fert Field Loc Sampling_stage Tillplc
>
>If I use the "if - then statement" as if "crop = 'Corn' then delete;"
>then it won't delete that dataset.
>
>Thanks
>Bhupinder
>
>-----Original Message-----
>From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of
>Nat Wooding
>Sent: Monday, January 10, 2011 12:03 PM
>To: SAS-L@LISTSERV.UGA.EDU
>Subject: Re: Creating subset of data and calculate weighted means for
>depth
>
>Bhupinder
>
>
>
>Which many of the data variables that your are reading are coming in as
>character values which is why new numeric variables are being created
>through multiplication by 1. This, by the way, is not the best coding
>practice but we can ignore it for now.
>
>
>
>If you write
>
>
>
>If crop = 'Corn' then delete;
>
>
>
>Are the values for corn removed?
>
>
>
>Nat
>
>
>
> _____
>
>From: Bhupinder Farmaha [mailto:bhupi80singh@yahoo.co.in]
>Sent: Monday, January 10, 2011 1:00 PM
>To: 'Nat Wooding'; SAS-L@LISTSERV.UGA.EDU; Arthur Tabachneck
>Subject: RE: Creating subset of data and calculate weighted means for
>depth
>
>
>
>Hi,
>
>
>
>I ran "data contents" to see if there is any trouble with the format. I
>saw that variable listed as character type have format something like
>"$8.", "$4.".. May be it is causing trouble in the data?
>
>
>
>Nat: I did try to imbed data as it is in SAS (10,440 values) but it
>still didn't resolve the issue. I am running SAS ver 9.2.
>
>
>
>Thanks
>
>Bhupinder
>
>
>
>From: Bhupinder Farmaha [mailto:bhupi80singh@yahoo.co.in]
>Sent: Monday, January 10, 2011 10:55 AM
>To: 'Nat Wooding'; 'SAS-L@LISTSERV.UGA.EDU'; Arthur Tabachneck
>(art297@ROGERS.COM)
>Subject: RE: Creating subset of data and calculate weighted means for
>depth
>
>
>
>Hello Art and Nat,
>
>
>
>Thanks for quick reply. I am hereby posting the part of my dataset
>along with the question that I have on this data. When crop is "none",
>it shows initial phosphorus, potassium, calcium, and magnesium levels
>of the field
at
>the start of experiment (2007). Then at the end of each year (2007,
>2008, and 2009) we measured these parameters again to quantify the
>effect of treatments at different depths and different parts of the plots,
aka, "loc"
>(IR-at the planting row; BR- between two planting rows). My second
>question in the original post came from the idea to quantify how much
>soil
phosphorus
>increased or decreased for top 18 cm (weighted means of 0-5, 5-10, and
>10-18) at the end of each year from initial values as well as from the
first
>year. This can be calculate for locations separately and also for
>overall each plot.
>
>
>
>The file also has data from other half part of the field for corn crop
which
>won't get utilized in this analysis.
>
>
>
>Soybean experiment (2007 - 09) was planned as RCBD with split split
>plot arrangements of treatments with three replications. Three tillage
treatments
>(NTBC, NTDP, and STDP) were done in three blocks as main treatments
>which I am calling as whole plots. Each block (whole plot) was divided
>into four parts to apply four phosphorus levels (0, 12, 24, and 36
>kg/ha) which I am calling as sub-plots. These sub-plots were further
>divided into four parts to apply four potassium levels (0, 42, 84, and
>168 kg/ha) which I am
calling
>as sub-sub-plots or "PLOTS". For year 2008 and 2009, we sampled all the
>plots but in the third year, we sampled plots only receiving 5
>particular fertility treatments out of 16 combinations of P and K.
>Therefore, I have fertility (P-K) as category.
>
>
>
>We sampled soil at two particular "locations" (within two planting rows
>and at the planting rows) and four depths (0-5, 5-10, 10-20, and 20-40)
>within PLOTS.
>
>
>
>
>Year
>
>Field
>
>Crop
>
>Sampling_stage
>
>Plot
>
>Block
>
>Tillplc
>
>Prate
>
>Krate
>
>Fert
>
>Loc
>
>Depth
>
>Phos
>
>Potas
>
>Calc
>
>Magn
>
>
>2007
>
>F2
>
>none
>
>Spring07
>
>401
>
>4
>
>NTBC
>
>0
>
>84
>
>P0K84
>
>.
>
>0-5
>
>18
>
>286
>
>1570
>
>219
>
>
>2007
>
>F2
>
>none
>
>Spring07
>
>401
>
>4
>
>NTBC
>
>0
>
>84
>
>P0K84
>
>.
>
>5-10
>
>9
>
>130
>
>1734
>
>182
>
>
>2007
>
>F2
>
>none
>
>Spring07
>
>401
>
>4
>
>NTBC
>
>0
>
>84
>
>P0K84
>
>.
>
>10-18
>
>6
>
>70
>
>1503
>
>164
>
>
>2007
>
>F2
>
>none
>
>Spring07
>
>401
>
>4
>
>NTBC
>
>0
>
>84
>
>P0K84
>
>.
>
>18-50
>
>3
>
>81
>
>1885
>
>300
>
>
>2007
>
>F2
>
>none
>
>Spring07
>
>402
>
>4
>
>NTBC
>
>0
>
>0
>
>P0K0
>
>.
>
>0-5
>
>19
>
>169
>
>1429
>
>204
>
>
>2007
>
>F2
>
>none
>
>Spring07
>
>402
>
>4
>
>NTBC
>
>0
>
>0
>
>P0K0
>
>.
>
>5-10
>
>7
>
>66
>
>1791
>
>187
>
>
>2007
>
>F2
>
>none
>
>Spring07
>
>402
>
>4
>
>NTBC
>
>0
>
>0
>
>P0K0
>
>.
>
>10-18
>
>6
>
>56
>
>1647
>
>162
>
>
>2007
>
>F2
>
>none
>
>Spring07
>
>402
>
>4
>
>NTBC
>
>0
>
>0
>
>P0K0
>
>.
>
>18-50
>
>3
>
>76
>
>2092
>
>303
>
>
>.
>
>.
>
>.
>
>.
>
>.
>
>.
>
>.
>
>.
>
>.
>
>.
>
>.
>
>.
>
>.
>
>.
>
>.
>
>.
>
>
>2007
>
>F2
>
>Soybean
>
>Fall07
>
>401
>
>4
>
>NTBC
>
>0
>
>84
>
>P0K84
>
>BR
>
>0-5
>
>10
>
>251
>
>1528
>
>207
>
>
>2007
>
>F2
>
>Soybean
>
>Fall07
>
>401
>
>4
>
>NTBC
>
>0
>
>84
>
>P0K84
>
>BR
>
>5-10
>
>7
>
>99
>
>1524
>
>166
>
>
>2007
>
>F2
>
>Soybean
>
>Fall07
>
>401
>
>4
>
>NTBC
>
>0
>
>84
>
>P0K84
>
>BR
>
>10-18
>
>3
>
>66
>
>1406
>
>151
>
>
>2007
>
>F2
>
>Soybean
>
>Fall07
>
>401
>
>4
>
>NTBC
>
>0
>
>84
>
>P0K84
>
>BR
>
>18-50
>
>2
>
>109
>
>2355
>
>367
>
>
>2007
>
>F2
>
>Soybean
>
>Fall07
>
>401
>
>4
>
>NTBC
>
>0
>
>84
>
>P0K84
>
>IR
>
>0-5
>
>5
>
>78
>
>1530
>
>158
>
>
>2007
>
>F2
>
>Soybean
>
>Fall07
>
>401
>
>4
>
>NTBC
>
>0
>
>84
>
>P0K84
>
>IR
>
>5-10
>
>6
>
>118
>
>1617
>
>176
>
>
>2007
>
>F2
>
>Soybean
>
>Fall07
>
>401
>
>4
>
>NTBC
>
>0
>
>84
>
>P0K84
>
>IR
>
>10-18
>
>10
>
>259
>
>1463
>
>218
>
>
>2007
>
>F2
>
>Soybean
>
>Fall07
>
>401
>
>4
>
>NTBC
>
>0
>
>84
>
>P0K84
>
>IR
>
>18-50
>
>3
>
>99
>
>2001
>
>330
>
>
>.
>
>.
>
>.
>
>.
>
>.
>
>.
>
>.
>
>.
>
>.
>
>.
>
>.
>
>.
>
>.
>
>.
>
>.
>
>.
>
>
>2008
>
>F1
>
>Soybean
>
>Fall08
>
>101
>
>1
>
>NTDP
>
>36
>
>168
>
>P36K168
>
>BR
>
>0-5
>
>39
>
>349
>
>1664
>
>291
>
>
>2008
>
>F1
>
>Soybean
>
>Fall08
>
>101
>
>1
>
>NTDP
>
>36
>
>168
>
>P36K168
>
>BR
>
>5-10
>
>37
>
>238
>
>1710
>
>237
>
>
>2008
>
>F1
>
>Soybean
>
>Fall08
>
>101
>
>1
>
>NTDP
>
>36
>
>168
>
>P36K168
>
>BR
>
>10-18
>
>101
>
>304
>
>1941
>
>233
>
>
>2008
>
>F1
>
>Soybean
>
>Fall08
>
>101
>
>1
>
>NTDP
>
>36
>
>168
>
>P36K168
>
>BR
>
>18-50
>
>6
>
>185
>
>3074
>
>543
>
>
>2008
>
>F1
>
>Soybean
>
>Fall08
>
>101
>
>1
>
>NTDP
>
>36
>
>168
>
>P36K168
>
>IR
>
>0-5
>
>36
>
>303
>
>1740
>
>258
>
>
>2008
>
>F1
>
>Soybean
>
>Fall08
>
>101
>
>1
>
>NTDP
>
>36
>
>168
>
>P36K168
>
>IR
>
>5-10
>
>22
>
>180
>
>1829
>
>242
>
>
>2008
>
>F1
>
>Soybean
>
>Fall08
>
>101
>
>1
>
>NTDP
>
>36
>
>168
>
>P36K168
>
>IR
>
>10-18
>
>65
>
>236
>
>2167
>
>253
>
>
>2008
>
>F1
>
>Soybean
>
>Fall08
>
>101
>
>1
>
>NTDP
>
>36
>
>168
>
>P36K168
>
>IR
>
>18-50
>
>7
>
>165
>
>2631
>
>491
>
>
>.
>
>.
>
>.
>
>.
>
>.
>
>.
>
>.
>
>.
>
>.
>
>.
>
>.
>
>.
>
>.
>
>.
>
>.
>
>.
>
>
>2009
>
>F2
>
>Soybean
>
>Fall09
>
>402
>
>4
>
>NTBC
>
>0
>
>0
>
>P0K0
>
>BR
>
>0-5
>
>8
>
>190
>
>1481
>
>266
>
>
>2009
>
>F2
>
>Soybean
>
>Fall09
>
>402
>
>4
>
>NTBC
>
>0
>
>0
>
>P0K0
>
>BR
>
>5-10
>
>5
>
>116
>
>1374
>
>211
>
>
>2009
>
>F2
>
>Soybean
>
>Fall09
>
>402
>
>4
>
>NTBC
>
>0
>
>0
>
>P0K0
>
>BR
>
>10-18
>
>5
>
>97
>
>1646
>
>204
>
>
>2009
>
>F2
>
>Soybean
>
>Fall09
>
>402
>
>4
>
>NTBC
>
>0
>
>0
>
>P0K0
>
>BR
>
>18-50
>
>3
>
>94
>
>1867
>
>299
>
>
>2009
>
>F2
>
>Soybean
>
>Fall09
>
>402
>
>4
>
>NTBC
>
>0
>
>0
>
>P0K0
>
>IR
>
>0-5
>
>9
>
>195
>
>1288
>
>237
>
>
>2009
>
>F2
>
>Soybean
>
>Fall09
>
>402
>
>4
>
>NTBC
>
>0
>
>0
>
>P0K0
>
>IR
>
>5-10
>
>5
>
>117
>
>1297
>
>177
>
>
>2009
>
>F2
>
>Soybean
>
>Fall09
>
>402
>
>4
>
>NTBC
>
>0
>
>0
>
>P0K0
>
>IR
>
>10-18
>
>4
>
>81
>
>1543
>
>172
>
>
>2009
>
>F2
>
>Soybean
>
>Fall09
>
>402
>
>4
>
>NTBC
>
>0
>
>0
>
>P0K0
>
>IR
>
>18-50
>
>3
>
>85
>
>1866
>
>266
>
>
>
>To handle this analysis, I need to subset this data for each MIXED
>model. I just want to keep only one data file to avoid confusion.
>
>
>
>In log of both the datasets, it doesn't give any error but also not do
>sub-setting of data. I am sorry if I confused you guys more.
>
>
>
>In the code I posted in previous email, first dataset is to import data
>successfully and remove error if there is any due to different
>character specified in the soil variable columns.
>
>
>
>Thanks
>
>Bhupinder
>
>
>
>-----Original Message-----
>From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of
>Nat Wooding
>Sent: Monday, January 10, 2011 9:57 AM
>To: SAS-L@LISTSERV.UGA.EDU
>Subject: Re: Creating subset of data and calculate weighted means for
>depth
>
>
>
>Bhupinder
>
>
>
>Thank you for posting the code. Are you running V9.2? If so, you can
>eliminate the Proc Import and use the excel file as if it were a SAS
>data set.
>
>
>
>I seldom used Proc Import and my SAS copy will not read the Excel
>engine so I cannot test a variation of your code.
>
>
>
>Please post the log for your two data steps. That would tell us a lot.
Also,
>how may crops are in the soils data set?
>
>
>
>I will be away from the computer for a while but the log would really
>help anyone else who is on line.
>
>
>
>Nat
>
>
>
>
>
>
>
>-----Original Message-----
>
>From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of
>Bhupinder Farmaha
>
>Sent: Monday, January 10, 2011 10:40 AM
>
>To: SAS-L@LISTSERV.UGA.EDU
>
>Subject: Re: Creating subset of data and calculate weighted means for
>depth
>
>
>
>Hi Nat,
>
>
>
>I made code with " " and the one your suggested. But, unfortunately,
>none
of
>them not worked. I am posting my code complete code here to see if
>there
is
>something wrong.
>
>
>
>PROC IMPORT OUT= WORK.Soils
>
> DATAFILE= "C:\Users\Bhupinder\Desktop\Roots, soil, and soil
>water\PKCSSoildata200709.xls"
>
> DBMS=EXCEL REPLACE;
>
> RANGE="CompleteCS$";
>
> GETNAMES=YES;
>
> MIXED=NO;
>
> SCANTEXT=YES;
>
> USEDATE=YES;
>
> SCANTIME=YES;
>
>RUN;
>
>
>
>data Soils;
>
> set Soils;
>
> Phos1=Phos*1;
>
> Potas1=Potas*1;
>
> Calc1=Calc*1;
>
> Magn1=Magn*1;
>
> drop Phos;
>
> drop Potas;
>
> drop Calc;
>
> drop Magn;
>
> rename Phos1=Phos;
>
> rename Potas1=Potas;
>
> rename Calc1=Calc;
>
> rename Magn1=Magn;
>
>run;
>
>
>
>data soils; set soils;
>
>if crop = "corn" then delete;
>
>if crop = "soybean" then delete;
>
>run;
>
>
>
>proc print data = soils;
>
>run;
>
>
>
>Thanks
>
>Bhupinder
>
>
>
>-----Original Message-----
>
>From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of
>Nat Wooding
>
>Sent: Monday, January 10, 2011 9:19 AM
>
>To: SAS-L@LISTSERV.UGA.EDU
>
>Subject: Re: Creating subset of data and calculate weighted means for
>depth
>
>
>
>Bhupinder
>
>
>
>In your code, the line
>
>
>
>If crop = corn then delete;
>
>
>
>Will not work as you wish for at least one reason, that corn is not
enclosed
>in quotation marks. This assumes that you want to select the crop
>called "corn". As the code stands, the statement will attempt to test
>the value of the variable Crop with the value of another variable, Corn
>which I presume does note exist in set Soils. Take a look at the log of the
following code.
>
>
>
>data crops;
>
>informat crop $10.;
>
>input crop @@;
>
>cards;
>
>corn
>
>corn
>
>wheat
>
>rye
>
>barley
>
>run;
>
>Data wanted;
>
>set crops;
>
>if crop = corn;
>
>run;
>
>
>
>If you enclose Corn in quotation marks, then the If statement will work.
>
>Your If statement, with quotations, would delete all data for Corn. Is
>this what you want or do you wish to select the values for corn?
>Without a
sample
>of your data, I can only guess.
>
>
>
>I am going to ignore your second question for the moment until we get
>the first one resolved.
>
>
>
>Nat Wooding
>
>
>
>-----Original Message-----
>
>From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of
>Bhupinder Farmaha
>
>Sent: Monday, January 10, 2011 10:00 AM
>
>To: SAS-L@LISTSERV.UGA.EDU
>
>Subject: Creating subset of data and calculate weighted means for depth
>
>
>
>Hi,
>
>
>
>
>
>
>
>I want to create subset of data based on one level of independent variable.
>
>I have used the following code to create subset of the data but it
>didn't work.
>
>
>
>
>
>
>
>data spring07; set soils;
>
>
>
>if crop = corn then delete;
>
>
>
>run;
>
>
>
>
>
>
>
>Is there any way to model with variable ne (^=). I did try but it is
>not working.
>
>
>
>
>
>
>
>My second question is to calculate weighted means based on depths from
>that particular subset of data. I want to get weighted means of Phos
>variable based on depth as per following formula
>
>
>
>
>
>
>
>Phos(weighted0-40) = (5*Phos1 + 5*Phos2 + 10*Phos3 + 20*Phos4)/40.
>
>
>
>
>
>
>
>Phos(weighted0-20) = (5*Phos1 + 5*Phos2 + 10*Phos3)/20.
>
>
>
>
>
>
>
>I don't know how I can do that because data has different other
>variables like Tillplc, Prate, Krate, and sampling location (in-row and
between row).
>
>
>
>
>
>
>
>Thanks, in advance!
>
>
>
>Bhupinder
| 
