The SAS Output Delivery System and reporting techniques

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

Reply
Occasional Contributor
Posts: 13

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

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!00399300Jupiter College 20130101SEI
D1ZacherySmithyMD1965091519680506003993000000083
T13

Any assistance is appreciated. 

SAS Super FREQ
Posts: 8,820

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

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 ;


do_not_understand_data_or_report.png
Occasional Contributor
Posts: 13

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

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

.                                                                                                                                                                                                                                                 
H100399300Jupiter College20130214SEI
D1 SmithZacharyMD1997101619680506 0039930084
D1 BadLRincewind 1983110920120815 0039930068
D1 GytheNOgg 1987110120120515 0039930067
D1 Roland Deschain 1978100120110515 0039930017
D1 RobertodGriva 1995110120101215 003993003788453
D1 Jamie Lannister 1996080620101215 003993003825626
T18
Super User
Posts: 19,167

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

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.

SAS Employee
Posts: 232

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

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.

Ask a Question
Discussion stats
  • 4 replies
  • 724 views
  • 0 likes
  • 4 in conversation