topic Re: How to assign a value based on a condition in SAS Programming

How to assign a value based on a condition

knveraraju91 — Tue, 20 Dec 2016 05:26:18 GMT

Dear,

In my data the following scenario is present.

I need create a variable base by following condition.

if flag='y' then base=value.

Then populate the base to all OBS containing same id test type. Please help in my code. I am not getting the output i need. Thank you

In the first five OBS, the first three have base=value of second OBS.

Next two have base=fourth OBS value

Code:

proc sort data=have out=havsrt;
by id test bt;
run;

data want;
retain base .;
do until (last.bt);
set havsrt;
by id test bt;
if flag='y' then base=val;
end;
do until (last.bt);
set havsrt;
by id test bt;
output;
end;
base=.;
run;

data

id date test type value flag

1 2014-08-24T04:55 bp morning 100

1 2014-08-24T06:55 bp morning 110 y

1 2014-08-24T08:55 bp morning 120

1 2014-09-24T09:55 bp morning 130 y

1 2014-09-24T010:55 bp morning 150

1 2014-06-24T04:55 bp evening 90

1 2014-06-24T06:55 bp evening 160 y

1 2014-06-24T08:55 bp evening 107

1 2014-09-24T09:55 bp evening 108 y

1 2014-09-24T10:55 bp evening 119

output needed;

id date test type value flag base

1 2014-08-24T04:55 bp morning 100 110

1 2014-08-24T06:55 bp morning 110 y 110

1 2014-08-24T08:55 bp morning 120 110

1 2014-09-24T09:55 bp morning 130 y 130

1 2014-09-24T010:55 bp morning 150 130

1 2014-06-24T04:55 bp evening 90 160

1 2014-06-24T06:55 bp evening 160 y 160

1 2014-06-24T08:55 bp evening 107 160

1 2014-09-24T09:55 bp evening 108 y 108

1 2014-09-24T10:55 bp evening 119 108

Re: How to assign a value based on a condition

PoojaP1 — Tue, 20 Dec 2016 06:29:28 GMT

Hello,

Could you please explain what is the "bt" variable in your code ? As it is not there in your input dataset.

Re: How to assign a value based on a condition

Astounding — Tue, 20 Dec 2016 07:20:34 GMT

The variables in your program are somewhat different than those in your sample data. Using those in your program (following the existing PROC SORT):

data first_flag;

set havsrt;

by id test bt;

where flag = 'y';

if first.bt;

base = val;

keep id test bt base;

run;

data want;

merge first_flag havsrt;

by id test bt;

if flag='y' then base = val;

run;

There was one condition that wasn't mentioned. What should happen to BASE if a grouping of ID TEST BT has no flagged observations? This program leaves BASE missing for that grouping, but the logic could fairly easily be changed.

Re: How to assign a value based on a condition

Ksharp — Tue, 20 Dec 2016 09:35:17 GMT

If I understood what you mean.



data have;
infile cards truncover ;
input id           date  : $29.      test  $     type   $       value  flag $;
n+1;
cards;
1          2014-08-24T04:55                    bp                       morning                   100                               
1          2014-08-24T06:55                    bp                       morning                   110                               y
1          2014-08-24T08:55                    bp                       morning                   120                               
1          2014-09-24T09:55                    bp                       morning                   130                                y  
1          2014-09-24T010:55                  bp                       morning                   150        
1          2014-06-24T04:55                    bp                       evening                    90                               
1          2014-06-24T06:55                    bp                       evening                    160                               y
1          2014-06-24T08:55                    bp                       evening                    107                               
1          2014-09-24T09:55                    bp                       evening                    108                                y  
1          2014-09-24T10:55                    bp                       evening                    119                                 
;
run;
data temp;
 set have;
 by id test type notsorted;
 retain temp_base;
 if first.type then call missing(temp_base);
 if flag='y' then temp_base=value;
run;
proc sort data=temp;by descending n;run;
data want;
 set temp;
 by id test type notsorted;
 retain base;
 if first.type then call missing(base);
 if not missing(temp_base) then base=temp_base;
 drop temp_base;
run;
proc sort data=want;by n;run;

Re: How to assign a value based on a condition

knveraraju91 — Tue, 20 Dec 2016 14:16:22 GMT

Hi Sorry for confusion. I did not explain better in the question. I will explain again. Thanks for the suggestion

There is also visit variable that I need to take into consideration.

first step:

I have assign a base value for each OBS. If flag is 'y' then base=value.

Then populate the base number to all OBS with same id test type. In the first five OBS, the second and fourth OBS have flag='y'.

For the second and fourth OBS have base=value.

So,for the first and third have same base value that second has, because same id test type and visit

Fourth OBS has flag='y', so the base=value of fourth OBS

But for the fifth OBS, the base value should be equal to fourth base value because the fifth OBS date is after fourth OBS date and same visit

data

id date test type value flag visit

1 2014-08-24T04:55 bp morning 100 wk1

1 2014-08-24T06:55 bp morning 110 y wk1

1 2014-08-24T08:55 bp morning 120 wk1

1 2014-09-24T09:55 bp morning 130 y wk4

1 2014-09-24T010:55 bp morning 150 wk4

1 2014-06-24T04:55 bp evening 90 wk1

1 2014-06-24T06:55 bp evening 160 y wk1

1 2014-06-24T08:55 bp evening 107 wk1

1 2014-09-24T09:55 bp evening 108 y wk4

1 2014-09-24T10:55 bp evening 119 wk4

output needed;

id date test type value flag base

1 2014-08-24T04:55 bp morning 100 110

1 2014-08-24T06:55 bp morning 110 y 110

1 2014-08-24T08:55 bp morning 120 110

1 2014-09-24T09:55 bp morning 130 y 130

1 2014-09-24T010:55 bp morning 150 130

1 2014-06-24T04:55 bp evening 90 160

1 2014-06-24T06:55 bp evening 160 y 160

1 2014-06-24T08:55 bp evening 107 160

1 2014-09-24T09:55 bp evening 108 y 108

1 2014-09-24T10:55 bp evening 119 108

Re: How to assign a value based on a condition

Astounding — Tue, 20 Dec 2016 15:21:27 GMT

I think my program does what you need. Is it giving you trouble?