- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
I have a data where if the prop value is same for same pt than my flag variable should be 0 and if changes then it should be 1.
In the below example it should be 0 for pt 101 and 103 as the value is same but for 102 the prop has changed.
any help
pt | prop |
101 | poor |
101 | poor |
102 | poor |
102 | high |
102 | very high |
103 | medium |
103 | medium |
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
@vraj1 wrote:
sorry for not being clear.
Here is the test data below and in this what i need is only improvement as 1 and else it should be 0 based on pt.
in 101 there is no improvement so flag should be 0 for both records. in 102 it improved from poor to high so flag should be 1 and in 105 it went to hight to medium so flag should be 0.
data have;
input pt prop $10.;
cards;
101 poor
101 poor
102 poor
102 high
102 very high
103 medium
103 medium
104 medium
104 poor
105 high
105 medium
;
Start by making prop a scalar value, using informats/formats:
proc format;
invalue property
'poor' = 0
'medium' = 1
'high' = 2
'very high' = 3
;
value property
0 = 'poor'
1 = 'medium'
2 = 'high'
3 = 'very high'
;
run;
data have;
infile cards dlm=',';
input pt prop property.;
format prop property.;
cards;
101,poor
101,poor
102,poor
102,high
102,very high
103,medium
103,medium
104,medium
104,poor
105,high
105,medium
;
run;
Now you can easily compare with a lagged value:
data flags (keep=pt flag);
set have;
by pt;
retain flag;
oldprop = lag(prop);
if first.pt then flag = 0;
else if prop > oldprop then flag = 1;
if last.pt then output;
run;
data want;
merge
have
flags
;
by pt;
run;
proc print data=want noobs;
run;
Result:
pt prop flag 101 poor 0 101 poor 0 102 poor 1 102 high 1 102 very high 1 103 medium 0 103 medium 0 104 medium 0 104 poor 0 105 high 0 105 medium 0
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
data have;
input pt prop $10.;
cards;
101 poor
101 poor
102 poor
102 high
102 very high
103 medium
103 medium
;
proc sql;
create table want as
select *,count(distinct prop)>1 as flag
from have
group by pt;
quit;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
it didnt work
data have; input pt vis prop $10. ; cards; 101 1 poor 101 2 poor 102 1 poor 102 2 high 102 3 very high 103 1 medium 103 2 medium 104 1 medium 104 2 poor 105 1 high 105 2 medium ; run; proc sort data=have; by pt vis; run; proc sql; create table want as select *,count(distinct prop)>1 as flag from have group by pt; quit;
also created a new variable to differentiate but for pt 105 it is from high to medium so the flag should be 0 but it is coming as 1.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Note that we have explained many times before how to post test data in the form of a datastep and show what you want as output.
So once again, this is just untested and may not be inline with what you want.
proc sql; create table want as select *, case when exists(select distinct b.prop from have b where b.pt=pt and b.prop ne prop) then "Diff" else "Same" end as result from have; quit;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
sorry for not being clear.
Here is the test data below and in this what i need is only improvement as 1 and else it should be 0 based on pt.
in 101 there is no improvement so flag should be 0 for both records. in 102 it improved from poor to high so flag should be 1 and in 105 it went to hight to medium so flag should be 0.
data have;
input pt prop $10.;
cards;
101 poor
101 poor
102 poor
102 high
102 very high
103 medium
103 medium
104 medium
104 poor
105 high
105 medium
;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Scrub that, see @novinosrin's response earlier, that works just as well, and is simpler coding, use that.
This works, can't think but am sure there is a simpler way:
data have; input pt prop $10.; cards; 101 poor 101 poor 102 poor 102 high 102 very high 103 medium 103 medium 104 medium 104 poor 105 high 105 medium ; run; proc sql; create table want as select a.*, case when b.prop ne "" then 1 else 0 end as result from have a left join have b on a.pt=b.pt and a.prop ne b.prop; quit;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
It is not working and creating more records. from 11 observations it is becoming 14 and it should havce the same order as it is i.e first record for 105 is high and second record is medium that case it is decreasing
NOTE: The data set WORK.HAVE has 11 observations and 2 variables.
NOTE: Compressing data set WORK.HAVE increased size by 100.00 percent.
Compressed is 2 pages; un-compressed would require 1 pages.
NOTE: DATA statement used (Total process time):
real time 0.02 seconds
cpu time 0.01 seconds
268 ;
269 run;
270 proc sql;
271 create table want1 as
272 select a.*,
273 case when b.prop ne "" then 1 else 0 end as result
274 from have a
275 left join have b
276 on a.pt=b.pt
277 and a.prop ne b.prop;
NOTE: Compressing data set WORK.WANT1 increased size by 100.00 percent.
Compressed is 2 pages; un-compressed would require 1 pages.
NOTE: Table WORK.WANT1 created, with 14 rows and 3 columns.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Use @novinosrin's method.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
used it but no luck
data have; input pt vis prop $10. ; cards; 101 1 poor 101 2 poor 102 1 poor 102 2 high 102 3 very high 103 1 medium 103 2 medium 104 1 medium 104 2 poor 105 1 high 105 2 medium ; run; proc sort data=have; by pt vis; run; proc sql; create table want as select *,count(distinct prop)>1 as flag from have group by pt; quit;
also created a new variable to differentiate but for pt 105 it is from high to medium so the flag should be 0 but it is coming as 1.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
You never mentioned that only changes which increase severity are to be considered?
So what exactly is the condition here, as
102 1 poor 102 2 high 102 3 very high
Row 1 cannot be 1 as there is no comparitor. Maybe this does what you want?
data have; input pt vis prop $10. ; select(prop); when ("poor") prop_n=1; when ("medium") prop_n=2; when ("high") prop_n=3; when ("very high") prop_n=4; otherwise prop_n=99; end; cards; 101 1 poor 101 2 poor 102 1 poor 102 2 high 102 3 very high 103 1 medium 103 2 medium 104 1 medium 104 2 poor 105 1 high 105 2 medium ; run; data want; set have; retain result lstprop; by pt; if first.pt then do; result=0; lstprop=prop_n; end; else if prop_n > lstprop then result=1; lstprop=prop_n; run;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
@vraj1 wrote:
sorry for not being clear.
Here is the test data below and in this what i need is only improvement as 1 and else it should be 0 based on pt.
in 101 there is no improvement so flag should be 0 for both records. in 102 it improved from poor to high so flag should be 1 and in 105 it went to hight to medium so flag should be 0.
data have;
input pt prop $10.;
cards;
101 poor
101 poor
102 poor
102 high
102 very high
103 medium
103 medium
104 medium
104 poor
105 high
105 medium
;
Start by making prop a scalar value, using informats/formats:
proc format;
invalue property
'poor' = 0
'medium' = 1
'high' = 2
'very high' = 3
;
value property
0 = 'poor'
1 = 'medium'
2 = 'high'
3 = 'very high'
;
run;
data have;
infile cards dlm=',';
input pt prop property.;
format prop property.;
cards;
101,poor
101,poor
102,poor
102,high
102,very high
103,medium
103,medium
104,medium
104,poor
105,high
105,medium
;
run;
Now you can easily compare with a lagged value:
data flags (keep=pt flag);
set have;
by pt;
retain flag;
oldprop = lag(prop);
if first.pt then flag = 0;
else if prop > oldprop then flag = 1;
if last.pt then output;
run;
data want;
merge
have
flags
;
by pt;
run;
proc print data=want noobs;
run;
Result:
pt prop flag 101 poor 0 101 poor 0 102 poor 1 102 high 1 102 very high 1 103 medium 0 103 medium 0 104 medium 0 104 poor 0 105 high 0 105 medium 0
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
I need flag 0 and 1. 0 if the value is depreciating and 1 if increasing. values i have are poor, medium, high and very high
data have; input pt vis prop $10. ; cards; 101 1 poor 101 2 poor 102 1 poor 102 2 high 102 3 very high 103 1 medium 103 2 medium 104 1 medium 104 2 poor 105 1 high 105 2 medium ; run; proc sort data=have; by pt vis; run; proc sql; create table want as select *,count(distinct prop)>1 as flag from have group by pt; quit;
In this if pt of 101 is poor in both records so flag should be 0 for that pt. for pt=105 it decreaed from high to medium and flag should be 0. in pt=102 it increased from poor to high and then to very high so the flag should be 1 for that pt.
Any help
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
@vraj1 Your modified question is quite different to the original question you posted at the top. I think that can create problems for responders to look through the thread as you make changes to your question. Kindly stick to one question