DATA Step, Macro, Functions and more

Weird sign appears after exporting a SAS dataset

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 18
Accepted Solution

Weird sign appears after exporting a SAS dataset

[ Edited ]

Hi Everyone,

 

I am trying to export my SAS Dataset into text file.

i Tried 3 ways

 

1) I tried the export menu option by clicking the file and the resulting text file showed some weird sign as under 

clamno lineno freqcode ICN
0101011521587 001 8 " 1502141198141"
0101011521587 002 8 " 1502141198141"
0101011521587 003 8 " 1502141198141"
0101011521587 004 8 " 1502141198141"
0101011521587 005 8 " 1502141198141"
0101011521587 006 8 " 1502141198141"

 

I don't need the ( " ) marks around " 1502141198141"

 

2) i wrote a proc export command as under 

 

proc export data= import
outfile='C:\Users\athapa\Desktop\cc.txt'
dbms= tab replace;
run;

The output is 

 

clamno lineno freqcode ICN
0101011521587 "001 " 8 " 1502141198141"
0101011521587 "002 " 8 " 1502141198141"
0101011521587 "003 " 8 " 1502141198141"
0101011521587 "004 " 8 " 1502141198141"
0101011521587 "005 " 8 " 1502141198141"
0101011521587 "006 " 8 " 1502141198141"

Again the weird quotations around numbers 

 

3) I tried the following code 

data _null_ ;
file "C:\Users\athapa\Desktop\ICN\aaa.txt" delimiter= tab ;
Set import;
if _n_ = 1 then do;
put "clamno" tab
"lineno" tab
"claimfrq" tab
"icn"
;
end;
Put (_all_)(+0);
;
run;

The output this time looks ok but has a lot of spacing, which i do not need. i only need 1 single space between variables.

clamno lineno claimfrq icn
0101011521587 001 8  1502141198141
0101011521587 002 8  1502141198141
0101011521587 003 8  1502141198141
0101011521587 004 8  1502141198141
0101011521587 005 8  1502141198141
0101011521587 006 8  1502141198141
0101011521587 007 8  1502141198141

 

I have attached the datase HAVE with only 10 obs. The actual data has 6111 obs. Since i could not post my sas dataset here. I have attached a text file of the Have sas dataset.

So first we have import this HAVE text file and then export it as text file.

 

Could someone please help me to understand where i am going wrong.

Regards


Accepted Solutions
Solution
‎12-05-2016 11:07 AM
Trusted Advisor
Posts: 1,583

Re: Weird sign appears after exporting a SAS dataset

Posted in reply to athapa1183

If it is not the tab that makes the long space, probably the spaces are part of the columns.

One more try, add a length statemane before the infile in order to truncate the value ti desired length:

 

data import;

length clamno $13 lineno $1 freqcode $1 icn $13;
infile "C:\Users\athapa\Desktop\ICN\QI_30_Medicare_01Dec2016.txt" truncover firstobs = 2 ;
input clamno $ 1-13 lineno $ 15-18 @19 freqcode $1. ICN $ 20-40;
run;

 

Alternatively you may change the INPUT statemnet:

input @1 clamno $13. @15. lineno  $1. @19 $1.  @27 icn $13. ;

 

Is lineno just one digit? can it be longer?  if yes - adapt its length to the maximum available digits ($n. instead $1.)

I freqcode just one digit ? - if need adapt length to maximum digits.

View solution in original post


All Replies
Trusted Advisor
Posts: 1,583

Re: Weird sign appears after exporting a SAS dataset

[ Edited ]
Posted in reply to athapa1183

All your 4 variables are numeric. The ouput is outcome of formats related to them.

You should decide how long should be each variable on output and do yo want leading zeroes.

The first ant the 4th variables should use, probably, format  z13.

while the 2nd and 3rd to be of one dizgit (?!) - then format should be z1. (z + number of digits inclde leading zeroes)

 

Why are you using delimeter tab ? that may make the impression of many blanks between variables.

 

Your code should be:

data _null_ ;
file "C:\Users\athapa\Desktop\ICN\aaa.txt" delimiter= ' ' ;
Set import;
if _n_ = 1 then do;
put "clamno" tab
"lineno" tab
"claimfrq" tab
"icn"
;
end;
Put  calmno Z13.  ' ' lineno Z4. ' ' claimfrq  Z2. ' '  icn z13. ;   /* adapt length if need */
;
run;

 

Occasional Contributor
Posts: 18

Re: Weird sign appears after exporting a SAS dataset

Sorry for late reply.

The variables are all character (They need to be character variables )as i imported them as under

 

