topic Re: enclose with double quotation mark in SAS Programming

enclose with double quotation mark

HeatherNewton — Thu, 07 Sep 2023 07:52:17 GMT

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?

Re: enclose with double quotation mark

Kurt_Bremser — Thu, 07 Sep 2023 07:57:54 GMT

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;

Re: enclose with double quotation mark

HeatherNewton — Thu, 07 Sep 2023 08:32:30 GMT

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

proc print data=outout;
run;

I get "hello and not "hello"

why?

Re: enclose with double quotation mark

Amir — Thu, 07 Sep 2023 09:02:57 GMT

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.

Re: enclose with double quotation mark

Kurt_Bremser — Thu, 07 Sep 2023 10:41:19 GMT

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).

Re: enclose with double quotation mark

HeatherNewton — Fri, 08 Sep 2023 02:58:43 GMT

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 “””””””

Re: enclose with double quotation mark

Tom — Fri, 08 Sep 2023 03:03:51 GMT

@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.

Re: enclose with double quotation mark

Tom — Fri, 08 Sep 2023 13:15:28 GMT

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

Re: enclose with double quotation mark

Amir — Fri, 08 Sep 2023 06:14:10 GMT

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.

Re: enclose with double quotation mark

ballardw — Fri, 08 Sep 2023 15:00:18 GMT

@HeatherNewton wrote:

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?

Warning: Any insertion of characters to a variable may fail because of length issues. If your variable is defined as 8 characters and has a current value like ABCDEFG (7 characters total) then adding quotes would attempt to be "ABCDEFG" for a total of 9 characters. Since the variable in the example is defined as 8 then anything over 8 would be discarded.

Is that .000000 to be added to a character or a numeric variable? If character, see the above note. Adding 7 characters is more likely to cause truncation issues than adding 2. If numeric change the format to one that forces enough decimal positions, such as 16.7 . But without knowing the range of values can't tell you the base format to use. Be aware that if you export such an read it with any spreadsheet software those trailing 0 are very likely to disappear for a column treated as numeric.

Re: enclose with double quotation mark

HeatherNewton — Mon, 11 Sep 2023 10:28:26 GMT

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?

Re: enclose with double quotation mark

HeatherNewton — Mon, 11 Sep 2023 11:24:34 GMT

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?

Re: enclose with double quotation mark

Kurt_Bremser — Mon, 11 Sep 2023 11:38:34 GMT

What we see here are the informats used to read the data from an external file. How the data is displayed is controlled by the formats, so we need to see those.

Re: enclose with double quotation mark

Tom — Mon, 11 Sep 2023 15:28:05 GMT

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.

Re: enclose with double quotation mark

HeatherNewton — Tue, 12 Sep 2023 01:49:25 GMT

I want to convert into csv then load into db2
What to do now

Re: enclose with double quotation mark

HeatherNewton — Tue, 12 Sep 2023 02:55:00 GMT

It is then output as $4.0
So pretty normal?

Re: enclose with double quotation mark

HeatherNewton — Tue, 12 Sep 2023 03:11:31 GMT

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;

Re: enclose with double quotation mark

Tom — Tue, 12 Sep 2023 04:01:31 GMT

Please be more systematic is what you are testing and how you reporting your results here.

What did you do to create the SAS dataset? Did you read in a text file, like you implied with the snippet of an INPUT statement? Did you run that code on an IBM mainframe or on an ASCII based machine?

What does the SAS dataset you now have look like? How many variables are their? How many observations? Can you just print the data? Does it look right? If not then perhaps there is something wrong with the step that created the dataset from the text file.

How did you try to create the CSV file? Did you write your own data step? Did you let PROC EXPORT write the data step for you?

If you are confused about what the text files look like then take advantage of the LIST statement in a data step to display what is in the text file. So run something like this on both the orignal text file and on the CSV File you created.

data _null_;
   infile textfile obs=5;
   input;
   list;
run;

How are you trying to load the CSV into DB2? Why didn't you just tell DB2 to read the original text file? Can DB2 not do that?

Re: enclose with double quotation mark

HeatherNewton — Tue, 12 Sep 2023 05:21:34 GMT

so this is the sequence
read from text file

data SDATA2.HKPBIM_STATIC;
infile "&G-IRF_RAW_PENDING_PATH./hkpb.static.txt" RECFM=N'
readdata:
input hkpbim_int_rate $EBCDIC4.0
.
.
.
FORMAT hkpbim_int_rate $4.0

THEN SDATA2.HKPBIM_STATIC is used to create wdata.installment using data step and nothing is done to the variable  hkpbim_int_rate  and wdata.installment is the file I want to convert to csv using the following code:
filename exprt "/toutput/path/installment.csv" encoding="utf-8";
proc export data=output.installment outfile=exprt dbms=csv replace; run;

now in the output most lines can be loaded into db2 using load insert command but as shown in picture attached where red line added, this row suddenly cannot be loaded because the input splitted into two lines unlike lines before and after it. I was told it was seperated by a 'cache return', not too sure but it sound likes it from a senior..

and senior asked me to put "" in the field to resolve. I tried but I end up getting """"""""""""" as the input instead very weird. so my question is how to have this column load into db2 properly, right now it gets rejected? I will show you how sas display these in a bit.... I must convert into csv first..

Re: enclose with double quotation mark

HeatherNewton — Tue, 12 Sep 2023 06:41:40 GMT

oh in sas it is actually blank

see attached next to 1077.27 (row 9243, col hkpbim_int_rate)