topic SAS to output a column of numeric values that are stored in macro variable in SAS Programming

SAS to output a column of numeric values that are stored in macro variable

SeekYourWay — Thu, 10 May 2012 18:31:06 GMT

Hi there,

this is my main program, which calls a SAS macro:

What I would like to do:

I need to populate the column "CT" in the Excel file with numeric numbers, which are essentially the value of the variable created in this SAS program. Please refer to the Excel file attached.

In worksheet "Test 2", you will see column CT, which already has values generated, but this is just test data. I would actually need to run this SAS program to be able to generate those numbers in column CT so that I can test and see if they match with what I already have in the Excel file. (The program needs to apply to every line of record in Excel, as you can tell).

Can the SAS program do that for me? If it doesn't output to Excel, that's fine,but the bottom line is I need to have column CT populated with numbers as a result of running above SAS code in SAS.

Is this possible? Is anyone able to assist me on this please? I'm new to SAS macro

Thanks very much

Re: SAS to output a column of numeric values that are stored in macro variable

cwilson — Thu, 03 Jan 2013 15:11:06 GMT

Actually, I think you may be almost there. It looks like all you have to do is output your work dataset to an excel file. (Without some actual test data, I can't test for sure.)

I have added a few comments to your main code.

%Main Program;

DATA test;

SET sasuser.test;

%A01_CPS_MH;

run ; * I recommend putting a run statement at the end of every data step, for clarity as well as to ensure execution if no other steps follow this one;

proc export data = test outfile ="C:\mySASfolder\myTestScoreResults.xlsx" DBMS=Excel2007 replace ;

run ;

%MEND scipp_2012_program; * this statement is outside of your data step ;

Re: SAS to output a column of numeric values that are stored in macro variable

art297 — Thu, 03 Jan 2013 17:52:10 GMT

I agree with cwilson that your program appears to do what you want. You don't really need to wrap the code in SAS macros, as you could have just included the code in a data step.

You also appear to have written scipp_2012_program as a SAS macro. Again, not necessary, but it will still work that way. However, if you are going to keep it as a macro, you have to change the first line and then call the macro.

Thus, stealing Carla's suggested code, you could use:

%macro scipp_2012_program;

DATA test;

SET sasuser.test;

%A01_CPS_MH

run ;

proc export data = test outfile ="C:\mySASfolder\myTestScoreResults.xlsx" DBMS=Excel2007 replace ;

run ;

%MEND scipp_2012_program;

%scipp_2012_program