Please help me how to code for the below scenario

Reply
New Contributor
Posts: 2

Please help me how to code for the below scenario

Hi all .Please help me how to code for the below scenario. We have a person with id ‘a’. He had tattoos all over his body parts with specific visits such as week 1 , week 2,week 3 and week 4(W1,W2,W3,W4). Tattoos are permanent on those particular body parts even the visits increment.

So in the below scenario “ankle” did not show up in week 3, then query should fire and  “wrist” and “shoulders” did not show up in week 4, then also query should fire. Basically if any of the specific body parts that are there from starting visit or got added along the way should continue to show up at every visit. If missing they should come in the output. Like this we should check for many ids/many persons.

 

Id

Visits

Tattoos on specific body parts

a

w1

Ankle

a

w1

Wrist

a

w2

Ankle

a

w2

Wrist

a

w2

Neck

a

w3

Wrist

a

w3

Neck

a

w3

Shoulders

a

w4

Ankle

a

w4

Neck

a

w4

finger spaces

 

Output should be as shown below:

Id

Visits

Tattoos on specific body parts

a

w3

Ankle

a

w4

wrist

a

w4

Shoulders

SAS Super FREQ
Posts: 8,717

Re: Please help me how to code for the below scenario

Hi:

  What you want is not clear to me. I don't understand "query should fire" and it is not apparent what form your output is in. It looks like you have 1 dataset starting with 11 rows, shown for 1 subject and then you have a much, much smaller report or dataset at the end. So if you start with 11 rows of data, you will end up with either a dataset of 3 rows or a report of 3 rows? OR, are you adding the "extra" body parts to the original file, so that the new file or report would have 14 rows (11+3)?

 

  You did not show anything that you've tried. What code have you tried? What output do you want? Report, table? What is the purpose for your final output? Until your specifications are clear, no one can figure out what you want.

 

cynthia

New Contributor
Posts: 4

Re: Please help me how to code for the below scenario

I believe the following code does what you are expecting.

 

data ink;
	input Id $ Visit $ body_part $6-18;
	datalines;
a w1 Ankle
a w1 Wrist
a w2 Ankle
a w2 Wrist
a w2 Neck
a w3 Wrist
a w3 Neck
a w3 Shoulders
a w4 Ankle
a w4 Neck
a w4 finger spaces
;
run;

proc sort data=ink;
	by id visit body_part;
run;

data query (keep= id visit _part rename=(_part=body_part));
	set ink;
	by id visit body_part;

	length cumul_body_parts parts_curvis $100;
	retain cumul_body_parts parts_curvis '';

	if index(cumul_body_parts, strip(body_part))=0 then cumul_body_parts = catx(', ',cumul_body_parts,body_part);
	if first.visit then parts_curvis = '';
	if index(parts_curvis, strip(body_part))=0 then parts_curvis = catx(', ',parts_curvis,body_part);

	if last.visit then do i=1 to countw(cumul_body_parts);
		if index(parts_curvis, scan(cumul_body_parts, i))=0 then do;
			_part = scan(cumul_body_parts, i);
			output query;
		end;
	end;
run;

 

New Contributor
Posts: 2

Re: Please help me how to code for the below scenario

Thankyou for your response

 

Esteemed Advisor
Posts: 7,290

Re: Please help me how to code for the below scenario

If you have more body parts then mentioned I'd avoid using a solution that uses the Index function as it would give wrong answers to parts such as Left hand, Left foot, etc.

 

As such, I'd approach the problem as follows:

 

proc format;
  invalue part
  'Ankle'=1
  'finger space'=2
  'Neck'=3
  'Shoulders'=4
  'Wrist'=5
  ;
  value tpart
  1='Ankle'
  2='finger space'
  3='Neck'
  4='Shoulders'
  5='Wrist'
  ;
run;

data want;
  set have;
  by id visits;
  array have(5) _temporary_;
  array weeks(5) _temporary_;
  if first.id then call missing(of have(*));
  if first.visits then call missing(of weeks(*));
  have(input(tattoos,part.))=1;
  weeks(input(tattoos,part.))=1;
  if last.visits then do i=1 to dim(have);
    if have(i) ne weeks(i) then do;
      tattoos=put(i,tpart.);
      output;
    end;
  end;
run;

HTH,

Art, CEO, AnalystFinder.com

 

New Contributor
Posts: 4

Re: Please help me how to code for the below scenario

Art,

 

Thanks for pointing out a couple of holes in my code. Your solution is slick and provided yet another way to utilize arrays.

 

Just for the sake of closure, I have modified my code to address the multiple words as body parts.

 


data query (keep= id visit _part rename=(_part=body_part));
	set ink;
	by id visit body_part;

	length cumul_body_parts parts_curvis $100;
	retain cumul_body_parts parts_curvis '';

	if first.id then cumul_body_parts = '';
	if first.visit then parts_curvis = '';
	if index(cumul_body_parts, strip(body_part))=0 then cumul_body_parts = catx(', ',cumul_body_parts,body_part);
	if index(parts_curvis, strip(body_part))=0 then parts_curvis = catx(', ',parts_curvis,body_part);

	if last.visit then do i=1 to countw(cumul_body_parts,',');
		x = strip(scan(cumul_body_parts, i, ','));
		if index(parts_curvis, strip(scan(cumul_body_parts, i, ',')))=0 then do;
			_part = strip(scan(cumul_body_parts, i, ','));
			output;
		end;
	end;
run;
Grand Advisor
Posts: 9,571

Re: Please help me how to code for the below scenario

[ Edited ]

Here is for multiple ID.

 

data ink;
	input Id $ Visit $ body_part $6-18;
	datalines;
a w1 Ankle
a w1 Wrist
a w2 Ankle
a w2 Wrist
a w2 Neck
a w3 Wrist
a w3 Neck
a w3 Shoulders
a w4 Ankle
a w4 Neck
a w4 finger spaces
b w1 Ankle
b w1 Wrist
b w2 Ankle
b w2 Wrist
b w2 Neck
b w3 Wrist
b w3 Neck
b w3 Shoulders
;
run;
data want;
 if _n_=1 then do;
  if 0 then set ink(rename=(body_part=part));
  declare hash h();
  declare hiter hi('h');
  h.definekey('id','part');
  h.definedata('part');
  h.definedone();
  
  declare hash temp();
  declare hiter hi_t('temp');
  temp.definekey('id','part');
  temp.definedata('part');
  temp.definedone();
 end;
set ink;
by id visit;
if first.id then h.clear();
if first.visit then do;
 temp.clear();
 do while(hi.next()=0);
  temp.add();
 end;
end;

rc=temp.remove(key:id,key:body_part);
if rc ne 0 then h.replace(key:id,key:body_part,data:body_part);

if last.visit then do;
 do while(hi_t.next()=0);
  output;
 end;
end;

drop body_part rc;
run;
Ask a Question
Discussion stats
  • 6 replies
  • 166 views
  • 4 likes
  • 5 in conversation