DATA Step, Macro, Functions and more

if the value is same for same id variable i want a flag and keep it as 0

Accepted Solution Solved
Reply
Regular Contributor
Posts: 214
Accepted Solution

if the value is same for same id variable i want a flag and keep it as 0

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
Solution
3 weeks ago
Super User
Posts: 10,280

Re: if the value is same for same id variable i want a flag and keep it as 0

[ Edited ]

@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 

 

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code

View solution in original post


All Replies
PROC Star
Posts: 1,836

Re: if the value is same for same id variable i want a flag and keep it as 0

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;
Regular Contributor
Posts: 214

Re: if the value is same for same id variable i want a flag and keep it as 0

Posted in reply to novinosrin

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.

Super User
Super User
Posts: 9,599

Re: if the value is same for same id variable i want a flag and keep it as 0

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;
Regular Contributor
Posts: 214

Re: if the value is same for same id variable i want a flag and keep it as 0

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
;

Super User
Super User
Posts: 9,599

Re: if the value is same for same id variable i want a flag and keep it as 0

[ Edited ]

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;

 

Regular Contributor
Posts: 214

Re: if the value is same for same id variable i want a flag and keep it as 0

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.

Super User
Super User
Posts: 9,599

Re: if the value is same for same id variable i want a flag and keep it as 0

Use @novinosrin's method.

Regular Contributor
Posts: 214

Re: if the value is same for same id variable i want a flag and keep it as 0

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.

Super User
Super User
Posts: 9,599

Re: if the value is same for same id variable i want a flag and keep it as 0

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;

  
Solution
3 weeks ago
Super User
Posts: 10,280

Re: if the value is same for same id variable i want a flag and keep it as 0

[ Edited ]

@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 

 

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Regular Contributor
Posts: 214

need flag for depriciating values

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

Frequent Contributor
Posts: 75

Re: need flag for depriciating values

@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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 12 replies
  • 146 views
  • 0 likes
  • 5 in conversation