BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
vraj1
Quartz | Level 8

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
1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

@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 

 

View solution in original post

12 REPLIES 12
novinosrin
Tourmaline | Level 20
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;
vraj1
Quartz | Level 8

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;
vraj1
Quartz | Level 8

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
;

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

 

vraj1
Quartz | Level 8

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.

vraj1
Quartz | Level 8

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

  
Kurt_Bremser
Super User

@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 

 

vraj1
Quartz | Level 8

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

Andygray
Quartz | Level 8

@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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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