Help using Base SAS procedures

problem exporting the last variable using FILE statement

Accepted Solution Solved
Reply
Super Contributor
Posts: 268
Accepted Solution

problem exporting the last variable using FILE statement

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;


Accepted Solutions
Solution
‎09-19-2016 09:48 AM
Super User
Super User
Posts: 6,500

Re: problem exporting the last variable using FILE statement

[ Edited ]

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.

 

View solution in original post


All Replies
Super User
Posts: 6,936

Re: problem exporting the last variable using FILE statement

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super Contributor
Posts: 268

Re: problem exporting the last variable using FILE statement

I double checked but I'll check again.

Super User
Super User
Posts: 6,500

Re: problem exporting the last variable using FILE statement

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.

Super Contributor
Posts: 268

Re: problem exporting the last variable using FILE statement

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

Super User
Posts: 6,936

Re: problem exporting the last variable using FILE statement

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Solution
‎09-19-2016 09:48 AM
Super User
Super User
Posts: 6,500

Re: problem exporting the last variable using FILE statement

[ Edited ]

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.

 

Super Contributor
Posts: 268

Re: problem exporting the last variable using FILE statement

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. 

Super User
Posts: 6,936

Re: problem exporting the last variable using FILE statement

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 Smiley Happy

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 367 views
  • 4 likes
  • 3 in conversation