topic Re: How to get a macro to pass dynamic var values to multiple spread sheets/apps in SAS Programming

How to get a macro to pass dynamic var values to multiple spread sheets/apps

madhatter — Wed, 24 Jun 2015 22:18:07 GMT

sput My SAS program produces an excel file for each of the college’s 7 academic departments. Each file contains a separate spread sheet for each academic major in that department with 95% of the requisite information. We currently manually edit in the remaining information for the college's 63 majors. The remaining information consists of three (3) values for each major (Grads,
Placed, PCT_Placed) and a list of graduates with multiple degrees that is included if graduates in that major have multiple degrees.

I created macros using symput (&GR &PL &PCTPL) to pass the correct placement values for use in the titles, but the macros picked up the values for the last department’s major and passed/ applied these values to all majors. The layout for the main data set (&pe..HUNTED)is: COLID, FName, LName, Home, Work_Day, Nite, Email, MD,DE, DR, NAICS, STATUS, DEPT, Major, GRADS, PLACED, PCT_PLACED. The layout for the data set multi-degree (Students who graduated from more than one program) is COLID, Maj1, Mal2, Maj3, Maj4, Maj5,Maj6, Maj7.). There is ONE observation for each student (COLID) with multiple degrees. If a student does not have multiple degrees, there is no observation.

The following contains the SAS code that produces the current reports and a copy of a mock output excel spread sheet for the AA department. The pe sas excerpt produces the excel
file for the AA department with the individual sheets for the two majors,AA and AS. The actual program runs this same code for each of the eight (8) departments. Apologies. but every attempt to provide the prog and results via attachment failed.

%include "C:\PROG
EVAL 2015\PE HEAD\SAS MACROS LIBS PE 2015.SAS";

PROC SORT DATA=&pe..HUNTED; BY DEPT MAJOR COLID;

DATA AAA HS BUS ET IND IST PS NUR;

SET HUNTED;

CALL SYMPUT ('GR',trim(left(put (GRADS,8.)))); CALL SYMPUT ('PL', trim(left(put(PLACED,8.))));

CALL SYMPUT ('PCT',trim(left(put(PCT_PLACED,8.))));

IF DEPT='AAA' THEN OUTPUT AA;;

IF DEPT='HS' THEN OUTPUT HS;

IF DEPT='BUS' THEN OUTPUT BUS;

IF DEPT='PS' THEN OUTPUT PS;

IF DEPT='ET' THEN OUTPUT ET;

IF DEPT='IND' THEN OUTPUT IND;

IF DEPT='IST' THEN OUTPUT IST;

IF DEPT='NUR' THEN OUTPUT NUR;

RUN;

/**AA Dept**/

ods tagsets.ExcelXP FILE="C:\PROGEVAL 2015\REPORTS\AAA 2013-14 Graduate Contacts testx.Xml" style=BARRETTSBLUE;

ods tagsets.ExcelXP options (autofit_height= 'yes' embedded_titles='yes' embedded_footnotes='yes' TITLE_FOOTNOTE_WIDTH='10' sheet_interval='bygroup' sheet_label=' ' frozen_headers= '3' absolute_column_width= '6,6,9,14,12,12,12,16,3,6,4,4,30' row_heights='0,10,0,0,0,0,0'
row_height_fudge='3' );

title;

footnote;

title1 font=timesRoman bold FONT='12PT' h=4 color=blue "2013-14
#BYVAL(MAJOR) Graduates Not Placed ";

title2 font=timesRoman h=4 color=blue " In
the Status Column, please use the following codes to indicate the
Graduates status";

title3 font=timesRoman h=4 color=blue "FT=Work FT in Job Related to Degree, PT= Work PT in Job Related to Degree, S=Full-time Student Fall 2014 I= NotSeeking Work/Active Duty";

title4 font=timesRoman h=4 color=BLACK "Graduates: &GR Placed: &PL Percent Placed: &PCT";

