DATA Step, Macro, Functions and more

Carraige return not working during proc export

Reply
Frequent Contributor
Posts: 131

Carraige return not working during proc export

Data my_file

  Set my_file2;

           File "/dors09/dors/sascode/reporting/test_post_mod/out/file..txt"

            LINESIZE=8000 dsd  dlm='|';/*missover*/

           guessingrows=32767; /*for truncation*/

          PUT &Final_VARS ;

            

run;

x "unix2dos /dors09/dors/sascode/reporting/test_post_mod/out/file..txt" ;

It is supposed to produce a test file without headers separated by a | example here

1744|filename|01/15/2015|Active|||804-111-2255|aqqq@gmail.com|modified

1114|filename|01/15/2015|Active|Western|CQQ1|804-111-2255|aqqq@gmail.com|modified

The actual headers are

ID|filename|date|Status|Region|ID_Region|Phone|email|loan_status

In some cases there may be a blank for the header.  In that case then just the  | is to show, The problem is, the program code reads the next ID on the same line and does not move to the next line.  Here is that same example that produces the problem

1744|filename|01/15/2015|Active|||804-111-2255|aqqq@gmail.com|modified1114|filename

|01/15/2015|Active|Western|CQQ1|804-111-2255|aqqq@gmail.com|modified

I thought this code here would resolve the issue however it does not.  Any ideas

Super User
Posts: 6,928

Re: Carraige return not working during proc export

Your code is syntactically wrong.

It misses a set statement (because "set" is considered a dataset in the data statement).

What do you try to achieve with assigning 32767 to the variable guessingrows?

Post the log of your test run.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Super User
Posts: 7,392

Re: Carraige return not working during proc export

Not sure why you have guessingrows option on, thats for reading in?  Anyways, the code below worked for me, you can drop the header row if you want.  Make sure &final_vars is space delimited list  

data _null_;

  set sashelp.class;

  file "s:\temp\rob\tt.txt" dlm="|";

  if _n_=1 then put "name|sex|age|height|weight";

  if _n_=1 then sex="";

  put name sex age height weight;

run;

Super User
Posts: 9,671

Re: Carraige return not working during proc export

Check option TERMSTR= 

file "s:\temp\rob\tt.txt" dlm="|"   TERMSTR=crlf ;

Super User
Super User
Posts: 6,498

Re: Carraige return not working during proc export

Your question mentions missing CR, but that is not mentioned in you post, other than the external call to unix2dos command.

You can control the end of line characters that SAS writes by using the TERMSTR option on the FILE statement.

%let varlist=ID filename date Status Region ID_Region Phone email loan_status ;

data _null_;

  set my_file2;

  file "/dors09/dors/sascode/reporting/test_post_mod/out/file.txt"

       lrecl=30000 dsd dlm='|' termstr=crlf

  ;

  if _n_=1 then put "%sysfunc(tranwrd(&varlist,%str( ),|))" ;

  put &varlist;

run;

Frequent Contributor
Posts: 131

Re: Carraige return not working during proc export

data test;

set sashelp.demographics;

run;

%let varlist= CONT ID ISO NAME ISONAME region pop popAGR popUrban totalFR AdolescentFPpct  ;

data _null_;

  set test ;

  file "/D5/dors09/dors/sascode/reporting/dev/output/test_&pbd_date..txt"

       lrecl=30000 dsd dlm='|' termstr=crlf

  ;

  if _n_=1 then put "%sysfunc(tranwrd(&varlist,%str( ),|))" ;

  put &varlist;

run;

Thanks for your help.  i plugged in your example for another sashelp table and it works.  Now how can I get the same results with just the data and not the headers too.

From this

CONT|ID|ISO|NAME|ISONAME|region|pop|popAGR|popUrban|totalFR|AdolescentFPpct

91|180|044|BAHAMAS|BAHAMAS|AMR|323,063|1.34%|90.00%|2.3|

91|227|084|BELIZE|BELIZE|AMR|269,736|2.14%|48.60%|3.1|12.50%

To this

91|180|044|BAHAMAS|BAHAMAS|AMR|323,063|1.34%|90.00%|2.3|

91|227|084|BELIZE|BELIZE|AMR|269,736|2.14%|48.60%|3.1|12.50%

Super User
Super User
Posts: 6,498

Re: Carraige return not working during proc export

The PUT statement inside of the IF statement is the one that is writing the header line. So just remove that whole statement.

Frequent Contributor
Posts: 131

Re: Carraige return not working during proc export

    To your knowlege is there a limit on how many veriables you can list in your varlist?  I am getting unbalanced quote warning however there are no commas nor unbalanced quotes here

SYMBOLGEN:  Macro variable VARLIST resolves to sponsor_loan_number Referral_Date Servicer_Name Agency_Name Backlog_Mod_Flag Trial_Mod_Type Trial_Start_Date Mod_Conversion_Date Servicer_Loan_Number ACH_Flag Trial_Mod_Payment_Amount NEXT_Due_Date

            Last_Payment_Applied_Date UPB Reason_for_Default English_Spanish_Ind Borrower_First_Name Borrower_last_Name Co_Borrower_First_Name Co_Borrower_Last_Name Property_Address_1 Property_Address_2 Property_City Property_State Property_Zip

            Mailing_Address_1 Mailing_Address_2 Mailing_City Mailing_State Mailing_ZIP Borrower_Phone_Home Borrower_Phone_office1 Borrower_Phone_office2 Borrower_Phone_Other Borrower_Phone_Cell1 Borrower_Phone_Cell2 Borrower_Email_Address

            CO_Borrower_Phone_Home CO_Borrower_Phone_office1 CO_Borrower_Phone_office2 COBorrower_Phone_Other COBorrower_Phone_Cell1 COBorrower_Phone_Cell2 Co_Borrower_Email_Address

WARNING: The quoted string currently being processed has become more than 262 characters long.  You might have unbalanced quotation marks.

Super User
Posts: 3,101

Re: Carraige return not working during proc export

If you just want to turn off the warning:

options noquotelenmax;

Frequent Contributor
Posts: 131

Re: Carraige return not working during proc export

Recall you assisted me with carraige return issue with proc export

data test;

set sashelp.demographics;

run;

%let varlist= CONT ID ISO NAME ISONAME region pop popAGR popUrban totalFR AdolescentFPpct  ;

proc sql;select count(ln_no) into :cnt from test;quit;  /*Just added to give a count of the records in the file name*/

data _null_;

  set test ;

  file "/D5/dors09/dors/sascode/reporting/dev/output/test_&pbd_date..txt"

       lrecl=30000 dsd dlm='|' termstr=crlf

  ;

  if _n_=1 then put "%sysfunc(tranwrd(&varlist,%str( ),|))" ;

  put &varlist; 

run;

You told me that I could remove the put &varlist; to start avoid the headers.  Instead when I run the code I get only the headers.  is there a way to incorporate the putnmase=No  or something similar since I am using a data _null_

Super User
Super User
Posts: 6,498

Re: Carraige return not working during proc export

The program is not really very complicated.  Not sure why you having so much trouble making changes.

The first and last statements define the DATA step that will be used to write the text file for you.

The second statement reads the dataset with the data you want to write.

The third statement defines the output text file that you are creating.

The fourth statement writes the data.

data _null_;

   set test ;

   file "/D5/dors09/dors/sascode/reporting/dev/output/test_&pbd_date..txt"

        lrecl=30000 dsd dlm='|' termstr=crlf

  ;

  put &varlist;

run;

Ask a Question
Discussion stats
  • 10 replies
  • 768 views
  • 6 likes
  • 6 in conversation