BookmarkSubscribeRSS Feed
kraman_uab_edu
Calcite | Level 5

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

6 REPLIES 6
Reeza
Super User

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

kraman_uab_edu
Calcite | Level 5

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

ballardw
Super User

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.

kraman_uab_edu
Calcite | Level 5

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.

ballardw
Super User

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?

Tom
Super User Tom
Super User

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;

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
  • 854 views
  • 0 likes
  • 4 in conversation