footnote1 font=timesRoman h=3 color=blue "http://www.census.gov/eos/www/naics/";

footnote2 font=timesRoman h=3 J=L color=blue "Note: A 'Y' in the MD column indicates
that the graduate has multiple degrees. If a graduate with multiple degrees is
'PLACED' for one one degree, ";

footnote3 font=timesRoman h=3 J=L color=blue " s/he is considered placed for all degrees. Multiple degree graduates for this program
and their other degrees are listed below:";

PROC REPORT DATA=AAA NOWD; BY major;

COLUMNS MAJOR COLID FNAME
LNAME Home Work_Day Nite Email MD STATUS DE DR NAICS ;

DEFINE MAJOR/ORDER WIDTH=5;

DEFINE COLID/ DISPLAY WIDTH=10;

DEFINE FNAME/DISPLAY WIDTH=20;

DEFINE LNAME/DISPLAY WIDTH=20;

DEFINE Home/DISPLAY WIDTH=12;

DEFINE Work_Day/DISPLAY WIDTH=12;

DEFINE Nite/DISPLAY WIDTH=12;

DEFINE EMAIL/DISPLAY WIDTH=45;

DEFINE MD/DISPLAY WIDTH=3;

DEFINE STATUS/DISPLAY WIDTH=6;

DEFINE DE/DISPLAY WIDTH=7;

DEFINE DR/DISPLAY WIDTH=7;;

DEFINE NAICS/ DISPLAY WIDTH=55;

BREAK AFTER MAJOR / PAGE;

RUN; ODS tagsets.ExcelXP close;

/**end**/

CALL SYMPUT ('GR', trim(left(put (GRADS,8.))));

CALL SYMPUT ('PL', trim(left(put(PLACED,8.))));

CALL SYMPUT ('PCT', trim(left(put(PCT_PLACED,8.))));

2013-14 AA Graduates Not
Placed

In the Status Column, please use the following codes to indicate
the Graduate's status

FT= Work FT in Job Related to Degree, PT= Work PT in Job Related
to Degree, S =Full-time Student Fall 2014
I= Not Seeking Work/Active Duty

Graduates: 222
Placed: 178 Percent
Placed: 80.0%

MAJOR

COLID

FNAME

LNAME

HOME

WORK_DAY

NITE

EMAIL

STATUS

naics

99999

Donald

Roeser

9999

777-

BOC@aol.com

812210 Funeral Homes

111111

Morris

Day

888

Dinowlk@hotmail.

713940 /Watch_Clock
mfg

2222

Jani

Lane

999-

cherries@yahoo.c

311811/bakeries

555555

Davis

Byron

999

888

stealer@yahoo.co

0211 /Cattle Ranching

3333

Buffett

James

999-

magrita@yahoo.c

336612/Boat building

http://www.census.gov/eos/www/naics/

Note: A 'Y' in the MD
column indicates that the graduate has multiple degrees. If a graduate with
multiple egrees is 'PLACED' for one one degree,

s/he is considered
placed for all degrees. Multiple
degree graduates for this program and their other degrees are listed below:

COLID

Major

99999

NUR

HLC6

555555

Re: How to get a macro to pass dynamic var values to multiple spread sheets/apps

Tom — Thu, 25 Jun 2015 03:35:00 GMT

Is there a question buried in there somewhere?

Re: How to get a macro to pass dynamic var values to multiple spread sheets/apps

madhatter — Thu, 25 Jun 2015 12:59:14 GMT

Sorry about that. . To summarize: I start with three data sets. DSN1 is based on the total graduates in a major and has 4 variables: Major, GRADS, PLACED, PCT_PLACED. DSN 2 has the graduate contact data for each non placed graduate (Major, Fname, phone data, et,). DSN3 has one orservation for each graduate who earned more than one degree; the layout is COLID, Maj1, Maj2, Maj3, Maj4, Maj5, Maj6, Maj7 where the values of Maj1-Maj7 are the vale of Major if applicable/null if not.

