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

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

kraman_uab_edu — Tue, 27 Jan 2015 21:20:01 GMT

%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;

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

Reeza — Tue, 27 Jan 2015 21:27:18 GMT

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

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

kraman_uab_edu — Tue, 27 Jan 2015 21:42:07 GMT

I generate subsets of data-tables from one table using the variable exam(1to 😎 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

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

ballardw — Tue, 27 Jan 2015 21:59:55 GMT

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.

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

kraman_uab_edu — Tue, 27 Jan 2015 22:07:07 GMT

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.

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

ballardw — Tue, 27 Jan 2015 22:19:44 GMT

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?

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

Tom — Tue, 27 Jan 2015 22:38:27 GMT

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;