BookmarkSubscribeRSS Feed
Born_Aioli
Calcite | Level 5

Hi,

I am not very used to code in SAS. My background is in R. But I want to do the following thing and I could need some help:

Imagine the following data:

SubjectId Seq ActivityId
1         1   A
1         2   B 
2         1   A 
2         2   B 
2         3   C
2         4   D 
2         5   A 
3         1   A 
3         2   C 
3         3   D 
4         1   C 
4         2   B 

As you can see I have (different) many rows per unique SubjectID. I have two conditions that I want to check if fulfilled:

  1. Imagine that Seq is a visit at the doctor. In total, there are 6 possible doctor visits(Seq: 1-6), and I want to check if every unique SubjectID has been to the doctor all 6 visits or if some is missing?

  2. I want to see if every unique SubjectId has done ActivityId A and B.

Do you know how to do this in SAS? Please feel free do ask if you miss any information.

6 REPLIES 6
ballardw
Super User

What do you expect the output to look like?

A data set (for further processing) a report (people read these)?

Born_Aioli
Calcite | Level 5

The optimal output is a new dataset where the unique SubjectId:s that are not fullfilling the 2 conditions are presented: including two new variables that tell us what conditions are not met, e.g. "Seq_missing" that includes what sequences that are missing (1-6) and "ActivityId_missing" that includes what ActivityId that is missing (A or/and B)

yabwon
Onyx | Level 15

In that case my previous code requires only one DoW-loop:

data have;
input SubjectId Seq ActivityId $;
cards;
1 1 A
1 2 B 
2 1 A 
2 2 B 
2 3 C
2 4 D 
2 5 A
2 6 A  
3 1 A 
3 2 C 
3 3 D 
4 1 C 
4 2 B 
;
run;

data want;
keep SubjectId allVisits activity;
allVisits = sum(1,2,3,4,5,6);
activityA = 0;
activityB = 0;
do until(last.SubjectId);
  set have;
  by SubjectId;
  allVisits + (-Seq);
  activityA + (ActivityId = "A"); drop activityA;
  activityB + (ActivityId = "B"); drop activityB;
end;
allVisits = (not allVisits);
activity = activityA and activityB;
if not(activity and allVisits);
run;
proc print;
run;

 

All the best

Bart

_______________
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation



ed_sas_member
Meteorite | Level 14

Hi @Born_Aioli 

Here is an attempt to achieve this:

data have;
	input SubjectId Seq ActivityId $;
	datalines;
1         1   A
1         2   B 
2         1   A 
2         2   B 
2         3   C
2         4   D 
2         5   A 
3         1   A 
3         2   C 
3         3   D 
4         1   C 
4         2   B 
;
run;

/* ActivityId_missing */
proc transpose data=have out=have_tr;
	var ActivityId;
	by SubjectId;
run;

data have2 (keep=SubjectId ActivityId_missing);
	set have_tr;
	length ActivityId_missing $10.;
	if findc(catx("",of col:),"A")>0 and findc(catx("",of col:),"B")>0 then ActivityId_missing = "";
	else if findc(cats("",of col:),"A")>0 then ActivityId_missing = "B";
	else if findc(cats("",of col:),"B")>0 then ActivityId_missing = "A";
	else ActivityId_missing = "A and B";
run;

/* Seq_missing */
proc sql;
	create table have3 as
	select SubjectId, case when count(Seq)+1<6 then catx("-",put(count(Seq)+1,best.),"6") else "" end as Seq_missing
	from have
	group by SubjectId;
quit;

/* Final table */
proc sql;
	create table want as
	select a.SubjectId,b.Seq_missing,a.ActivityId_missing from
	have2 as a inner join have3 as b
	on a.SubjectId=b.SubjectId;
quit;

Capture d’écran 2020-03-03 à 17.08.54.png

Born_Aioli
Calcite | Level 5

The first condition works fine! 

 

According to the second condition, is it possible to get the output for the missing steps like: 3,4,5,6 instead of 3-6? It is because I cannot be sure that the answer wouldn't be: 3,4,6 (like if visit 5 isn't done). I cannot figure out how to do it. 

 

Thanks a lot! 

 

yabwon
Onyx | Level 15

Hi @Born_Aioli ,

 

it looks like good example for double DoW-loop:

 

data have;
input SubjectId Seq ActivityId $;
cards;
1 1 A
1 2 B 
2 1 A 
2 2 B 
2 3 C
2 4 D 
2 5 A
2 6 A  
3 1 A 
3 2 C 
3 3 D 
4 1 C 
4 2 B 
;
run;

data want;

allVisits = sum(1,2,3,4,5,6);
activityA = 0;
activityB = 0;
do until(last.SubjectId);
  set have;
  by SubjectId;
  allVisits + (-Seq);
  activityA + (ActivityId = "A"); drop activityA;
  activityB + (ActivityId = "B"); drop activityB;
end;
allVisits = (not allVisits);
activity = activityA and activityB;

do until(last.SubjectId);
  set have;
  by SubjectId;
  output;
end;

run;
proc print;
run;

Read about it in @hashman article here: http://support.sas.com/resources/papers/proceedings13/126-2013.pdf

 

All the bets

Bart

_______________
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation



Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
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
  • 672 views
  • 0 likes
  • 4 in conversation