topic Re: Help with data manipulation in data step in SAS Procedures

Help with data manipulation in data step

LucyLou — Sun, 28 Apr 2013 18:26:58 GMT

Hi All,

I am trying to link records in my data by whether a few conditions apply. I want to create a new variable to determine contact with an infected classmate. I have my data so that for every day a student is in a particular school there is a separate record. I want to create a new variable "contact" that indicates whether a student was at the same school on the same day as any of the infected students (does not matter if it was one infected student or 10). I also need a way to prevent it saying it was in contact with an infected student if the infected student was himself (I hope that makes sense).

Please help!

Thank you in advance.

Re: Help with data manipulation in data step

lloydc — Sun, 28 Apr 2013 18:47:24 GMT

I may be mis-reading your question and if so, I apologize. But it sounds like you can have a dataset (healthy) with variables (school, student, date-attended) and another dataset (infected) with variables (school,infected-student,date-attended) then do a merge:

proc sort data=healthy; by school date-attended;

proc sort data=infected; by school date-attended;

data mergem;

set healthy; by school date-attended;

set infected; by school date-attended;

merge healthy (in=inh) infected (in=inf);

if inh and inf then output;

proc sort data=mergem; by student infected-student;

proc freq data=mergem; tables student*infected-student;

I think that would give you a table by healthy-student and each infected-student with the # of potential interactions for each combination.

Hope this helps.

Re: Help with data manipulation in data step

PGStats — Sun, 28 Apr 2013 19:29:33 GMT

I will make a slightly different guess about your data structure and assume you have a single dataset with variables School, Student, Date, and Contagious. The following query would give you useful information (untested) :

proc sql;

create table contact as

select

A.school,

A.student,

coalesce(count(distinct B.student), 0) as nbStudents,

min(B.date) as firstContact format=date9.,

max(B.date) as lastContact format=date9.

from

(select * from attendance where not contagious) as A left join

(select * from attendance where contagious) as B

on A.school=B.school and A.date=B.date and A.student ne B.student

group by A.school, A.student;

quit;

Re: Help with data manipulation in data step

LucyLou — Mon, 29 Apr 2013 03:52:27 GMT

I think this worked! Thank you so much.