DSN1 and DSN 2 were merged on the var Major to produce the DSN -&pe..HUNTED which has the layout: COLID, FName, LName, Home, Work_Day, Nite, Email, MD,DE, DR, NAICS, STATUS, DEPT, Major, GRADS, PLACED, PCT_PLACED; There is one observation per individual graduate; the values for the GRADS, PLACED, PCT_PLACED variables are the same for every observation in a single major.

Question 1: What edits do I need to make to my program so it will put the correct GRADS, PLACED, PCT_PLACED values in the title of the spread sheet for each major?.

Question 2. How do I get SAS to put the graduates with multiple degrees in the footnote of the appropriate spreadsheet?

Thanks,

Charles

Re: How to get a macro to pass dynamic var values to multiple spread sheets/apps

Tom — Thu, 25 Jun 2015 14:39:13 GMT

So if your report is generated BY MAJOR and you have values for GRADS, PLACED, PCT_PLACED for each MAJOR then you can just change your BY statement to include the other three variables and they will be available to use in titles using #BYVAL().

Not sure how you want to handle the mutliple degree students.

Re: How to get a macro to pass dynamic var values to multiple spread sheets/apps

madhatter — Thu, 25 Jun 2015 16:28:32 GMT

K. I added the variables to my sort and added the &BYVAL() to my title statement, but it does not print the values in the excel file. After the data step creating the department data sets, I ran a proc freq for the major*grads*placed*pct_placed and got accurate results, so I know the data is there. I even tried sorting the AAA data independently before running the proc report. The LOG shows no errors. I added PCT_Placed as a variable and detailed it in the Proc Report and it printed out correctly.

Is something in my excelXP set-up scrogging this?. My code is below.

PROC SORT DATA=HUNTED; BY DEPT MAJOR GRADS PLACED PCT_PLACED COLID;

/*create separate output files for each department - has data for each major*/

DATA AAA HS BUS ET IND IST PS NUR;

SET HUNTED;

IF DEPT='AAA' THEN OUTPUT AAA;

IF DEPT='HS' THEN OUTPUT HS;

IF DEPT='BUS' THEN OUTPUT BUS;

IF DEPT='PS' THEN OUTPUT PS;

IF DEPT='ET' THEN OUTPUT ET;

IF DEPT='IND' THEN OUTPUT IND;

IF DEPT='IST' THEN OUTPUT IST;

IF DEPT='NUR' THEN OUTPUT NUR;

RUN;

ods tagsets.ExcelXP FILE="C:\PROGEVAL 2015\REPORTS\AAA 2013-14 Graduate Contacts testx.Xml" style=barrettsblue;

ods tagsets.ExcelXP options (autofit_height= 'yes' embedded_titles='yes' embedded_footnotes='yes'
merge_titles_footnotes='yes' sheet_interval='bygroup' sheet_label=' ' frozen_headers= '3' absolute_column_width= '6,6,8,12,9,9,9,20,3,6,3,3,35' row_height_fudge='3' );

title; footnote;

title1 font=timesRoman bold FONT='12PT' h=4 color=blue "2013-14 #BYVAL(MAJOR) Graduates Not Placed ";

title2 font=timesRoman FONT='11PT' h=4 color=blue " In the Status Column, please use the following codes
to indicate the Graduate's status";

title3 font=timesRoman FONT='11PT' h=4 color=blue "FT= Work FT in Job Related to Degree, PT=
Work PT in Job Related to Degree, S =Full-time Student Fall 2014 I= Not Seeking Work/Active Duty";

title4 font=timesRoman FONT='11PT' h=4 color=black "Graduates: #BYVAL(GRADS)
Placed: #BYVAL(PLACED) Percent Placed: #BYVAL(PCT_PLACED)";

footnote1 font=timesRoman h=3 color=blue "http://www.census.gov/eos/www/naics/";

