DATA Step, Macro, Functions and more

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

Reply
Occasional Contributor
Posts: 13

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

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


 

 

MD


 

 

STATUS


 

 

DE


 

 

DR


 

 

naics


 

 

AA


 

 

99999


 

 

Donald


 

 

Roeser


 

 

9999


 

 


 

 

777-


 

 

BOC@aol.com


 

 

Y


 

 


 

 

PT


 

 

Y


 

 

812210 Funeral Homes


 

 


 

 

111111


 

 

Morris


 

 

Day


 

 


 

 

888


 

 


 

 

Dinowlk@hotmail.


 

 


 

 


 

 


 

 


 

 

713940 /Watch_Clock
  mfg


 

 


 

 

2222


 

 

Jani


 

 

Lane


 

 

999-


 

 


 

 


 

 

cherries@yahoo.c


 

 

Y


 

 


 

 


 

 


 

 

311811/bakeries


 

 


 

 

555555


 

 

Davis


 

 

Byron


 

 

999


 

 

888


 

 


 

 

stealer@yahoo.co


 

 


 

 


 

 

PT


 

 

Y


 

 

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


 

 

Major


 

 

Major


 

 


 

 


 

 


 

 


 

 


 

 


 

 


 

 


 

 


 

 

99999


 

 

AA


 

 

NUR


 

 

HLC6


 

 


 

 


 

 


 

 


 

 


 

 


 

 


 

 


 

 

555555


 

 

AA


 

 

AS


 

 


 
Super User
Super User
Posts: 6,502

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

Is there a question buried in there somewhere?

Occasional Contributor
Posts: 13

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

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


Super User
Super User
Posts: 6,502

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

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.

Occasional Contributor
Posts: 13

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

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;


Super User
Super User
Posts: 6,502

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

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

Occasional Contributor
Posts: 13

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

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?

Super User
Super User
Posts: 6,502

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

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;

New Contributor
Posts: 4

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

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.

Ask a Question
Discussion stats
  • 8 replies
  • 416 views
  • 0 likes
  • 3 in conversation