BookmarkSubscribeRSS Feed
rockerd
Fluorite | Level 6
Hi all,

I was running regression analysis for which I am taking a macro variable from the SAS enterprize miner prompt window.

Suppose following is the dataset and the code.

options symbolgen mprint mlogic;

DATA survey;
INPUT id sex $ age inc r1 r2 r3 ;
DATALINES;
1 F 35 17 7 2 2
17 M 50 14 5 5 3
33 F 45 6 7 2 7
49 M 24 14 7 5 7
65 F 52 9 4 7 7
81 M 44 11 7 7 7
2 F 34 17 6 5 3
18 M 40 14 7 5 2
34 F 47 6 6 5 6
50 M 35 17 5 7 5
;

%macro reg21;
proc sort data = work.survey; by sex; run;

PROC reg DATA=work.survey;

%if (® = ) %then %do; call symputx('commented', %str(*)); %end;
%else %do; call symput('commented', ' ') %end;

model age=inc r1 r2 r3;
&commented by sex;
RUN;
%mend reg21;
%reg21;

I want regression analysis done is such a way that if the value of macro variable reg from prompt is null then perform it on whole dataset
otherwise perform it by sex F and M. I thought this code would work however, it is not.

It would be great if you guys could help me with this.

Thank you,

Rockerd.
3 REPLIES 3
Cynthia_sas
SAS Super FREQ
Hi:
I'm not sure you need CALL SYMPUT in this instance. Your instinct to test &REG with a %IF statement is good. I would probably take a slightly different approach. I would just set &COMMENTED to either be a whole BY statement or NOT. This would also allow you to conditionally call the PROC SORT. I'm not sure how &REG is getting set in Enterprise Miner, but in your macro program, &REG needs to be set immediately before the %REG21 macro is called, as shown in the modified example below.

Remember that the Macro facility is just acting like a big typewriter and the %LET statement will assign a value to &COMMENTED when the macro program is first invoked. Then the actual value for &COMMENTED is not "typed" into the PROC REG part of the program until the macro word scanning phase for the code within the PROC REG. Generally speaking, you use the CALL SYMPUT(X) within a DATA _NULL_ program because you want to grab a value from a running DATA step program and create a macro variable. But, in your case, you just need for the macro program itself to assign the appropriate value to &COMMENTED based on the value of &REG -when the macro program %REG21 is invoked-. So, a simple %LET will work in this case.

Also remember that since the macro program is just generating code that gets sent onto the compiler, you can conditionally execute the PROC SORT, as well, by moving it into the %IF statement logic. In your previous example, you were ALWAYS going to do the sort, whether you were using the BY statement or not.

Note that I added a fake variable called OTHER to your data in order to illustrate how you might use the conditional macro logic with %IF and %LET to insert a BY statement and a sort for a different variable. I realize that the OTHER variable is probably not going to result in any meaningful analysis in terms of PROC REG -- but it is just for illustration purposes.

If you are always going to control the invocation of this macro program, then this conditional logic would probably be sufficient. However, if someone else could possibly use a bogus value for &REG, like

%let reg = xyz;
%reg21;


Then you might want to add a final %ELSE condition to the macro program code:
[pre]
%else %do;
%let commented =;
%put ^^^^^ ^^^^^ ^^^^^ WRONG;
%put valid values for REG macro variable are:;
%put nothing, use_sex or use_oth;
%put ^^^^^ ^^^^^ ^^^^^ WRONG;
%end;
[/pre]

cynthia
[pre]
options symbolgen mprint mlogic;

DATA survey;
INPUT id sex $ age inc r1 r2 r3 other $;
DATALINES;
1 F 35 17 7 2 2 a
17 M 50 14 5 5 3 a
33 F 45 6 7 2 7 a
49 M 24 14 7 5 7 a
65 F 52 9 4 7 7 a
81 M 44 11 7 7 7 b
2 F 34 17 6 5 3 b
18 M 40 14 7 5 2 b
34 F 47 6 6 5 6 b
50 M 35 17 5 7 5 b
;
run;

