madhatter Tracker

Re: Negative if or then statements. What am I not seeing ?

madhatter — Wed, 26 Aug 2015 01:52:08 GMT

Thanks. The easier way works. I ma going to have mull over the logic issue though..

Negative if or then statements. What am I not seeing ?

madhatter — Tue, 25 Aug 2015 22:53:19 GMT

I am producing a report on student's grades- how many start a Math or English course (Cohort=1), how many complete it (COMP) and how many have success (PASS). I thought that the section of code below would create the variable 'PASS' with a value of 1 for each occurrence of grades A, B, C and S and create a variable 'COMP' with a value of 1 for every student who finished a course .

Rather than list all the grades that constitute completion, I thought that I could use the 'not equal' condition for the grades that indicate non-completion and create a positive counter for students who last to the bitter end. This code does create the correct PASS values; BUT even though there are students that have grades of 'I', 'NC, and "W", all 10K+ students in the cohort have a 'COMP' variable with a value of 1. If I use one grade, i.e., If GRADE~=W" then COMP=1, all students with a 'W' grade have a COMP value of 0 and do not appear in the QUAPROPTER subset.

So what an I doing wrong? I would bet my ex's alimony that I have used this combination before.

Thanks

F9_GRADES;

SET IA.CERSGRADES;

IF TERM=>&COHO_TERM AND CRSE_NO>100 AND (COURSENAME='MAT' OR COURSENAME='ENG');

IF (GRADE='A' OR GRADE='B' OR GRADE='C' OR GRADE='S') THEN PASS=1; /*SET PASS VAR*/

IF ( GRADE~='I' OR GRADE~='NC' OR GRADE~='W' ) THEN COMP=1; /*SET COMPELETION VAR*/
cohort=1

array a(*) _numeric_;

do i=1 to dim(a);

if a(i) = . then a(i) = 0;

end;

drop I;
KEEP SSN TERM COURSE CRSE_NO COURSENAME GRADE GOT COMPLETE PASS;

DATA QUAPROPTER;/*test if the code works*/

SET F9_GRADES;

IF COMPLETE=1;

PROC FREQ; TABLES COHO PASS COMP GRADE;

RUN;;

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

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

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

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: Need help trouble shooting program to produce multiple folders with a macro.

madhatter — Wed, 20 May 2015 23:24:59 GMT

Tom. What you provided was most helpful. I am applying this to another current annual projects, ; One tracks 6 student cohorts (Fall 2008...Fall 2013) thru the 2014 fall term, reporting how many were retained/transferred/graduated in each intervening term; next year the 2014 cohort will be added; then 2015, ad infinitum. This project's directory structure is: a main folder (year); subfolders for each cohort; and six sub-folders for each cohort. Office policy dictates all project data/programs be maintained in a project folder on the server. Copying and editing last years 30 folders, files & program names and then editing the program fileref names appropriately, as was previously done, would been quicker than developing this process using macros....this year. But extrapolating the copy & edit time for future years & the increasing number of cohorts, no way. And I have learned a lot. After I finish this year's reports, I'm going to do a program to create the folders for the new year and copy the requisite SAS program files from the past year, all correctly named; and do it with an array, Do Loop, and macros; I'm sure you'll see other questions from me here in the future as I stumble along. Again, thanks for taking the time to offer some very useful information/guidance rather than pontificate;, it is appreciated.

Re: Need help trouble shooting program to produce multiple folders with a macro.

madhatter — Mon, 18 May 2015 22:08:47 GMT

Correct, this is setting up directories and files , SAS 9.3. on a PC, so IT is not going to do it. And copying and renaming folders and files is so lame. I am setting up my major recurring Semester Based studies to run with ONE or TWO macros that set the parameters for the data acquisition from the various sources; the parameters and variables names for the analysis; the titles, column headers, etc. for the reports; and the output file name for the reports. I have been fairly successful with that and getting better. My problem with the macros in my example program for creating the actual folders and subfolders I need is that I do not understand why my SAS LOG log states "SYMBOLGEN: Macro variable DIR resolves to C:\PROG EVAL 2017\GFU/MLOGIC(CHK_DIR): %PUT %sysfunc(sysmsg()) The directory has been created.WARNING: Physical file does not exist, C:\PROG EVAL 2017\GFU. The directory has been created." But the directory is not created; at least where it is suppose to be created. What was created were folders named: EVAL, 2017\GFU, and 2017\DATA in the C:\PROG EVAL 2015\PE HEAD folder, which is the folder where this SAS program resides. I guess that I can take the cheap way and create SAS ref lib folders and then clear the ref lib name; but was hoping would be able to see the error(s) with my macros.

