SAS PROC SQL: How to add trailing spaces?

Question

I have a problem like this at work:

Some system requires a specific input like:

0000051420189999999                         ABC12345678<20 SPACES>;

Which is a (some digit) + Sample_Date + (some digit) + x amount of spaces + Sample_KEY + y amount of spaces.

I read a few online post about string concatenation and converting date into string. But the trailing spaces are always eliminated at the end.

PROC SQL;
CREATE TABLE WORK.MAINFRAME_FILE AS 
SELECT CAT('0000', 
           COMPRESS(PUT((Sample_Date), MMDDYY10.), '/',),
           '9999999                         ', 
           Sample_KEY,
           '                                          ') 
           AS INPUT FORMAT=$100.
FROM WORK.TEST
;
QUIT;

How can I add some trailing spaces at the end, say 20? Thanks

Do you need that value in a variable or written to a text file? If the latter then use pointer controls on the PUT statement. — Tom, May 14 '18 at 22:37
I've usually only seen this with text files as fixed width files. Is that what you're trying to do here? — Reeza, May 14 '18 at 23:00
@Reeza Yes, you are correct. I am using virtual version of SAS EG, it has no access to write file. But I can manually export a dataset, or setup export as a "step". Ultimately I need it in a text file format... — George, May 15 '18 at 20:56

score 2 · Answer 1 · answered May 14 '18 at 20:41

2

If you're putting this into a SAS dataset, the spaces are there automatically as long as the field length is defined sufficiently. All SAS columns are by definition space-padded to full length. You might not see them depending on how you're looking at the field, but they're there internally.

If you're putting this into a SQL Server table or similar, it will probably depend on the particular RDBMS you're connecting to how to accomplish this.

SAS example - note x has the ascii representation '20'x which is space:

PROC SQL;
CREATE TABLE WORK.MAINFRAME_FILE AS 
SELECT CAT('0000', 
           COMPRESS(PUT((today()), MMDDYY10.), '/',),
           '9999999                         ', 
           Name,
           '                                          ') 
           AS INPUT FORMAT=$100.
FROM SASHELP.CLASS
;
QUIT;

data your_file;
  set work.mainframe_file;
  x = substr(input,60,1);
  put x= $HEX2.;
run;

answered May 14 '18 at 20:41

Joe

62,789
6
49
67

What does the substr(input, 60,1) do? Is it for demonstrating there is an actual space there? – George May 14 '18 at 20:50
It is. (There is always an actual space there in a SAS dataset, whether I put it there or not - leave off the last `' '` and you can still see it there.) – Joe May 14 '18 at 20:59
It might be clearer to use the `repeat` function rather than typing out n spaces like that. – user667489 May 15 '18 at 12:56
1

@Joe I am using virtual version of SAS EG, it has no access to write file. But I can manually export a dataset, or setup as a step. Ultimately I need it in a text file format. I used the sample code above, but it still does not have the spaces at the end when exported (manually). – George May 15 '18 at 20:57
Can you copy/paste out of the virtual client? If so, you could write it to the log and copy it out. – Reeza May 16 '18 at 00:51
@George Not sure how you’re exporting, might want to be more clear. Also not sure what a virtual client means. But it’s probably the method you’re using to write or whatever the data, SAS in some cases by default ignores trailing spaces. I can assure you they are there in the dataset, but you may need to output them explicitly. – Joe May 16 '18 at 04:33
@Joe The SAS Enterprise Guide software is not installed locally, instead it's a virtual version (like in the cloud or something...) The normal process of writing to a local disk does not work, it limited the write access to where the software is installed to. Even with the log the trailing spaces are ignored. – George May 16 '18 at 17:36

SAS PROC SQL: How to add trailing spaces?

1 Answers1