topic Re: Appending dataset in Macro loop in SAS Programming

Appending dataset in Macro loop

dwights — Mon, 01 Apr 2019 20:57:05 GMT

I'm trying to create a table of all macro variables which follow a particular condition.

%do i=1 to &N;

%let variable = %scan(&varlist,&i); 



*/

*  Code

*/

         %if(condition) %then %do;

         data tempSignificance;
         SignificantColumn="&variable";
         run;



        proc append base=FinalSignificance data=tempSignificance ;
        run;

        %end;



proc sql;
drop table tempSignificance;
quit;

 %end;

The above code is creating the following table -

SignificantColumn	CLSTR_NM
.	.

But I want the table in the following format -

SignificantColumn

CLSTR_NM

Can someone please let me know. TIA

Re: Appending dataset in Macro loop

ballardw — Mon, 01 Apr 2019 21:24:15 GMT

The above code is creating the following table -

SignificantColumn CLSTR_NM

. .

Which data set?

I suspect that you may have created your FinalSignificance data set in a manner that has the variable SignificantColumn as numeric.

But you are starting in the middle and missing lots of parts such as

1) the complete text of macro code

2) the actual value of your &varlist macro variable,

3) your macro loop is missing the % for %to so I suspect your macro may have actually failed to compile and has not replaced an earlier version of a set

Question:

Did you have code that did this successfully without any macro bits? If so, look at it closely. If not, create. Add one piece at a time.

Another issue you may have is the length of the variable SignificantColumn. If the initial value is less than later values you are either going to get failure of appends due to different lengths or truncation of data.

You can also save a lot of unneeded temporary data set with something like:

data tempSignificance;
   length  SignificantColumn $ 32;
   %do i=1 %to &n.;
      %let variable = %scan(&varlist.,&i.); 
      SignificantColumn="&variable.";
      output;
   %end;
 run;

which should assign all of the variables into a single data set.

But where did you build your &varlist? It may be that you already had the pieces you needed and have spent additional time taking them out of a source such as Dictionary.columns just to place them back into another table. If that is the approach you used then perhaps evaluating the conditions first and selecting from Dictionary.Columns for the append would be appropriate.

Re: Appending dataset in Macro loop

Astounding — Mon, 01 Apr 2019 22:23:06 GMT

I agree with everything @ballardw said, including the importance of using a LENGTH statement.

The results you got are consistent with accidentally removing the quotes in this portion of the program:


         data tempSignificance;
         SignificantColumn="&variable";
         run;

Re: Appending dataset in Macro loop

dwights — Tue, 02 Apr 2019 03:05:15 GMT

	SignificantColumn="&variable";

When I put in the quotes, I get this error -

WARNING: Variable SignificantColumn not appended because of type mismatch.
WARNING: Variable CLSTR_NM was not found on DATA file.
ERROR: No appending done because of anomalies listed above. Use FORCE option to append these files.

Re: Appending dataset in Macro loop

dwights — Tue, 02 Apr 2019 03:12:55 GMT

The varlist contains list of all column names of a table (got the list using proc contents) . CLSTR_NM is a column of that table which satisfies the if condition(which is a t-test result being significant) . What I'm trying to do in the code is get the list of all columns which are statistically significant when compared from two tables. Thanks!

Re: Appending dataset in Macro loop

Kurt_Bremser — Tue, 02 Apr 2019 08:09:35 GMT

Step 1 of macro development: start with working Base SAS code.

Create the code that works with one instance from your varlist. Once that works without WARNINGs/ERRORs, start making it dynamic.

Re: Appending dataset in Macro loop

gamotte — Tue, 02 Apr 2019 08:55:07 GMT

Hello,

/* Random values for the example */
data _NULL_;
    array a a1-a10;
    do over a;
        a=10*rand("Uniform");
        call symputx(vname(a),a);
    end;
run;

data FinalSignificance;
    length SignificantColumn $5.;
    call missing(SignificantColumn);
    stop;
run;


data _NULL_;
    array mv $5. a1-a10; /* List the macrovariables names here */

    call execute('proc sql;');

    do over mv;
        mv=symget(vname(mv));

        if mv>5 then do;
            call execute(cats('insert into FinalSignificance set SignificantColumn="',mv,'";'));
        end;
    end;

    call execute('quit;');
run;

Re: Appending dataset in Macro loop

ballardw — Tue, 02 Apr 2019 15:37:43 GMT

@dwights wrote:
	SignificantColumn="&variable";
When I put in the quotes, I get this error -

WARNING: Variable SignificantColumn not appended because of type mismatch.
WARNING: Variable CLSTR_NM was not found on DATA file.
ERROR: No appending done because of anomalies listed above. Use FORCE option to append these files.

Which tells that SignificantColumn in the BASE data set is numeric. Run proc contents on the data set to verify variable types and lengths.

Re: Appending dataset in Macro loop

ballardw — Tue, 02 Apr 2019 15:39:36 GMT

@dwights wrote:

The varlist contains list of all column names of a table (got the list using proc contents) . CLSTR_NM is a column of that table which satisfies the if condition(which is a t-test result being significant) . What I'm trying to do in the code is get the list of all columns which are statistically significant when compared from two tables. Thanks!

So show the code how you actually derived that list.

And possibly more important at this point since you keep getting append errors: Code for how you build the BASE dataset you are using in the Proc Append code.