DATA Step, Macro, Functions and more

Folks , any suggestions on how to reduce the second part of the code?(after the sqlloop)

Reply
New Contributor
Posts: 3

Folks , any suggestions on how to reduce the second part of the code?(after the sqlloop)

%macro sqlloop(begin,stop);
/* &exam is the variable for looping through the eight exams of the cohort */
PROC SQL noprint;
  %DO exam=&begin. %TO &stop.;
   %let i=%eval(&exam. +1);

   /* %put the value of i is &i;
       %let k=%eval(&exam + 2); */
   CREATE TABLE Gout_miss_&exam. as
    SELECT t1.sample,  t1.shareid, t1.exams, t1.cohort, t1.gout_inci, count(*) as  GoutMiss_&exam.
     FROM work.FRAMINGHAM_CLEANDATA_1 t1 where (t1.exams = &exam.) and (t1.gout_inci = 'NA')
   ;
   CREATE table gout_inci&exam. as
    SELECT t1.sample,  t1.shareid, t1.exams, t1.cohort, t1.gout_inci, count (*) as GoutInci_&exam.
     FROM work.FRAMINGHAM_CLEANDATA_1 t1 where (t1.exams = &exam.) and (t1.gout_inci = '1');
  %END;
%mend;

%sqlloop(begin=1, stop=8 );

proc sql;
create table Gout_miss_12  as
  SELECT A.sample,  A.shareid, A.exams, A.cohort, A.gout_inci, count(*) as  GoutMiss_
   from Gout_miss_1  A
    inner join Gout_miss_2  B on (A.sample = B.sample)
    inner join Gout_inci3 t1 on (A.sample= t1.sample);
create table Gout_miss_123  as
  SELECT A.sample,  A.shareid, A.exams, A.cohort, A.gout_inci, count(*) as  GoutMiss_
   from Gout_miss_1  A
    inner join Gout_miss_2  B on (A.sample = B.sample)
    inner join Gout_miss_3  C on (A.sample = C.sample)
    inner join Gout_inci4 t1 on (A.sample= t1.sample);
create table Gout_miss_1234  as
  SELECT A.sample,  A.shareid, A.exams, A.cohort, A.gout_inci, count(*) as  GoutMiss_
   from Gout_miss_1  A
    inner join Gout_miss_2  B on (A.sample = B.sample)
    inner join Gout_miss_3  C on (A.sample = C.sample)
    inner join Gout_miss_4  D on (A.sample = D.sample)
    inner join Gout_inci5 t1 on (A.sample= t1.sample);
create table Gout_miss_12345  as
  SELECT A.sample,  A.shareid, A.exams, A.cohort, A.gout_inci, count(*) as  GoutMiss_
   from Gout_miss_1  A
    inner join Gout_miss_2  B on (A.sample = B.sample)
    inner join Gout_miss_3  C on (A.sample = C.sample)
    inner join Gout_miss_4  D on (A.sample = D.sample)
    inner join Gout_miss_5  E on (A.sample = E.sample)
    inner join Gout_inci6 t1 on (A.sample= t1.sample);
create table Gout_miss_123456  as
  SELECT A.sample,  A.shareid, A.exams, A.cohort, A.gout_inci, count(*) as  GoutMiss_
   from Gout_miss_1  A
    inner join Gout_miss_2  B on (A.sample = B.sample)
    inner join Gout_miss_3  C on (A.sample = C.sample)
    inner join Gout_miss_4  D on (A.sample = D.sample)
    inner join Gout_miss_5  E on (A.sample = E.sample)
    inner join Gout_miss_6  F on (A.sample = F.sample)
    inner join Gout_inci7 t1 on (A.sample= t1.sample);
create table Gout_miss_1234567  as
  SELECT A.sample,  A.shareid, A.exams, A.cohort, A.gout_inci, count(*) as  GoutMiss_
   from Gout_miss_1  A
    inner join Gout_miss_2  B on (A.sample = B.sample)
    inner join Gout_miss_3  C on (A.sample = C.sample)
    inner join Gout_miss_4  D on (A.sample = D.sample)
    inner join Gout_miss_5  E on (A.sample = E.sample)
    inner join Gout_miss_6  F on (A.sample = F.sample)
    inner join Gout_miss_7  G on (A.sample = G.sample)
    inner join Gout_inci8 t1 on (A.sample= t1.sample);
quit;

Super User
Posts: 17,784

Re: Folks , any suggestions on how to reduce the second part of the code?(after the sqlloop)

Why don't you explain what you're trying to do instead, there may be a better way overall.

New Contributor
Posts: 3

Re: Folks , any suggestions on how to reduce the second part of the code?(after the sqlloop)

I  generate subsets of data-tables  from one table using the variable exam(1to 8) with two possibilities each time(gout_inci = 'na' or 1).

After I get these tables generated I  inner join them to generate new tables. This code works. But I was wondering if I could do the inner join portion using some kind of loop. that's all

Super User
Posts: 10,483

Re: Folks , any suggestions on how to reduce the second part of the code?(after the sqlloop)

I believe you have described what you are currently doing not why. What are you doing with the resulting tables?

I think what you are doing is possibly an odd way of selecting records for counting, which might be doable by creating a series of index variables and summing those. Which could also be used for subsetting if the data is then fed into other steps.

New Contributor
Posts: 3

Re: Folks , any suggestions on how to reduce the second part of the code?(after the sqlloop)

I just need the record count after the inner joins to predict the
subjects who entered the study with gout.  for example the first inner join intersects where gout_inci was 'NA' in exams 1 & 2 but had gout_inci = 1 in exam 3.  Then I repeat the same  upto exam 8.

Super User
Posts: 10,483

Re: Folks , any suggestions on how to reduce the second part of the code?(after the sqlloop)

So you are looking across multiple records and want to identify if the subject had gout at any of the exams? Or which exam number that gout first appeared?

Super User
Super User
Posts: 6,498

Re: Folks , any suggestions on how to reduce the second part of the code?(after the sqlloop)

Are you trying to count how many people first developed GOUT by each EXAMS value?

data want ;

do until (last.SAMPLE);

  set work.FRAMINGHAM_CLEANDATA_1

  by SAMPLE EXAMS ;

  if GOUT_INCI='1' and missing(first_gout) then first_gout=EXAMS;

end;

keep SAMPLE FIRST_GOUT ;

run;

proc freq ;

tables first_gout;

run;

Ask a Question
Discussion stats
  • 6 replies
  • 240 views
  • 0 likes
  • 4 in conversation