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
Hello,
Could you please explain what is the "bt" variable in your code ? As it is not there in your input dataset.
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.
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;
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
I think my program does what you need. Is it giving you trouble?
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.