Problem with nested macro do loop

[Eileen1496]

Hi all,

I'm trying to modify one of my code to a nested loop. Below is the code I have. However, when I submit the code, sas did not start running (black text of my code, no blue output nor green warning nor red error)

%macro repeat_code2; 
%do i = 1 %to 9;
/* Create a dataset with unique record_num values */
proc sql noprint;
    select distinct record_num
    into :record_nums separated by ' '
    from user_2&i._record_num;
quit;

/* Macro to split data into separate datasets based on record_num */
proc sql noprint;
    select max(record_num)
    into :max_num
    from user_2&i._record_num;
quit;


    %do j = 1 %to &max_num;
        data ework.user2&i._uniq_pt&j;
            set user_2&i._record_num;
            where record_num = &j;
        run;
		    
	data user2&i._uniq_pt&j (compress=yes);
	    set ework.user2&i._uniq_pt&j (drop = record_num);
	    run;
	%end;


/* get user_year_list */
data user_2&i._yr_list(compress=yes);
set ework.user_2&i._nqtr_info_sl(keep=user_id year);
run;

proc sort data = user_2&i._yr_list nodupkey;
by _all_;
run;

/* merge in information i have in wide by year and user_id */
proc sort data = user_2&i._yr_list;
by user_id year;
run;
proc sort data = user2&i._uniq_pt1;
by user_id year;
run;

/* Merge the datasets */
data clcareer.user_2&i._career_wide (compress=yes);
    merge user_2&i._yr_list (in=a)
          user2&i._uniq_pt1;
    by user_id year;
    if a; /* Keep only observations from user_2_yr */
run;


data clcareer.user_2&i._career_wide;
    set clcareer.user_2&i._career_wide;
    rename 
        nqtr = nqtr1
        mapped_role = mapped_role1
        rcid = rcid1
		salary = salary1;
run;

proc delete data = user_2&i._yr_list;
run;



%do j = 2 %to &max_num;

proc sort data = user2&i._uniq_pt&j;
by user_id year;
run;

/* Merge the datasets */
data clcareer.user_2&i._career_wide (compress=yes);
    merge clcareer.user_2&i._career_wide (in=a)
          user2&i._uniq_pt&j;
    by user_id year;
    if a; /* Keep only observations from user_2_yr */
run;


data clcareer.user_2&i._career_wide;
    set clcareer.user_2&i._career_wide;
    rename 
        nqtr = nqtr&j
        mapped_role = mapped_role&j
        rcid = rcid&j
		salary = salary&j;
run;
%end;
%end;
%mend;
%repeat_code2;

At first, I thought it might be me missing end or mend or repeat_code2 somewhere, but when I try a smaller piece of code below, it works.

%macro repeat_code; 
%do i = 1 %to 9;
    proc sql noprint;
        select max(record_num)
        into :max_num
        from user_2&i._record_num;
    quit;

    %do j = 1 %to &max_num;
        data ework.user2&i._uniq_pt&j;
            set user_2&i._record_num;
            where record_num = &j;
        run;
    %end;
%end;
%mend;
%repeat_code;

So could you tell me where my code is wrong?

Thank you

[Tom]

What is the purpose of the code?  The comments do not seem to match the code.  For example this first comment says

/* Create a dataset with unique record_num values */

But the code after that is just making MACRO variables, not a dataset.

[Eileen1496]

the code first enumerate the number of records each user_id has, then get the max_num of the records, say the user with the most number of records has 79 records, this max_num will be 79. Then there is a nest loop that go from 1 to 79, and split the data into 79 parts. Then I merge these part horizontally together.

I tried the code in charge of these functions on one dataset, and it worked as I wanted. So my problem is just the format of nested loop.

[Tom · Accepted solution]

---

@Eileen1496 wrote:

the code first enumerate the number of records each user_id has, then get the max_num of the records, say the user with the most number of records has 79 records, this max_num will be 79. Then there is a nest loop that go from 1 to 79, and split the data into 79 parts. Then I merge these part horizontally together.

I tried the code in charge of these functions on one dataset, and it worked as I wanted. So my problem is just the format of nested loop.

---

Ok. So that is a blow by blow description of the steps.  But what is the purpose?  

The main reason I ask is because it really seems like a strange approach and I cannot figure out what you are trying to do so I can suggest a more direct approach, perhaps one that does not involve splitting data and merging it back together.

What is the issue with running the code?  Your description of the failure seemed to imply that the macro did not compile and so it did not run.  But when I copy your code and run it the macro did compile and run.  It failed of course since I do not have any of the datasets it references.

If it is not running for you then I suggest starting a new SAS session and submitting the macro definition and call again. Perhaps you just have left SAS in an unstable state with unbalanced quotes (or other unbalanced blocks like comments of macro definitions).  Turn on MPRINT to make sure you see the SAS code that the macro does generate.

[PaigeMiller]

Code begins executing for me when I run it. Of course, I get the expected errors because I don't have your data sets.

Please add debugging options and run your macro and then show us the LOG (down to the first error).

options mprint symbolgen mlogic;
%repeat_code2

Let's not jump to conclusions that it is the nesting that is the problem.

[Tom]

This code:

data clcareer.user_2&i._career_wide;
    set clcareer.user_2&i._career_wide;
    rename 
        nqtr = nqtr1
        mapped_role = mapped_role1
        rcid = rcid1
		salary = salary1;
run;

makes it look like the object is to transpose the data.

If so you should be able to do that without pulling it apart and pasting it back together.  Something like:

data want;
  do until(last.user_id);
    set have;
    by user_id year;
    array _nqtr [2000:2024] nqtr2000-nqtr2024;
    array _salary [2000:2024] salary2000-salary2024;
    array _rcid [2000:2024] rcid2000-rcid2024;
    array _mapped_role [2000:2024] mapped_role2000-mapped_role2024;
    _nqtr[year] = nqtr1;
    _salary[year]=salary1;
    _rcid[year]=rcid1;
    _mapped_role[year]=mapped_role1;
  end;
run;

Then the only code modification you might want would be to replace the upper and lower bounds of the set of year values with macro variables.

[Eileen1496]

@Tom @PaigeMiller Hi Tom and Paige, thanks for all the comments. It took me a while to reply because I was suspecting the SAS somehow enter a freeze state since my code is only submitted but not run. So I restarted SAS and it took me a while to rerun the previous temp data sets. Then my remote computer had some problems that were fixed until now. 

I add the debug option at the beginning and rerun the nested loop, turns out it works as expected! But the debug line is very helpful for me.