You should literally code it with the literal WORK. WORK is the libname. SAS will then retrieve the path for you.
%let temp_path=%sysfunc(pathname(WORK));
Jim
If you're exporting to an Excel file, why not just export from Excel as fixed length/fixed position text? Excel likes to do csv or tab delimited text, but you can export it to a .prn file which is space filled and then rename it to .txt.
Jim
Well, SAS can definitely do it. It's been a while since I've had the occasion, but you'd set up FILENAME before a Data step. In your Data step, you'd have a FILE statement corresponding to the FILENAME. Then you'd use the Put command to write to the file with the @ symbol to position the pointer to where you want each column. This will give you fixed width columns and a fixed length output record. Something along the lines of the below. The @ followed by the number starts each column, and the LRECL (logical record length) sets the overall length of the output record.
FILENAME Out_File 'C:\my_directory\my_file.txt' LRECL=46;
Data _NULL_;
Set Have;
File Out_File;
Put @1 ID
@10 First_Name
@35 Middle_Initial
@36 Last_Name
;
RUN;
Obviously, that's not using Query Builder, but that's how I would build a fixed position text output file. It's really a pretty easy piece of code. Maybe you could get Query Builder to do it, but that's really not what Query Builder is for. A Data step is ideal for what you want to do. Using Query Builder is like putting a round peg in a square hole. If you pound on it hard enough, it may eventually work, but it's a lot easier if you just use a peg that is the same shape as the hole.
Jim
@Boswser wrote:
Or, alternatively, anything in the EXPORT step in SAS EG that could output a fixed-width file?
As a matter of fact there is. Click on "Share" in the Output window, then "Export", just as you normally would, but for the file type, select Text (space delimited).
Now, will that allow you to precisely plot the widths of each column and the position where each column starts? I believe it will, but you would have to set widths of the columns via FORMAT statements for each variable.
Jim
Well, crap. 😞
I just tried my Export idea, and it doesn't work. It produces space delimited but not fixed position text.
I think you have to use the FILENAME - FILE set up I explained earlier. As @Sajid01 pointed out, you should have permission to write to your WORK directory.
You can find your work directory as follows:
PROC OPTIONS OPTION=WORK;
RUN;
You should get something like this in your log:
SAS (r) Proprietary Software Release 9.4 TS1M6 WORK=X:\saswork\jbarbour\_TD56672_APSWP2335_\Prc2 Specifies the libref or location of the Work library.
Cut and paste the WORK=xxxxx that it gives you. In my case it is X:\saswork\jbarbour\_TD56672_APSWP2335_\Prc2 into that FILENAME that I mentioned earlier as in the below. This is just an example, you'll have to use real column names and formats, of course.
FILENAME Out_File 'X:\saswork\jbarbour\_TD56672_APSWP2335_\Prc2' LRECL=46;
Data _NULL_;
Set Have;
File Out_File;
Put @1 ID
@10 First_Name
@35 Middle_Initial
@36 Last_Name
;
RUN;
Once you create the file, you will need to download it from your SAS server to your machine. Do not close your SAS EG session until you have downloaded the file. Typically, WORK locations are deleted/cleaned up when the session terminates.
Jim
First the EXPORT menu in EG does not appear to support fixed position file generation.
Second your data step is confused, for example you are overwriting the value of CMPY with the value of ST_CD since you are writing both starting in the second character of the line.
It will be easier if you list the variables in the order that you want them to appear in the text file.
It might be easier to just use formats instead of the @ notation. For example if CMPY is supposed to be one character field then use a format with a width of one. If it is character use $1. as the format and if it is a numeric variable just use 1. instead.
But you can mix them. For example if there is large difference between the number of characters on the line reserved for the variable and the length of the values you want to write. So you wanted the DATE starting at column 1 and the NAME starting at column 20, but you are writing the dates using the DATE9. format. Then you want 11 spaces between the end of the date value and the start of the name value.
put date date9. @20 name $30. .....
Or if the values you have for a variable is longer than the space reserved for it in the fixed format you are generating then you will need to use a format to limit the number of character written. For example if your NAME field has values that are longer than 30 characters if you just write it with
@20 name
it might write some characters past column 50 and into the space reserved for the next field.
Finally you need a method to move the file from your SAS server to the location you really want to put it. You can use the file download task in Enterprise Guide to copy it to your local machine. Or you can ask the SAS administrator to mount the shared file location you want to write the file to as a Unix folder on the server where SAS runs. Then you could write directly to that folder instead of writing to some temporary file and downloading it.
You should be able to schedule a Copy Files task as part of your EG Process Flow. Copy Files can both upload and download, so be sure to check the right boxes when you set it up. You'll also need to check "Fix line ending characters..." (see screen print, below).
Jim
Please have a look at my post that is at the end of this thread. There is no need to use proc options.
That approach is used all the time.
Changing folder names/ locations will not have any impact.
If PSTL_ZIP is character use $9. format.
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 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: