BookmarkSubscribeRSS Feed
drkongala
Calcite | Level 5

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

6 REPLIES 6
Cynthia_sas
SAS Super FREQ

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

patelshree
Fluorite | Level 6

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;

 

drkongala
Calcite | Level 5

Thankyou for your response

 

art297
Opal | Level 21

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

 

patelshree
Fluorite | Level 6

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;
Ksharp
Super User

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;

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
  • 6 replies
  • 618 views
  • 4 likes
  • 5 in conversation