Can Query Builder in SAS EG be used to output fixed-width columns?

[Boswser]

Thanks for any help! I’ll try to be concise.

I am new to SAS and have inherited a SAS run that outputs a table with 15 columns, which I export as an excel file and deliver.

The recipient would now like it as a .txt file with fixed-width columns, and has provided the character limit/width for each column.

Is this something that can be done in query builder, so I can just query the existing table rather than modifying the code in the run? I tried filling the field for ‘Length’ when you add a column to query builder, but it did not have the desired result.

I have attempted to use filename and PUT statements to output a .txt file, but am running into access issues I’m currently working on with the admin team. I’m hoping I can just do this in query builder to get it done but I’m not sure if it’s possible to have QB output a results table with fixed width columns?

[jimbarbour]

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

[Boswser]

Thank you Jim. The file is written to a specific shared folder so it can be automatically picked up by another process, which is why it needs the fixed widths. So we want it to export right from SAS EG and into that shared folder.

[jimbarbour]

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]

Thanks Jim! The filename data step is the solution I am currently working on. I am running into permission issues when trying to write a file directly from the SAS program, rather than exporting the results to a file within SAS EG.

The error I get is: Insufficient authorization to access /opt/sas/sas_control/config/Lev1/SASApp/C:\Users\ShaneLu\Desktop\testfile.txt

I am currently working with my IT team to see if there is an access/permission they can give me so that I can get this to work.

Are you aware of any way circumvent this so I can write the file to my desktop? I want to put it there first so I can make sure it’s outputting correctly, before I change the output path to the desired folder.

[Boswser]

Or, alternatively, anything in the EXPORT step in SAS EG that could output a fixed-width file?

Unfortunately the current issues I’m running into involve that error I mentioned above when trying to write a file using just the SAS Program. I was hoping to find a way to circumvent that, as I don’t know how long IT will take to fix this, or if they even can.

[jimbarbour]

---

@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

[jimbarbour]

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

[Boswser]

Thanks again Jim! I am definitely making progress here.

So the good news is—that worked to get me a file! I’m able to write to the work folder.

***(EDIT because I realized I was using PUT incorrectly, updated the code below)

This is my code:

filename out_file “/sas/work/saswork3/SAS_work21C400006785_dr877/SAS_workA1B200006785_dr877/testfile.txt

DATA FIXEDTEXTFILE;
SET work.results;

FILE out_file;

PUT
@1 ID_NB
@27 CMPY
@30 AREA_SZ_RT_CD
@34 EF_DT
@44 STSTC_RPRT_LT
@48 ADRS_LN_1
@89 CTY_NM
@120 ST_CD
@123 PSTL_ZIP
;
RUN;

The only thing is, I need that last column—PSTL_ZIP—to be a fixed-width of 9. How do I make that happen?

[Tom]

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.

[Boswser]

Thanks Tom—I realized what you pointed out as soon as I replied, then fixed my code and edited the post. I appreciate it 🙂

So as you mentioned, I now have the issue of trying to get this file off the SAS server and where it needs to be. Ideally we want this to be an automated process.

I am pretty new to SAS and SAS EG—can I insert a step into this run that plucks the file out of the WORK folder it ends up in, and deposits it somewhere else (in my case, this would be a shared folder my team and their team can access)?

[jimbarbour]

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

[Boswser]

This function definitely seems like what I want! I tested it and it works to get the file to my desktop. But...sadly there is a different issue.

My .txt file is written to the temp folder in WORK. I just realized that the name of WORK’s folders change slightly every time I close and reopen the program, and thus the filename path in my code no longer works. To fix, I have to manually run the PROC OPTIONS step to get the new name of the WORK path, and replace it in the FILENAME part of my code. So of course that won’t work if we want this to be automated 😞

Is there anywhere I can write this file to where the folder name won’t change every time I open the program? The goal is to run this automatically via Windows Task Scheduler, so really this process flow should never be open (unless I need to change something). If it’s not open, does the WORK folder even exist?

Alternatively, is there something I can write into the code that will always grab whatever the existing WORK filepath happens to be at that time? Or even...run the PROC OPTIONS program separately, then have my filename program use the specific “WORK=“ result that it generates?

[Sajid01]

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.

[Tom]

If PSTL_ZIP is character use $9. format.