%macro reg21;

%if (® = ) %then %do;
%let commented = ** No By Stmt ;
%end;
%else %if ® = use_sex %then %do;
%let commented = by sex;
proc sort data = work.survey; by sex; run;
%end;
%else %if ® = use_oth %then %do;
%let commented = by other;
proc sort data = work.survey; by other; run;
%end;

%put for this run REG=® commented=&commented;

PROC reg DATA=work.survey;
title "For macro var= &REG and commented=&commented";
model age=inc r1 r2 r3;
&commented ;
RUN;
quit;
%mend reg21;

** should be whole dataset (BY commented out);
%let reg =;
%reg21;

** should be by sex (BY SEX in effect);
%let reg = use_sex;
%reg21;

** should be by other (BY OTHER in effect);
%let reg = use_oth;
%reg21;
[/pre]
rockerd
Fluorite | Level 6
Thank you so much for your immediate reply to my query. After trying a few tweaks I was able to get it running.

:)

In addition to that I had one more question ...

Is there any way by which I can export the multiple regression models I got into excel file at different sheets within the same Excel file.

Again thank you Cynthia@sas.

Regards,

Rockerd
Cynthia_sas
SAS Super FREQ
Hi;
The only way to make a multi-sheet workbook is to use
1) the LIBNAME engine for Excel or PROC EXPORT (but those need a SAS dataset) or
2) use ODS TAGSETS.EXCELXP to make an XML file (Spreadsheet Markup Language XML from Excel 2003 specification) that Excel knows how to open and render.

The only "hitch" in this scenario is that while TAGSETS.EXCELXP can make an XML version of a multi-sheet workbook, it does have 1 limitation. When you try to put graphic output into the XML file, such as with PROC GCHART, TAGSETS.EXCELXP gives you the following message:
Excel XML does not support output from Proc:Gchart
Output will not be created.


because, by MICROSOFT design, the XML file CANNOT contain graphical output from SAS (or any graphical output, like a logo, for that matter). So as long as you only want the report TABLES from PROC REG analysis, you'll be OK.

The basic way to invoke TAGSETS.EXCELXP given the above PROC REG code and macro invocation is this:
[pre]
ods tagsets.excelxp file='c:\temp\whole_regress.xls' style=sasweb
options(doc='Help');
** should be whole dataset (BY commented out);
%let reg =;
%reg21;

** should be by sex (BY SEX in effect);
%let reg = use_sex;
%reg21;

** should be by other (BY OTHER in effect);
%let reg = use_oth;
%reg21;

ods tagsets.excelxp close;
[/pre]

The doc='Help' suboption will put a list of valid suboptions into the SAS log. Note that you must have at least SAS 9 to use TAGSETS.EXCELXP and at least Excel 2003 to open and render the XML file correctly.

Also note the fact that I give the file an extension of .XLS for convenience purposes only -- so Windows will launch Excel when I double-click on the file name, c:\temp\whole_regress.xls -- I am merely "fooling" the Windows registry with this file extension trick. The actual file extension is .XML (because TAGSETS.EXCELXP creates an XML file, but if you double-click on a file that has an extension of XML, usually a browser will try to open the file. (The correct file extension for files created with TAGSETS.EXCELXP is either .XML or .XLS -- do not be tempted to name the file .XLSX -- that file extension is reserved for a different "flavor" of Excel XML (Office 2007). TAGSETS.EXCELXP creates Office 2003 "flavor" of XML.)

Also note that when you open the XML file with Excel 2007 or 2010, you may receive a warning message similar to the one described in this Tech Support note (note describes the situation when you give an HTML file the extension of .XLS):
http://support.sas.com/kb/31/956.html

For more information about how to use some of the TAGSETS.EXCELXP sub-options, this is an excellent user-group paper:
http://www.nesug.org/proceedings/nesug08/ap/ap06.pdf

For more information and a good overview of SAS Macro processing, this is an excellent paper:
http://www2.sas.com/proceedings/sugi28/056-28.pdf

cynthia

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 783 views
  • 0 likes
  • 2 in conversation