topic Re: Reading out using PUT in SAS in SAS Programming

Reading out using PUT in SAS

manonlyn — Fri, 21 Jun 2019 09:48:27 GMT

I usually use PROC EXPORT to read out data from SAS to a csv or .dat file.

I've been given a readout that uses PUT statement but I can't find an example of how to use this that works for me.

What I have at the minute is:

DATA TEST;
SET TC_LEVEL_JUNE01;
FILE "C:\users\JUNE012.csv"
	LRECL = 7353;
	PUT
	@5 TENANCY_REFERENCE_NUMBER $20.
	@25 TENANT_ID $20.
	@45 RECORD_TYPE 2.
	@47 EVENT_TYPE 2.
	@49 NODE_TYPE $8.
	@57 CLOSURE_TYPE $1.
	@58 CLOSED_DATE 8.
	@66 EXTRACT_TYPE 2.
	@68 OPEN_DATE 8.
	@76 DATE_LAST_ACTIVE 8.
	@84 NUMBER_TENANTS 2.
	@86 NUMBER_OCCUPANTS 2.
	@88 RENT_TYPE $20.
	@108 TENANCY_START_DATE 8.
	@116 TENANCY_END_DATE 8.
....... more outputs until
	@7310 SPAREDATE_5 8.;  
RUN;

This creates a dataset called TEST with 870 fields but the dataset TC_LEVEL_JUNE01 has 516 fields (which is correct). I'm not sure why it's creating more fields in the TEST dataset and the read out is not correct either.

Re: Reading out using PUT in SAS

Kurt_Bremser — Fri, 21 Jun 2019 10:32:16 GMT

csv is the Acronym for Comma Separated Volume, but is sometimes interpreted as Character Separated Volume (eg by Excel, which uses semicolons instead of commas). Either way, a csv has to be a delimited file without fixed columns, so set a proper delimiter and discard the positions and formats in the put statement, but set formats as needed in a separate format statement:

data _null_;
set tc_level_june01;
file"C:\users\JUNE012.csv" lrecl=7353 dlm=',';
format
  RECORD_TYPE 2.
  EVENT_TYPE 2.
;
put
  TENANCY_REFERENCE_NUMBER
  TENANT_ID
  RECORD_TYPE
  EVENT_TYPE

..........

;
run;

Re: Reading out using PUT in SAS

FreelanceReinh — Fri, 21 Jun 2019 11:15:39 GMT

The discrepancy between the numbers of variables in datasets TEST and TC_LEVEL_JUNE01 indicates that the PUT statement uses 870-516=354 variable names that are not contained in TC_LEVEL_JUNE01, i.e., the PUT statement doesn't match the dataset, which is alarming. Even worse, if exactly 516 variable names are listed in the PUT statement, 354 variables from TC_LEVEL_JUNE01 will not be written to JUNE012.csv, only missing values instead. Normally, TEST would be an identical copy of TC_LEVEL_JUNE01 (and therefore typically omitted by using a data _null_ statement).

Watch out for log messages like

NOTE: Variable NEWVAR is uninitialized.

to identify the mismatching variable names.

Also, consider using the DSD option (which is useful if a character value happens to contain the delimiter) if you want to create a CSV file and not a text file with fixed field widths and without delimiters (as is suggested by your PUT statement).