BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
GreggB
Pyrite | Level 9

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;

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

8 REPLIES 8
Kurt_Bremser
Super User

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.

GreggB
Pyrite | Level 9

I double checked but I'll check again.

Tom
Super User Tom
Super User

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.

GreggB
Pyrite | Level 9

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

Kurt_Bremser
Super User

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.

Tom
Super User Tom
Super User

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.

 

GreggB
Pyrite | Level 9

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. 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 1618 views
  • 5 likes
  • 3 in conversation