BookmarkSubscribeRSS Feed
Q1983
Lapis Lazuli | Level 10

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

10 REPLIES 10
Kurt_Bremser
Super User

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

Ksharp
Super User

Check option TERMSTR= 

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

Tom
Super User Tom
Super User

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;

Q1983
Lapis Lazuli | Level 10

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%

Tom
Super User Tom
Super User

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

Q1983
Lapis Lazuli | Level 10

    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.

SASKiwi
PROC Star

If you just want to turn off the warning:

options noquotelenmax;

Q1983
Lapis Lazuli | Level 10

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_

Tom
Super User Tom
Super User

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;

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 10 replies
  • 3479 views
  • 6 likes
  • 6 in conversation