topic problem exporting the last variable using FILE statement in SAS Procedures

problem exporting the last variable using FILE statement

GreggB — Mon, 19 Sep 2016 12:15:52 GMT

I'm attempting to export a csv file to exactly match the vendor's requirements. Everything is good except the last variable in the data set (sortgroup) does not export. I remember there's something different to do to ensure it gets exported but can't remember.

Below is the code. I omitted several of the variables below that are being exported for brevity. (all vars are char. I'm running SAS 9.4 64 bit on a PC)

data _null_;
%let _EFIERR_ = 0; /* set the ERROR detection macro variable */
%let _EFIREC_ = 0; /* clear export record count macro variable */
file 'G:\Departments\Research\TESTING\PSAT\1617\Final.csv' delimiter=',' DSD DROPOVER lrecl=32767;
set WORK.final end=EFIEOD;
format aicode $6. ;
format PSAT8IND $1. ;
format PSAT10IND $1.;
.
.
format sortgroup $10. ;
if _n_ = 1 then /* write column names */
do;
put
'AI CODE'
','
'PSAT 8/9 TEST ADMINISTRATION INDICATOR'
','
.
.
.
'SAT TEST CENTER'
','
'SORT GROUP'
;
end;
do;
EFIOUT + 1;
put aicode $ @;
.
.
.
put testcenter $ @;
put sortgroup $ ;

;
end;
if _ERROR_ then call symputx('_EFIERR_',1);
if EFIEOD then call symputx('_EFIREC_',EFIOUT);
run;

Re: problem exporting the last variable using FILE statement

Kurt_Bremser — Mon, 19 Sep 2016 12:28:33 GMT

Your code looks good on first glance. Are you sure that the number of items in your header line corresponds to the number of variables in the put statements?

I'd try to reduce the complexity of the data step until I get a correct result, and build back up from there, if no obvious discrepancy can be found in the code.

Re: problem exporting the last variable using FILE statement

GreggB — Mon, 19 Sep 2016 12:30:57 GMT

I double checked but I'll check again.

Re: problem exporting the last variable using FILE statement

Tom — Mon, 19 Sep 2016 12:36:13 GMT

Your code looks fine. What is it about the last column that they do not like?

One thing that can confuse some programs is if you create the file using PC standard of CR+LF at the end of the line and try to read it on Unix where the standard end of line is just a LF then the CR character can be included into the value of the last column.

If that is the issue than add TERMSTR=LF to your FILE statement.

Also you should not need the FORMAT statements of the $ in the PUT statement.

Re: problem exporting the last variable using FILE statement

GreggB — Mon, 19 Sep 2016 12:39:29 GMT

The last column is not exporting at all, just the column header. Unix is not in play here.

Re: problem exporting the last variable using FILE statement

Kurt_Bremser — Mon, 19 Sep 2016 12:46:30 GMT

Also take a good look at the log. If you erroneously tried to export sortgroup, when the variable name is actually sort_group, all you get is a NOTE that sortgroup is uninitialized.

Re: problem exporting the last variable using FILE statement

Tom — Mon, 19 Sep 2016 13:35:57 GMT

So just export the last column and see what is happening.

data _null_;
  file 'G:\Departments\Research\TESTING\PSAT\1617\Final.csv' 
    delimiter=',' DSD DROPOVER lrecl=32767
  ;
  if _n_ = 1 then do;
    put 'SORT GROUP' ;
  end;
  set WORK.final ;
  put sortgroup ;
run;

Also what is with the DROPOVER option on the FILE statement? Is it possible that SORTGROUP is so long that trying to write it would cause the line to be longer than 32,767 characters? If so then try making the LRECL option larger in the FILE statement.

Re: problem exporting the last variable using FILE statement

GreggB — Mon, 19 Sep 2016 13:39:46 GMT

So....here was the culprit all along: I had created SORTGROUP by concatenating 2 vars and I didn't trim it. Since SORTGROUP had to be a length of 10, I was actually exporting 10 blanks. Fortunately, it's too early in the day to start drinking.

Re: problem exporting the last variable using FILE statement

Kurt_Bremser — Mon, 19 Sep 2016 13:47:00 GMT

Don't be sorry, happens to all of us.

Still great for us if one of the posts gave you the important hint that led you on to the right trail 🙂