CODEWITHSUNDEEP
macrossas
Hung Ta
Hung Ta

Reputation: 53

Get out the value of a variable in each observation to a macro variable

I have a table called term_table containing the below columns

comp, term_type, term, score, rank

I go through every observation and at each obs, I want to store the value of variable rank to a macro variable called curr_r. The code I created below does not work

Data Work.term_table;
 input  Comp $
        Term_type $
        Term $
        Score
        Rank
      ;
 datalines;
 comp1 term_type1 A 1 1  
 comp2 term_type2 A 2 10
 comp3 term_type3 A 3 20
 comp4 term_type4 B 4 20
 comp5 term_type5 B 5 40
 comp6 term_type6 B 6 100
 ;
Run;

    %local j;
    DATA tmp;
    SET term_table;
    LENGTH freq 8;

    BY &by_var term_type term;
    RETAIN freq;

    CALL SYMPUT('curr_r', rank);
    IF first.term THEN DO;
        %do j = 1 %to &curr_r;
                         do some thing
                    %end;
    END;
RUN;

Could you help me to solve the problem

Thanks a lot

Hung

Upvotes: 1

Views: 1862

Answers (1)

Jay Corbett
Jay Corbett

Reputation: 28431

The call symput statement does create the macro var &curr_r with the value of rank, but it is not available until after the data step.

However, I don't think you need to create the macro var &curr_r. I don't think a macro is needed at all.

I think the below should work: (Untested)

DATA tmp;
 SET term_table;
 LENGTH freq 8;

 BY &by_var term_type term;
 RETAIN freq;

 IF first.term THEN DO;
  do j = 1 to rank;
   <do some thing>
  end;
 END;
RUN;

If you needed to use the rank from a prior obs, use the LAG function.

Start=Lag(rank);

To store each value of RANK in a macro variable, the below will do that:

Proc Sql noprint;
 select count(rank)
 into :cnt
 from term_table;

 %Let cnt=&cnt;

 select rank
 into :curr_r1 - :curr_r&cnt
 from term_table;
quit;

Upvotes: 1

Related Questions