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

Hello

Can someone help me in tacking the below and assigning them the flag.

Below is are unique records, I want to flag and increment if they fall under same visit and id, and reflag if not same visit

id date visit output

1 2001-02-21 3 --------------->flag =1

1 2001-02-22 3 --------------->flag =2

2 2002-02-26 9 --------------->flag =1

3 2002-12-13 2 --------------->flag =1

3 2002-12-29 9 --------------->flag =1

4 2004-12-13 7 ---------------->flag =1

4 2004-12-14 7 ----------------->flag =2

4 2005-01-06 24 ------------------>flag =1

4 2005-01-07 24 ------------------>flag =2

For above:

data test;

infile datalines dsd;

input id 1-2 date $3-12 visit 14 ;

datalines;

1 2001-02-21 3

1 2001-02-22 3

2 2002-02-26 9

3 2002-12-13 2

3 2002-12-29 9

4 2004-12-13 7

4 2004-12-14 7

4 2005-01-06 24

4 2005-01-07 24

;

run;

Criteria here

a)for first record same id, visit assign flag=1, when the visit is same for the same id, but the date change than increase the flag by 1.

b) when the first id and and second id observation is same, the visit is different and they are the first record for the the id assign flag to 1.

3) over all id falls in the same visit but changes can be seen on the data increment

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

If I understand correctly, if a subject had the same visit and date you wouldn't want output to be increased by one. You could do this:

proc sort data=test; by id visit date; run;

data testOut;
set test;
by id visit date;
if first.visit

     then output = 1;
     else output + first.date;
run;

proc print data=testOut noobs; run;

PG

PG

View solution in original post

10 REPLIES 10
tad
Calcite | Level 5 tad
Calcite | Level 5

Above will not solve the problem , it will assign all the flag to 1

PGStats
Opal | Level 21

I just noticed something in that second idre example: the statement

if first.class or first.gender then count = 1;

should be simply

if first.gender then count = 1;

since first.class inplies first.gender when data is sorted by class gender. The result is the same of course, except for the poor student trying to learn about the workings of first. and last. Smiley Wink

PG

PG
PGStats
Opal | Level 21

If I understand correctly, if a subject had the same visit and date you wouldn't want output to be increased by one. You could do this:

proc sort data=test; by id visit date; run;

data testOut;
set test;
by id visit date;
if first.visit

     then output = 1;
     else output + first.date;
run;

proc print data=testOut noobs; run;

PG

PG
Fugue
Quartz | Level 8

Your solution is simpler and far more elegant than the one I posted.

Fugue
Quartz | Level 8

data test;
infile datalines dlm=' ' dsd;
format date date9.;
input id date :yymmdd10. visit ;
datalines;
1 2001-02-21 3
1 2001-02-22 3
2 2002-02-26 9
3 2002-12-13 2
3 2002-12-29 9
4 2004-12-13 7
4 2004-12-14 7
4 2005-01-06 24
4 2005-01-07 24
;
;;;;

/* do a precautionary sort */

proc sort data=test;
by id date visit;

/* process and set flag */

Data want ;
     set test;
  drop last_id last_visit ;
     by id date visit;
  last_id = lag1(id);
  last_visit = lag1(visit);
  if last_id = . then flag = 1;
else if id ^= last_id then flag = 1;
else if visit ^= last_visit then flag = 1 ;
else flag + 1 ;
run ;

Pallav
Calcite | Level 5

This might be useful

proc sort data = test;

   by id   visit;

run;

data new;

   set test;

by id   visit;

   if first.visit then flag=0;

     flag +1;

Run;

proc print;

run;

UrvishShah
Fluorite | Level 6

data want;

  set have;

  by id visit;   

  if (first.id or first.visit) NE (last.id or last.visit) then flag + 1;

  if first.id or first.visit then flag = 1;

run;

-Urvish

tad
Calcite | Level 5 tad
Calcite | Level 5

Orignial questions was answered but now i would like to add a twist here and introduce a new variable:

id date visit                       output

1 2001-02-21 3 --------------->flag =1

1 2001-02-22 3 --------------->flag =2

2 2002-02-26 9 --------------->flag =1

3 2002-12-13 2 --------------->flag =1

3 2002-12-29 9 --------------->flag =1

4 2004-12-13 7 ---------------->flag =1

4 2004-12-14 7 ----------------->flag =2

4 2005-01-06 24 ------------------>flag =1

4 2005-01-07 24 ------------------>flag =2

2) New variable introduced here test, so now here, for the same id, different date, same visit but same test would like to increment the flag, otherwise keep as flag 1

id date            visit     test                   output

1 2001-02-21      3      a--------------->flag =1

1 2001-02-22      3      a--------------->flag =2

2 2002-02-26      9      a --------------->flag =1

3 2002-12-13      2      a--------------->flag =1

3 2002-12-29      9      a--------------->flag =1

4 2004-12-13      7      a---------------->flag =1

4 2004-12-14      7      b----------------->flag =1

4 2005-01-06      24    a ----------------->flag =1

4 2005-01-07      24    a------------------>flag =2

Reeza
Super User

Play around with the by variable.

You probably want by id test date visit or something like that.

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 10 replies
  • 4575 views
  • 3 likes
  • 6 in conversation