topic How to output a an excel file with the first and last rows with values not related to the variables? in ODS and Base Reporting

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.

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

Cynthia_sas — Tue, 10 Jun 2014 01:13:01 GMT

Hi:

You haven't really shown what your data looks like. And by my count, you are missing column 'L' or COLID in the output example you show. See the attached screen shot. When I look at your DEFINES and your COLUMN statement, this is what I you describe, but this is NOT what you show. If you look at the screen shot, you are missing column L. And your description is confusing. For example, you say that E='DATE FILE CREATED' and that F='SE' but in your desired output, you show 'SE' as a string, but you show DATE FILE CREATED as 20130101. So it is confusing about what is a true header value and what is data. What does your data REALLY look like?

Cynthia

A B C D E F G H I J K L <------- this is what you describe.

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

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

Reeza — Tue, 10 Jun 2014 02:02:39 GMT

Use ods html with either put statements or create your dataset with the first row as labels for headers.

Either way you need to provide sample data and output and more details as Cynthia has mentioned.

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

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

Community_Help — Tue, 10 Jun 2014 15:42:13 GMT

Hi @ madhatter .. I am moving this dicussion out of our community help section and into ODS/Base reporting community just wanted to let you know.