footnote2 font=timesRoman h=3 J=L color=blue "Note: A 'Y' in the MD column indicates
that the graduate has multiple degrees. If a graduate with multiple degrees is 'PLACED' for one one degree,

s/he is considered placed for all degrees.";

footnote3 font=timesRoman h=3 J=L color=BLACK "Multiple degree graduates for this program and their other degrees are listed below:";

PROC REPORT DATA=AAA NOWD; BY major;

COLUMNS MAJOR COLID FNAME LNAME Home Work_Day Nite Email MD STATUS DE DR NAICST ;

DEFINE MAJOR/ORDER WIDTH=5;

DEFINE COLID/ DISPLAY WIDTH=10;

DEFINE FName/DISPLAY WIDTH=20;

DEFINE LName/DISPLAY WIDTH=20;

DEFINE Home/DISPLAY WIDTH=12;

DEFINE Work_Day/DISPLAY WIDTH=12;

DEFINE Nite/DISPLAY WIDTH=12;

DEFINE Email/DISPLAY WIDTH=45;

DEFINE MD/DISPLAY WIDTH=3;

DEFINE STATUS/DISPLAY WIDTH=6;

DEFINE DE/DISPLAY WIDTH=7;

DEFINE DR/DISPLAY WIDTH=7;

DEFINE NAICST/ DISPLAY WIDTH=55;

BREAK AFTER MAJOR / PAGE;

RUN;

ods tagsets.ExcelXP close;

Re: How to get a macro to pass dynamic var values to multiple spread sheets/apps

Tom — Thu, 25 Jun 2015 16:45:34 GMT

So you included #BYVAL() in the TITLE, but the variables you are referencing are not in your BY statement on the PROC REPORT step.

Re: How to get a macro to pass dynamic var values to multiple spread sheets/apps

madhatter — Thu, 25 Jun 2015 16:49:59 GMT

Tom :

Found the problem; I did not have the" by variables" on the the proc report command. IT works. The PCT_PLACED is printing in the format ##.#####;any way to change this via sas and is it possible to change the color of the variable value so it would be like GRADS in black and the VALUE in blue?

Re: How to get a macro to pass dynamic var values to multiple spread sheets/apps

Tom — Thu, 25 Jun 2015 17:00:09 GMT

You should be able to use font commands in title statement.

You can use a FORMAT statement to change the format used to display the value of a by variable.

title1 color=black '#byvar(age) =' color=blue '#byval(age)';

proc print data=sashelp.class;

where age=12;

by age;

format age Z7.3 ;

run;

Re: How to get a macro to pass dynamic var values to multiple spread sheets/apps

GeoffDer — Mon, 29 Jun 2015 13:26:04 GMT

Hi,

I see you've got something that works, but don't you want to wrap the proc report in a macro too.

Something like this:

%let depts=AAA HS BUS ET IND IST PS NUR;

%macro dodepts;

%do i=1 %to %sysfunc(countw("&depts"));

%let dept=%scan(&depts,&i);

%doreport(&dept);

%end;

%mend dodepts;

%macro doreport(dept);

data _temp;

set hunted;

if dept="&dept";

CALL SYMPUT ('GR',trim(left(put (GRADS,8.))));

CALL SYMPUT ('PL', trim(left(put(PLACED,8.))));

CALL SYMPUT ('PCT',trim(left(put(PCT_PLACED,8.))));

run;

ods tagsets.ExcelXP FILE="C:\PROGEVAL 2015\REPORTS\&dept. 2013-14 Graduate Contacts testx.Xml" style=barrettsblue;

* etc;

title4 font=timesRoman h=4 color=BLACK "Graduates: &GR Placed: &PL Percent Placed: &PCT";

* etc;

proc report data=_temp;

* etc;

%mend doreport;

%dodepts;

Message was edited by: Geoff Der ps. You could also put all the code inside the dodepts macro and only have one macro, if you find that simpler.