data import;
infile "C:\Users\athapa\Desktop\ICN\QI_30_Medicare_01Dec2016.txt" truncover firstobs = 2 ;
input clamno $ 1-13 lineno $ 15-18 @19 freqcode $1. ICN $ 20-40;
run;

I don't want them to be numeric so i cannot use Z. format.

 

I am using tab as i am assuming there is a space between all 4 variables as shown by code above 

sample of exported text file aaa

clamnolineno freqcodeICN
0101011521587 001 8 1502141198141
0101011521587 002 8 1502141198141
0101011521587 003 8 1502141198141
0101011521587 004 8 1502141198141
0101011521587 005 8 1502141198141
0101011521587 006 8 1502141198141
0101011521587 007 8 1502141198141

even though it looks fine here .In my text file i still can see many spaces between 8 & 1502141198141. 

the problem still remains why there are so many spaces.

The code is still doesn't help.

Thanks

Trusted Advisor
Posts: 1,583

Re: Weird sign appears after exporting a SAS dataset

Posted in reply to athapa1183
##- Please type your reply above this line. Simple formatting, no
attachments. -##
The long space is the result of tabing in your text brouser/editor.
Replace the tab with any charscter, including blank, and check again
Occasional Contributor
Posts: 18

Re: Weird sign appears after exporting a SAS dataset

 

i did this 

 

data _null_ ;
file "C:\Users\athapa\Desktop\ICN\aaa.txt" delimiter= '' ;
Set avis.have;
if _n_ = 1 then do;
put "clamno" ''
"lineno" ''
"claimfrq" ''
"icn"
;
end;
Put (_all_)(+0);
;
run;

 

still same results.

Valued Guide
Posts: 2,177

Re: Weird sign appears after exporting a SAS dataset

Posted in reply to athapa1183
Try file statement option DSD with DLM=" "
and adapt your PUT statement to
PUT (_all_)(Smiley Happy ;
Valued Guide
Posts: 2,177

Re: Weird sign appears after exporting a SAS dataset

Posted in reply to athapa1183
Pesky formatting
PUT (_all_)( : ) ;
Occasional Contributor
Posts: 18

Re: Weird sign appears after exporting a SAS dataset

sorry for late reply.

sample of exported text file aaa

clamnolineno freqcodeICN
0101011521587 001 8 1502141198141
0101011521587 002 8 1502141198141
0101011521587 003 8 1502141198141
0101011521587 004 8 1502141198141
0101011521587 005 8 1502141198141
0101011521587 006 8 1502141198141
0101011521587 007 8 1502141198141

even though it looks fine here .In my text file i still can see many spaces between 8 & 1502141198141. 

the problem still remains why there are so many spaces.

 

All the variables are character variables because i imorted them that way as under

 

data import;
infile "C:\Users\athapa\Desktop\ICN\QI_30_Medicare_01Dec2016.txt" truncover firstobs = 2 ;
input clamno $ 1-13 lineno $ 15-18 @19 freqcode $1. ICN $ 20-40;
run;

Solution
‎12-05-2016 11:07 AM
Trusted Advisor
Posts: 1,583

Re: Weird sign appears after exporting a SAS dataset

Posted in reply to athapa1183

If it is not the tab that makes the long space, probably the spaces are part of the columns.

One more try, add a length statemane before the infile in order to truncate the value ti desired length:

 

data import;

length clamno $13 lineno $1 freqcode $1 icn $13;
infile "C:\Users\athapa\Desktop\ICN\QI_30_Medicare_01Dec2016.txt" truncover firstobs = 2 ;
input clamno $ 1-13 lineno $ 15-18 @19 freqcode $1. ICN $ 20-40;
run;

 

Alternatively you may change the INPUT statemnet:

input @1 clamno $13. @15. lineno  $1. @19 $1.  @27 icn $13. ;

 

Is lineno just one digit? can it be longer?  if yes - adapt its length to the maximum available digits ($n. instead $1.)

I freqcode just one digit ? - if need adapt length to maximum digits.

Occasional Contributor
Posts: 18

Re: Weird sign appears after exporting a SAS dataset

Looks fine and works.

Sry I didn't have SAS at home so replying now.

Thank you So much Shmuel.

Super User
Posts: 7,847

Re: Weird sign appears after exporting a SAS dataset

Posted in reply to athapa1183

Do a proc contents on your dataset. This will reveal data types and assigned formats for your variables.

 

To post your SAS data in a readable and usable form here, use the hints from https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...

 

The data step created might even give you enough hints about the nature of your data.

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

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

Discussion stats
  • 10 replies
  • 369 views
  • 0 likes
  • 4 in conversation