PQL Procedures

Home	Starting	Environment	DBMS	VisualPQL	Procedures	SQL	Forms	Host/API	Index

PQL Procedures

CSV Save File

The CSV SAVE FILE procedure creates a file that can be used to transfer data to a software package that recognises Comma Separated Variables format.

     CSV SAVE FILE [ FILENAME = ldi ]
                   [ VARIABLES = { varname [( ['header_text'] [format] )]... | ALL }]
                   [ BOOLEAN = ( logical_expression ) ]
                   [ HEADER]
                   [ SAMPLE = fraction ]
                   [ SEPARATOR = "single_character" | TAB ]
                   [ SORT = [(n)]variable [(A)|(D)], ...]

FILENAME

Specifies the filename created by the procedure. If no filename is specified a file named sirproc.csv is produced.

VARIABLES

Specifies the procedure variables that are written to the output file. Specify the variables in the order in which they are to appear in the output file. If this option is not specified or the keyword ALL is specified, the default variables are output.

When specific variables are output, header text and/or a format can be specified following the variable name within parentheses.

String variables, categorical variables and value labels are output within double quotes - "". Numeric variables, dates and times are output as per the format without quotes.

header_text If a header record is written, by default, the variable name is used. Specify header text in quotes to use for this column in the header.
Format The default format is taken from the schema. Specify a format expression to alter this. The width of a column is calculated from the format expression. If the data for the column does not fit in the specified width, a numeric column is filled by X's and a character column is truncated to fit.
In the format expressions below, "w" is a number specifying the width in columns.
Aw Specifies Alphanumeric string format.
Bw
Specifies reverse string format. If there is a column header it is written as normal and the data is written in reverse (backwards).
Lw
Specifies that Value Labels are written instead of the data value. If a value has no defined label, blanks are output.
Iw
Specifies Integer format. Any decimal portion of the number is ignored.
Fw.d
Specifies Floating point format. Use for either floating point or scaled integers with decimal portions. "d" is the number of decimal places.
Ew
Specifies Exponential (scientific).
DATE
Specifies a date variable formatted using the date format. See date formats for a complete description of date formats. The width of the column is specified by the characters in the format. For example:

VARIABLES = BIRTHDAY (DATE'Wwwwww Mmm DD, YYYY')

TIME
Specifies a time variable formatted using the time format. See time formats for a complete description of time formats. The width of the column is specified by the characters in the format. For example:

VARIABLES = TESTTIME (TIME'HH:MM:SS PP')

D, C, P
D puts a dollar sign ($) before the numeric value.
C separates thousands with the comma character.
P puts a percent sign (%) after the numeric value. These can be specified in addition to other numeric format expressions. For example:

VARIABLES = MONEY (F9.2, D, C)

BOOLEAN

Specifies which procedure table records are used by the procedure. The procedure table records for which the logical expression is true are used by the procedure. If this option is not specified, all procedure table records are used.

HEADER

Specifies that a header is written to the output file. This contains one entry per column, entries separated by columns. Each entry is up to 32 characters in quotes. Each entry is either the name of the variable or any specified header text.

SAMPLE

Specifies that a random sample of the procedure table records are used by the procedure.
The fraction specifies the percent of records used and is specified as a positive decimal number less than or equal to 1 (one). .25, for example specifies that a 25% sample be used.

SEPARATOR

Specifies that a character is used instead of the default comma to separate fields. Specify the character in quotes or use the keyword TAB to use a tab character as the separator.

SORT

Specifies the sequence of the output. n is an integer that specifies the maximum number of records to be sorted. The default for this parameter is either the number of records in the database or the value specified in the sortn parameter and need only be specified if the number of records in the procedure table is greater than the default. The procedure table is sorted by the specified variables in variable list order. A variable name followed by (A) or (D) specifies that for that variable the sort is in Ascending order (the default) or in Descending order.

Examples

The following produces a CSV file with the variables ID, SALARY, EDUC and BIRTHDAY.

retrieval
process cases
.  process rec employee
.    get vars id salary educ birthday
.    perform procs
.  end rec
end case
csv save file filename = example.csv  header
end retrieval

When the above program is run, the following summary report is displayed:

CSV SAVE FILE Summary Report
------------------------------


No of data records ..... 20
No of columns .......... 4

The file itself contains:

"ID","SALARY","EDUC","BIRTHDAY"
1,2150,1,01 15 38
2,2650,4,12 07 42
3,3150,3,08 10 53
....

The following produces a CSV file with some variables to illustrate format options.

program
integer * 1 sex
string*10
value labels sex (1)'Male'(2)'Female'
real * 8 realnum

DATE filedate ('MM/DD/YYYY')
DATE bdate    ('MM/DD/YYYY')

.  COMPUTE filedate = today(0);bdate=today(0)
.  compute realnum=12.12345
.  compute sex=1
.  compute string = 'abcdefhij'
.  perform procs

CSV SAVE FILE FILENAME = "test.csv" /
              VARIABLES= realnum ('Salary' f8.2)
                         filedate(date"mm/dd/yyyy")
                         bdate(date"mm/dd/yyyy")
                         sex(L6) string(b8) /
              HEADER
END RETRIEVAL

The file itself contains:

"Salary","FILEDATE","BDATE","SEX","STRING"
   12.12,03/06/2002,03/06/2002,"Male  ","jihfedcb"