proc sql / do loop

lt1 — Tue, 27 Nov 2018 03:10:19 GMT

I have a set of numbers that I need to collect the count for. Essentially, I need to perform :

proc sql;
create table lala&i as
select count(*)
from singlefamilyhht
where persons=4 and hinc<.8*(i)
or
persons=3 and hinc< .9*(.8*(i))
or
 persons=2 and hinc< .8*(.8*(i))
or
 persons=5 and hinc< 1.08*(.8*(i))
or
persons=6 and hinc< 1.16*(.8*(i))
group by puma1;
quit;

repeatedly for different medians (@i).

I wrote the following code:

%macro data;
%let median = {61000.00 68000.00 68400.00 88000.00 77400.00 84040.00 103000.00 65400.00 47800.00 75000.00 92000.00 80000.00 99000.00 64400.00 79300.00};
%do i = 61000 %to &median;
proc sql;
create table lala&i as
select count(*)
from singlefamilyhht
where persons=4 and hinc<.8*(i)
or
persons=3 and hinc< .9*(.8*(i))
or
 persons=2 and hinc< .8*(.8*(i))
or
 persons=5 and hinc< 1.08*(.8*(i))
or
persons=6 and hinc< 1.16*(.8*(i))
group by puma1;
quit;
%end;
%mend data;

My log is as follows:

1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

73 %macro data;

74 %let median = {61000.00 68000.00 68400.00 88000.00 77400.00 84040.00 103000.00 65400.00 47800.00 75000.00 92000.00

74 ! 80000.00 99000.00 64400.00 79300.00};

75 %do i = 61000 %to &median;

76 proc sql;

77 create table lala&i as

78 select count(*)

79 from singlefamilyhht

80 where persons=4 and hinc<.8*(i)

81 or

82 persons=3 and hinc< .9*(.8*(i))

83 or

84 persons=2 and hinc< .8*(.8*(i))

85 or

86 persons=5 and hinc< 1.08*(.8*(i))

87 or

88 persons=6 and hinc< 1.16*(.8*(i))

89 group by puma1;

91 quit;

92 %end;

93 %mend data;

95 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

108

Any recommendations?

Re: proc sql / do loop

ChrisNZ — Tue, 27 Nov 2018 04:05:16 GMT

I suspect you want something like this:

%macro loop;
  %local i median medians;
  %let medians = 61000.00 68000.00 68400.00 88000.00 77400.00 84040.00 103000.00 65400.00 47800.00 75000.00 92000.00 80000.00 99000.00 64400.00 79300.00;
  %do i = 1 %to %sysfunc(countw(&medians,%str( )));
    %let median=%scan(&medians,&i,%str( ));
    create table LALA&i as 
    select count(*) as COUNT
          ,&median. as MEDIAN
          ,PUMA1
    from SINGLEFAMILYHHT
    where (PERSONS=2 and HINC <  .80   .8 * &median)
       or (PERSONS=3 and HINC <  .90 * .8 * &median)
       or (PERSONS=4 and HINC < 1.00 * .8 * &median)
       or (PERSONS=5 and HINC < 1.08 * .8 * &median)
       or (PERSONS=6 and HINC < 1.16 * .8 * &median)
    group by PUMA1;
  %end;
%mend ; 
       
proc sql;
  %loop;
quit;

topic proc sql / do loop in SAS Programming

proc sql / do loop

Re: proc sql / do loop