BookmarkSubscribeRSS Feed
HeatherNewton
Quartz | Level 8

if I want to enclose content of a field with double quotation mark, how to do

e.g. hello becomes "hello"

 

also if I want add .000000 to content of a field, how do I do it?

26 REPLIES 26
Kurt_Bremser
Super User

Try

length field $10;
field = "hello";
field = quote(field);

For your second question: if this is about a numeric variable, use a format which displays the desired decimals:

number = 10;
format number 20.6;
HeatherNewton
Quartz | Level 8
data outout;
length field $10;
field = "hello";
field = quote(field);
run;

proc print data=outout;
run;

I get "hello and not "hello"

why?

Amir
PROC Star

Hi,

 

As the field variable has a length of 10, any text less than that length is padded by spaces. So try using the strip() function:

 

data outout;
  length field $10;
  field = "hello";
  field = quote(strip(field));
run;

 

 

 

Thanks & kind regards,

Amir.

Tom
Super User Tom
Super User

@Amir wrote:

Hi,

 

As the field variable has a length of 10, any text less than that length is padded by spaces. So try using the strip() function:

 

data outout;
  length field $10;
  field = "hello";
  field = quote(strip(field));
run;

 

 

 

Thanks & kind regards,

Amir.


Don't use STRIP() as that will remove the LEADING spaces and therefore change the MEANING of the value.

Use TRIM() as it will remove just the TRAILING spaces.  SAS comparisons will ignore trailing spaces so removing them will not change the meaning.  

Amir
PROC Star

Hi,

 

True in the case of leading spaces. I suppose I couldn't convince you it was a typo! 😂

 

@HeatherNewton please pay heed to the good practice that @Tom has highlighted.

 

 

Thanks & kind regards,

Amir.

Kurt_Bremser
Super User

QUOTE() does not automatically strip leading and trailing blanks; use the STRIP()function, as already suggested.

I have gotten so used to more modern functions and routines (CATS, CATX, CALL SYMPUTX) that I tend to forget the "hidden blanks" (Maxim 46).

HeatherNewton
Quartz | Level 8
Sorry it didnt work that field become ””””””
Let me clarify
What happened was i try to convert sas dataset to csv
This particular column was something like ^@000^@^@
After converted to csv, it became
22,33, hello,^@000
^@^@,44,55
So it has a lot of space in it and split into two lines in csv
I was told it is due to a cache return and so should put it inside “”

But i tried concatx and quote both give me “””””””
Tom
Super User Tom
Super User

How did you create the CSV file?  SAS knows how to write delimited files and does not add extra spaces between the fields (or any space at all if the value is missing).

 

So if your file has 5 variables.  Let's say they are named VAR1, VAR2 ... up to VAR5.  Then to generate a CSV file from it just do this:

filename csv 'somefilename.csv';
data _null_;
  set have;
  file csv dsd ;
  put var1-var5 ;
run;

Make sure the logical record length (the LRECL= option of the FILE statement) is long enough for the longest values to avoid it inserting a line break when the values will not fit on the line.  The default LRECL is now 32,767 bytes.  But you can use values much larger if you need.

 

Also make sure none of the values contain the end of line characters you are using.  On Unix the default end of line is just a LineFeed character ('0A'x) and on Windows it is the two byte sequence of a Carriage Return character ('0D'x) and Line Feed character.  Otherwise your generate file will be impossible to parse.

 

With the DSD option on the FILE statement SAS will default the delimiter to a comma (you can change it with the DLM= option). And it will add quotes around any value that contains the delimiter to insure that the generated lines can be parsed without confusion.

 

If for some strange reason you want to force SAS to add quotes around a particular variable you can use the ~ modifier on the PUT statement.  So for example if you wanted quotes to be added around the value of VAR3 whether or not the value is something that needs to be quoted you could change the PUT statement to :

put var1 var2 var3 ~ var4 var5 ;

Let's try to convert your comment into actual data:

data have;
  infile cards dsd truncover;
  input var1 var2 var3 :$10. var4 :$30. var5 var6 ;
cards;
22,33,hello,^@000^@^@,44,55
;

Now we can generate a normal CSV file like this:

375  data _null_;
376    file log dsd;
377    set have;
378    put var1-var6;
379  run;

22,33,hello,^@000^@^@,44,55

Or we can add ~ modifier for the character variables so it generates a file that has extra quotes added that do nothing to make the file more readable.

399  data _null_;
400    file log dsd;
401    set have;
402    put var1 var2 var3 ~ var4 ~ var5 var6;
403  run;

22,33,"hello","^@000^@^@",44,55

 

HeatherNewton
Quartz | Level 8

I reread the program and realised these data is put in like this

 

input

int_amt S370fpd5.2

int_rate $EBCDIC4.0

 

 

could S370fpd5.2 or S370fpd5.2 make numbers display in a strange way like symbol?

 

 

HeatherNewton
Quartz | Level 8

this second column in $EBCDIC4.0 is giving me trouble, in sas it looks empty and after converting to csv it looks like & (1 digit) or 0 (but when use len(), says 2) 

what is the best way to convert this in csv and have it as utf8?

Tom
Super User Tom
Super User

What are you doing?  Looks like you are reading in a file that has data in fixed columns.  Data that was generated on an IBM mainframe so you need to use special informats to read the values.  Once you have read the text variable using the $EBCDIC informat the values should be ASCII now.  So unless you plan to send the file to an IBM mainframe then you do not want to use the $EBCIDIC format to display the data since that will convert back into EBCIDC (which will look like gibberish if interpreted as ASCII character codes).

 

How does the CSV file get into the picture?  I think you should solve your data input issue first before working on your output issues.

HeatherNewton
Quartz | Level 8
I want to convert into csv then load into db2
What to do now
HeatherNewton
Quartz | Level 8
It is then output as $4.0
So pretty normal?
HeatherNewton
Quartz | Level 8
If output as $4.0 why i am getting symbols like ^@000^@^@ but splitted into two lines with cache return after convert to csv as original sas is showing 0 (but length is 2)

I convert this way

Filename exprt “/path/to/test.csv” encoding=“utf8”;
Proc export data=out.test outfile=exprt dbms=csv replace; run;

SAS Innovate 2025: Call for Content

Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!

Submit your idea!

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
  • 26 replies
  • 1868 views
  • 1 like
  • 5 in conversation