topic Re: Proc export null values in SAS Programming

Proc export null values

helannivas88 — Mon, 06 Jul 2020 16:07:37 GMT

HI,

Im trying to export a SAS dataset into .txt file using proc export and delimiter is pipe. In the SAS dataset ,there are totally 6 columns but the fourth column is null in all rows. So after export I can able to get only the first three columns in the .txt file, not the remaining three columns ( 04th ,05th ,06th)

When I brought the null column into the first variable , then the .txt file is totally empty.

Please let me know if I need to introduce any extra parameters in proc export to rectify this issue.

Re: Proc export null values

Reeza — Mon, 06 Jul 2020 16:19:09 GMT

Show your code. That shouldn't happen as described using PROC EXPORT.

This generates a file as expected with the empty variables at the end.

data class;
set sashelp.class;
y = .;
z = "";
t = "";
run;

proc export data=class outfile='./demo.txt' dbms=dlm replace;
delimiter = '|';
run;

@helannivas88 wrote:

HI,

Im trying to export a SAS dataset into .txt file using proc export and delimiter is pipe. In the SAS dataset ,there are totally 6 columns but the fourth column is null in all rows. So after export I can able to get only the first three columns in the .txt file, not the remaining three columns ( 04th ,05th ,06th)

When I brought the null column into the first variable , then the .txt file is totally empty.

Please let me know if I need to introduce any extra parameters in proc export to rectify this issue.

Re: Proc export null values

Tom — Mon, 06 Jul 2020 16:57:25 GMT

Please post example input data (in the form of data step), the code you ran, and example lines from the generated text file. Make sure to use with the Insert Code (icon looks like the text < / >) or Insert SAS code (looks like the SAS submit, or running person, icon) buttons when adding the text to your post.

Re: Proc export null values

helannivas88 — Tue, 07 Jul 2020 10:31:15 GMT

Below is my code. Just I do a direct hit to SQL server database and create a SAS dataset and then load into a file.

In the intermediate SAS dataset, I could see all the columns are populated correctly but not in the .txt file.

proc format ;
picture datetimefmt
low-high = '%Y-%0m-%0d %0H:%0M:%0s' (datatype=datetime )
other = .;
run;

proc format ;
picture timefmt
low-high = '%0H:%0M:%0S' (datatype=time )
other = .;
run;

proc format ;
picture datefmt
low-high = '%Y-%0m-%0d' (datatype=date)
other = .;
run;

proc sql;
connect to SQLSVR as SRC (datasrc="&Datasrc" AUTHDOMAIN="XXX");
exec
(
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
) by SRC;

create table WORK.TEMP as
select
CONTEXTID ,
RREF ,
DTTM format datetimefmt26.6,
TS length=25 format=$25. ,
MONOTONIC() AS ROW_NUM,
input("&SYSDATE9 &SYSTIME",DATETIME25.6) as STG_LOADTM format datetimefmt26.6
from connection to SRC
(
select * from &Schema..REL
);
disconnect from SRC;
alter table WORK.TEMP
modify ts char(16) informat=$16. format=$16.;
quit;

%let LOG_DIR=%unquote(%str(%')&_LOGDIR%str(%'));
%let SRC_FEED=TEMP.txt;
%let SOURCE_FILE=%unquote(%str(%')&_SRCDIR/&SRC_FEED%str(%'));

proc export data=work.TEMP
outfile="&SOURCE_FILE"
dbms=dlm replace;
PUTNAMES=NO;
delimiter='|';
run;

Re: Proc export null values

Tom — Tue, 07 Jul 2020 12:13:30 GMT

Show the values of the SAS datasets and the lines of text in the file generated by PROC EXPORT.

One possible issue is that you are originally created TS as LENGTH=25 and then later truncating it to 16 characters. So if the values in the remote database have blanks in the first 16 characters then the result of trimming it will always be blank.

...
 , TS length=25 format=$25. 
...
alter table WORK.TEMP
  modify ts char(16) informat=$16. format=$16.
;