rlWy successful with that

Need help trouble shooting program to produce multiple folders with a macro.

madhatter — Mon, 18 May 2015 10:13:52 GMT

My goal is to create A program that will create all the macros and File folders that are required for a given project, with minimal upfront coding. I have multiple annual projects that I will be doing this for and most of these projects use the same data sources and formats each year; so my ultimate goal is to use one macro to create all the variables and report names.

My current issue ( I am sure I will have more before I am through) is that I do have enough knowledge of macros to identify why my program is not producing the correct file folders. The SASA program and log are below (. . Before I ran this program. I checked that any folders that I was trying to created DID NOT exist; this included deleting the files the program was creating in the wrong directory; exited SAS; and started it new.

The log indicates hat The directory "C:\PROG EVAL 2017" exists (But it did not and does not); and that C:\PROG EVAL 2017\Data and C:\PROG EVAL 2017\Data\GFU. were created BUT they were not. The C:\CPSASLIB\PROGEVAL2017 folder was created and the LIBREF was assigned correctly. What the program did do is create the following folders C:\PROG EVAL 2015\PE HEAD\2017; C:\PROG EVAL 2015\PE HEAD\2017\DATA; C:\PROG EVAL 2015\PE HEAD\2017\DATA\GFU and C:\PROG EVAL 2015\PE HEAD\EVAL.

Any help will be appreciated.

Thanks

LOG

NOTE: SAS (r) Proprietary Software 9.3 (TS1M2)

Licensed to STATE BOARD FOR TECH & COMP EDU - ADMIN, Site 70049135.

NOTE: This session is executing on the W32_7PRO platform.

NOTE: Enhanced analytical products:

SAS/STAT 12.1, SAS/ETS 12.1, SAS/OR 12.2, SAS/IML 12.1, SAS/QC 12.1

NOTE: SAS initialization used:

real time 1.07 seconds

cpu time 0.59 seconds

1 /*THIS IS A PROGRAM THAT ESTABLISHES THE MACROS THAT WILL BE USED FOR THE PROGRAM

2 EVALUATION SAS PROGRAMS*/

4 /*Change The PE_TERM Value To The Starting Summer Term*/

5 %let PE_TERM=20143;

7 /*****************************************************/

9 /***************FIXED MACROS DO NOT CHANGE*******************/

10 %LET PE_YR=%SYSFUNC(INT(%EVAL(&PE_TERM/10)))+3;/*Resolves to the year the eval is conducted

10 ! 2015*/

11 %LET ST_YR=%sysfunc(MOD(%sysfunc(INT(%EVAL(&&PE_TERM/10))),100))+1;/*resolves to the 2-Digit

11 ! start yrr for the grads*/

12 %LET JK=%eval(&st_yr)+2; /*resolves to the correct YR for the libname*/

14 /*****************************************************/

16 OPTIONS NONUMBER NODATE NOBYLINE mprint mlogic SYMBOLGEN;

17 TITLE;

19 /*CREATES the MACRO FOR MAIN FOLDER*/

20 %Let PXDIR=C:\PROG EVAL %EVAL(&PE_YR);

SYMBOLGEN: Macro variable PE_YR resolves to 2014+3

22 /*CREATES MACRO FOR SAS LIBRARY FOLDER PATH. */

23 %Let fpath=C:\CPSASLIB\PROGEVAL%EVAL(&PE_YR);

SYMBOLGEN: Macro variable PE_YR resolves to 2014+3

24 /*****************************************************/

25 /*CREATES THE NEW PE FOLDER AND SUBFOLDERS*/

26 %macro chk_dir(dir=) ;

27 options noxwait;

28 %local rc fileref ;

29 %let rc = %sysfunc(filename(fileref,&dir)) ;

30 %if %sysfunc(fexist(&fileref)) %then

31 %put NOTE: The directory "&dir" exists ;

32 %else

33 %do ;

34 %sysexec md &dir ;

35 %put %sysfunc(sysmsg()) The directory has been created. ;

36 %end ;

37 %let rc=%sysfunc(filename(fileref)) ;

39 %mend chk_dir ;

41 %chk_dir(DIR=&PXDIR) ;

MLOGIC(CHK_DIR): Beginning execution.

SYMBOLGEN: Macro variable PXDIR resolves to C:\PROG EVAL 2017

MLOGIC(CHK_DIR): Parameter DIR has value C:\PROG EVAL 2017

MPRINT(CHK_DIR): options noxwait;

MLOGIC(CHK_DIR): %LOCAL RC FILEREF

MLOGIC(CHK_DIR): %LET (variable name is RC)

SYMBOLGEN: Macro variable DIR resolves to C:\PROG EVAL 2017

SYMBOLGEN: Macro variable FILEREF resolves to #LN00012

MLOGIC(CHK_DIR): %IF condition %sysfunc(fexist(&fileref)) is TRUE

MLOGIC(CHK_DIR): %PUT NOTE: The directory "&dir" exists

SYMBOLGEN: Macro variable DIR resolves to C:\PROG EVAL 2017

NOTE: The directory "C:\PROG EVAL 2017" exists

MLOGIC(CHK_DIR): %LET (variable name is RC)

MLOGIC(CHK_DIR): Ending execution.

42 %chk_dir(DIR=&PXDIR\DATA);

MLOGIC(CHK_DIR): Beginning execution.

SYMBOLGEN: Macro variable PXDIR resolves to C:\PROG EVAL 2017

MLOGIC(CHK_DIR): Parameter DIR has value C:\PROG EVAL 2017\DATA

MPRINT(CHK_DIR): options noxwait;

MLOGIC(CHK_DIR): %LOCAL RC FILEREF

MLOGIC(CHK_DIR): %LET (variable name is RC)

SYMBOLGEN: Macro variable DIR resolves to C:\PROG EVAL 2017\DATA

SYMBOLGEN: Macro variable FILEREF resolves to #LN00013

MLOGIC(CHK_DIR): %IF condition %sysfunc(fexist(&fileref)) is FALSE

MLOGIC(CHK_DIR): %SYSEXEC md &dir

SYMBOLGEN: Macro variable DIR resolves to C:\PROG EVAL 2017\DATA

MLOGIC(CHK_DIR): %PUT %sysfunc(sysmsg()) The directory has been created.

WARNING: Physical file does not exist, C:\PROG EVAL 2017\DATA. The directory has been created.

MLOGIC(CHK_DIR): %LET (variable name is RC)

MLOGIC(CHK_DIR): Ending execution.

43 %chk_dir(DIR=&PXDIR\GFU);

MLOGIC(CHK_DIR): Beginning execution.

SYMBOLGEN: Macro variable PXDIR resolves to C:\PROG EVAL 2017

MLOGIC(CHK_DIR): Parameter DIR has value C:\PROG EVAL 2017\GFU

MPRINT(CHK_DIR): options noxwait;

MLOGIC(CHK_DIR): %LOCAL RC FILEREF

MLOGIC(CHK_DIR): %LET (variable name is RC)

SYMBOLGEN: Macro variable DIR resolves to C:\PROG EVAL 2017\GFU

SYMBOLGEN: Macro variable FILEREF resolves to #LN00014

MLOGIC(CHK_DIR): %IF condition %sysfunc(fexist(&fileref)) is FALSE

MLOGIC(CHK_DIR): %SYSEXEC md &dir

SYMBOLGEN: Macro variable DIR resolves to C:\PROG EVAL 2017\GFU

MLOGIC(CHK_DIR): %PUT %sysfunc(sysmsg()) The directory has been created.

WARNING: Physical file does not exist, C:\PROG EVAL 2017\GFU. The directory has been created.

MLOGIC(CHK_DIR): %LET (variable name is RC)

MLOGIC(CHK_DIR): Ending execution.

44 %CHK_DIR(DIR=C:\CPSASLIB\PROGEVAL%EVAL(&PE_YR));

MLOGIC(CHK_DIR): Beginning execution.

SYMBOLGEN: Macro variable PE_YR resolves to 2014+3

MLOGIC(CHK_DIR): Parameter DIR has value C:\CPSASLIB\PROGEVAL2017

MPRINT(CHK_DIR): options noxwait;

MLOGIC(CHK_DIR): %LOCAL RC FILEREF

MLOGIC(CHK_DIR): %LET (variable name is RC)

SYMBOLGEN: Macro variable DIR resolves to C:\CPSASLIB\PROGEVAL2017

SYMBOLGEN: Macro variable FILEREF resolves to #LN00015

MLOGIC(CHK_DIR): %IF condition %sysfunc(fexist(&fileref)) is FALSE

MLOGIC(CHK_DIR): %SYSEXEC md &dir

SYMBOLGEN: Macro variable DIR resolves to C:\CPSASLIB\PROGEVAL2017

MLOGIC(CHK_DIR): %PUT %sysfunc(sysmsg()) The directory has been created.

WARNING: Physical file does not exist, C:\CPSASLIB\PROGEVAL2017. The directory has been created.

MLOGIC(CHK_DIR): %LET (variable name is RC)

MLOGIC(CHK_DIR): Ending execution.

SYMBOLGEN: Macro variable JK resolves to 15+2

46 /*CREATE THE NEW THE LIB REF*/

48 LIBNAME PE%EVAL(&JK) "&fpath";

SYMBOLGEN: Macro variable FPATH resolves to C:\CPSASLIB\PROGEVAL2017

NOTE: Libref PE17 was successfully assigned as follows:

Engine: V9

Physical Name: C:\CPSASLIB\PROGEVAL2017

50 proc format;

50 ! value BGRAD 0-5= '#e13300'

51 6-1000= 'BLACK';

NOTE: Format BGRAD has been output.

52 RUN;

NOTE: PROCEDURE FORMAT used (Total process time):

real time 0.06 seconds

cpu time 0.03 seconds

Help with ARRAY, Loops.

madhatter — Mon, 09 Mar 2015 04:43:09 GMT

I am tracking student cohorts over time to identify when students transfer out of the college and which terms they are co-enrolled at my college (HOME) and another institution (TRFINST) at the same time. This is a many to many match between the student’s HOME semesters and the TRFINST semesters. I can identify the multiple semesters that
students were “co-enrolled” (enrolled at Home and a TRFINST in the same semester) and the semesters that students transferred to a TRFINST. My problem is that project requirements are that after a student is identified as a student is identified as a Transfer Student, that transfer semester is to be reported an no further tracked terms are to be reported. I am
trying to revise a program that I use to track retention using the same type data but am bogged down on how to eliminate future values for students identified as Transfer students. (i.e., student transfers and then comes back to HOME for a semester and later transfers to another college - no data after the first transfer is to be reported). I figure I can transpose the data results I have, unduplicate it, and then remerge the data again. But would like to do it without all that manipulation. So any ideas will be appreciated.

I have attached a program Headach.SAS and a sample SAS file (TEST_TRF_PROJ2). ENRTERM(I) - "1" indicates the student was enrolled at HOME; TRFSEM(I)- "1" indicates the student was enrolled at TRFINST; The variable index numbers are based on the student's cohort term and indicate the numbers of semesters since the cohort term. Student 23 has become my “test” student and should have a “1” value for COENRSEM 3-7 and 0 for COENRSEM 9-15; and a “1” value for XSEMTRF9 and “0” for the XSEMTRF1-9 and 10-15.

proc tabulate (to excel via ods) Fonts not applying corectly

madhatter — Tue, 01 Jul 2014 23:15:26 GMT

My Excel is set up with Times New Roman 12pt as the default font. When I run the following program, the CLASS Variable values are displayed with the Arial 12 PT font when I open the output in excel. Also, the Degree variable values are centered and not left adjusted. Any ideas on why the Arial font is being applied or why the Degree var values are being centered? It does not take much to edit it, but it is annoying.

Thanks in advance,

Charlie

%LET STY1=FONT_FACE="TIMES_NEW_ROMAN" FONT_SIZE=12PT FOREGROUND=BLACK just=left cellwidth=1.5

%LET STY2= FONT_FACE="TIMES_NEW_ROMAN" FONT_SIZE=12PT FOREGROUND=MAROON just=left cellwidth=1.5;

HTML FILE='C:\PROG EVAL 2014\REPORTS\PRELIM GFU-MTC-TRF-DEW REPORT.XLS';

"<td align=center colspan=9> Interim Results for the SCTCS Academic Program Evaluation of the 2012-2013 Graduate Placement </td>";

"<td align=center colspan=9> based on the Graduate Survey, National Clearing House, and SC Employment Data </td>";

TABULATE DATA=DEWYX MISSING STYLE=[&STY1] ;

CLASS DEPT DEGREE DEGTITLE/style=[&STY1] ; VAR GRAD IN_FIELD SCHOOL RESP EXCLUDE PLACED ADJTOT/STYLE=[&STY1];

TABLE (DEPT=' '*DEGREE=' '*DEGTITLE=' ' ALL='TOTAL'*F=6.0)*[STYLE=[&STY1] ], ((GRAD='Grads' In_Field School Placed Resp)*[STYLE=[&STY2]]*F=6.0 'Placed %'*((PCTSUM<adjtot>)*f=pctPIC.)*[style=[FONT_FACE="TIMES_NEW_ROMAN" FONT_SIZE=12PT foreground=traffic.]]) ;

KEYLABEL SUM=' ';

KEY LABEL PCTSUM=' ';

RUN;

HTML CLOSE;

Re: How to output a an excel file with the first and last rows with values not related to the variables?

madhatter — Tue, 10 Jun 2014 15:37:11 GMT

Sorry about the confusion. I had difficulty getting the output example into the SAS problem page correctly and accidently deleted "Column B" . I have provide a correct example below ( A normal submission file would contain between 2,000 and 14,000 students). The NSLCH receives the input file, processes the data, and returns information pertaining to other institutions that the students attended.

Row 1. Row 1 contains data required by the NSLCH to receive and process the data: Column-Description (My example) A- Record Type (H1), B- School/Entity Code (003993), C-Branch Code(00), D-School/Entity Name (Jupiter College), E-File Creation Date(20130214), F-Purpose of Inquiry (SE). G-Entity Type (I).

Detail data. The detail data is in row 2-6. One row for each student who I am requesting a search on. Column-Description (my additional comment):

A- Record Type ('D1' is specified by NSLCH), B-Blank. C-First Name, D-Middle Initial, E-Last Name, F-Name, G-Birth Date, H-Search Date (The NSLCH
searches for any attendance dates subsequent to this date), I- Blank, J- School Code, K-Branch Code, L- Student ID ( My COLID)
Last Row. Trailer Record. Column-Description (My comments). A- Record Type ('T1" is specified by NSLCH), B-A number equal to the total number of detail records, plus two for the header and trailer records).

I run a SAS prog against my student data base which, based on selected parameters, extracts the required data elements for each student and creates a temporary dataset. I then add variables A ('D1'), B (Blank), and I (Blank) and create another data formatted to prescribed format.

I need assistance on how to produce the final product containing the required Row 1 and Trailer Row data and the Detail Data rows. I can set each of the Row 1 and Trailer elements as additional variables but can not figure out how to output the desired final product. I currently fill in the ROW 1 and Trailer Row data manually. It is not that time consuming, but I would like to have a program that would do it.

Thanks you

H1	003993	00	Jupiter College	20130214	SE	I
D1		Smith	Zachary	MD	19971016	19680506		003993	00	84
D1		Bad	L	Rincewind		19831109	20120815		003993	00	68
D1		Gythe	N	Ogg		19871101	20120515		003993	00	67
D1		Roland		Deschain		19781001	20110515		003993	00	17
D1		Roberto	d	Griva		19951101	20101215		003993	00	3788453
D1		Jamie		Lannister		19960806	20101215		003993	00	3825626
T1	8

How to output a an excel file with the first and last rows with values not related to the variables?

madhatter — Tue, 10 Jun 2014 00:48:54 GMT

I use PROC REPORT with ODS HTML output to generate a file to send to the National Stu

dent Loan Clearinghouse. I can easily manually edit the file to the correct format, but was wondering if there was a way to eliminate this step. The file uses columns A-L for the data: A='D'1, B=' ', C=F_NAME, D=MI, E=L_NAME, F=SUFIX, G=DOB, H=LAST_DATE,

I=' ', J='003993', K=='00', L=STUDENT_ID. In order to transmit the file I have to edit the first and last rows to their defined values. The first row has to have the following values in specifies columns A='H1', B='003993' C='00', D='MIDLANDS COLLEGE', E='DATE FILE CREATED', F='SE', G='I' and the last row has to have A='T1' B=TOTAL NUMBER OF ROWS IN THE FILE.

report data=NSLCH nowd;

column A B F_NAME MI L_NAME SUFFIX bdate lastdate i j k COLID ;

define A / ' ';

define B / ' ';

definE F_NAME / ' ';

define MI/ ' ';

define L_NAME / ' ';

define SUFFIX / ' ';

define BDATE / ' ' '' width=8;

define LASTDATE / '' width=8;

define I / '';

define J / '';

define K / '';

define COLID / '';

run;

Output example

H!	003993	00	Jupiter College	20130101	SE	I
D1	Zachery		Smithy	MD	19650915	19680506		003993	00	0000083
T1	3

Any assistance is appreciated.