Prev: date and time format
Next: How to resolve macro variable in double qoutes inside single quotes
From: Thx for your HELP on 14 Jun 2010 14:01
Hi, Could you help me with a code that will do the job below? Thank
you very much.


data test;
input temperature value;
cards;
-20 23.25
-10 19.9
0 16.9
10 14.3
20 12.1
30 10.3
40 8.9
50 7.9
60 7.2
70 7.0
80 7.3
90 7.7
;
run;

Using the above data, I'd like to create a new variable "base" with a
uniform value taken from temperature 70.
It would look something like below.


data test;
input temperature value base;
cards;
-20 23.25 7.0
-10 19.9 7.0
0 16.9 7.0
10 14.3 7.0
20 12.1 7.0
30 10.3 7.0
40 8.9 7.0
50 7.9 7.0
60 7.2 7.0
70 7.0 7.0
80 7.3 7.0
90 7.7 7.0
;
run;
From: Ya on 14 Jun 2010 14:10
On Jun 14, 11:01 am, Thx for your HELP <jjongw...(a)gmail.com> wrote:
> Hi, Could you help me with a code that will do the job below?  Thank
> you very much.
>
> data test;
> input temperature value;
> cards;
>         -20 23.25
>         -10 19.9
>         0 16.9
>         10 14.3
>         20 12.1
>         30 10.3
>         40 8.9
>         50 7.9
>         60 7.2
>         70 7.0
>         80 7.3
>         90 7.7
> ;
> run;
>
> Using the above data, I'd like to create a new variable "base" with a
> uniform value taken from temperature 70.
> It would look something like below.
>
> data test;
> input temperature value base;
> cards;
>         -20 23.25 7.0
>         -10 19.9 7.0
>         0 16.9 7.0
>         10 14.3 7.0
>         20 12.1 7.0
>         30 10.3 7.0
>         40 8.9 7.0
>         50 7.9 7.0
>         60 7.2 7.0
>         70 7.0 7.0
>         80 7.3 7.0
>         90 7.7 7.0
> ;
> run;

proc sql;
create table test1 as
select *, mean(case when temperature=70 then value else . end) as base
from test
;

You can use max, min, sum etc., I use mean in case you have tied
temp=70, then the mean of them will be used
for the base.

HTH

Ya
From: Thx for your HELP on 14 Jun 2010 14:27
Thank you so much for your prompt response, Ya.
That works great, but how would that change if you have a by variable
called "var" as below.


data test;
input var temperature value;
cards;

1 -20 23.25
1 -10 19.9
1 0 16.9
1 10 14.3
1 20 12.1
1 30 10.3
1 40 8.9
1 50 7.9
1 60 7.2
1 70 7.0
1 80 7.3
1 90 7.7
2 -20 23.25
2 -10 19.9
2 0 16.9
2 10 14.3
2 20 12.1
2 30 10.3
2 40 8.9
2 50 7.9
2 60 7.2
2 70 8.0
2 80 7.3
2 90 7.7

;
run;

I'd like to still create a variable "base" grabbing the corresponding
value for temperature 70 for both 1 and 2 by variable.
Thanks again.
From: Ya on 14 Jun 2010 14:37
On Jun 14, 11:27 am, Thx for your HELP <jjongw...(a)gmail.com> wrote:
> Thank you so much for your prompt response, Ya.
> That works great, but how would that change if you have a by variable
> called "var" as below.
>
> data test;
> input var temperature value;
> cards;
>
>         1 -20 23.25
>         1 -10 19.9
>         1 0 16.9
>         1 10 14.3
>         1 20 12.1
>         1 30 10.3
>         1 40 8.9
>         1 50 7.9
>         1 60 7.2
>         1 70 7.0
>         1 80 7.3
>         1 90 7.7
>         2 -20 23.25
>         2 -10 19.9
>         2 0 16.9
>         2 10 14.3
>         2 20 12.1
>         2 30 10.3
>         2 40 8.9
>         2 50 7.9
>         2 60 7.2
>         2 70 8.0
>         2 80 7.3
>         2 90 7.7
>
> ;
> run;
>
> I'd like to still create a variable "base" grabbing the corresponding
> value for temperature 70 for both 1 and 2 by variable.
> Thanks again.

Just add "group by"

proc sql;
create table test1 as
select *, mean(case when temperature=70 then value else . end) as
base
from test
group by var
;
 | 
Pages: 1
Prev: date and time format
Next: How to resolve macro variable in double qoutes inside single quotes