topic Re: Data truncation of rows while running proc sql in sas macro, please help. in SAS Programming

Data truncation of rows while running proc sql in sas macro, please help.

Ashfaq_BOA — Sun, 15 Sep 2013 15:14:26 GMT

Hi folks, i am trying to generate a data file by running a proc sql step having a select query from database table.

i am selecting many columns in the multi-table select.

and set options as "options nonumber nocenter nolabel nodate pagesize=MAX linesize=MAX;"

However the data in each row is getting truncated to 256 chars in the listing file that is generated ( while my data is way beyond 400 chars per selected row)

Please could someone help what other options i may have to achieve my requirement to dump data from database tables to a .txt file (to be sent to other downstream application).

Please provide a pseudo code if possible. ( i just started studying and coding sas macro and have no formal training with this tech)

Best Regards

Re: Data truncation of rows while running proc sql in sas macro, please help.

jakarman — Sun, 15 Sep 2013 15:45:22 GMT

The default linesize off all IO in Windows/Unix is that length of 255.

At the options statement it is just defning the old classic dms approach off the output file (output window / log window / editor window).

Defining an interface file in the old historical way of defining an file lays-out by yourself will need some programming as:

- using the datastep

- dinfing the needed technical attributes along with the file statement (filename)

- using put statements

As being very tedious and needing those skills all those interface connectors has been introduced as.

- using SQL with SAS/ACCESS to exterenal dbms

- XML file formatting (many subtypes)

- proc import/export wiht file pc file formats

- Encoding standards like codepages Unicode

All is related and dependant to the used environments and versions. Possible having some additional restrictions

You are possible feeling with this you have given to little information to give a good answer.

Re: Data truncation of rows while running proc sql in sas macro, please help.

Ashfaq_BOA — Sun, 15 Sep 2013 15:57:25 GMT

Thank you Jaap with your swift response.

i googled through many pages to try file statement and also ods clause, but since i am novoice with this tech i am not sure how this goes with "proc sql"

The other options you suggested may work well. However ...

- i have-to/am invoking the sas macro from AIX unix shell script to be able to automate this as a daily job producing the .txt file output

- then use the ndm on unix to send the file to a downstream system.

( my job is at stake :S )

Re: Data truncation of rows while running proc sql in sas macro, please help.

Reeza — Sun, 15 Sep 2013 16:52:14 GMT

Is the format or the length of the variable set to 256? Run a proc contents on your data to check this.

Are you talking about a row or a specific character variable?

Can you post your code and the sample output?

Re: Data truncation of rows while running proc sql in sas macro, please help.

Ashfaq_BOA — Sun, 15 Sep 2013 17:15:17 GMT

Hi Reeza

Below is the excerpt of the code i am trying (cannot put the entire code for compliance reason, the colums in the select are | concatenated to form a single line for each row returned.

i have set the options in data step as "pagesize=MAX linesize=MAX;" The MAX for linesize defaults to 256, even setting to a bigger value say 1000 does not desired output.

=============================

proc sql

connect to db2(database=&dbase authdomain="AAA");

create table Gen_UnionQuery_Data as

select * from connection to db2

(

SELECT T.OUTPUT FROM (

<<<< select query with many coulums from a multi-table join >>>>

);
disconnect from db2;
quit;

data temp;
set Gen_UnionQuery_Data;
run;

options nonumber nocenter nolabel nodate pagesize=MAX linesize=MAX;
titlel;
proc print data=temp noobs;
run;

=============================

Please check an help ...

Re: Data truncation of rows while running proc sql in sas macro, please help.

jakarman — Sun, 15 Sep 2013 17:18:26 GMT

Ashfaq, as you are starting to code in SAS avoid the word macro for that.
SAS-macros are used in advanced techniques modifying preparing the sas code in a more dynamic way.

Understandable samples to do your work will be most usefull for you. The mind loop for that is: you need to know the solution to find examples.

As yoy are telling having varaibles with al length up to 400 this could be another issue resulting in a tremendous long record.

SAS(R) 9.4 Statements: Reference (File statement)

SAS(R) 9.4 Statements: Reference (put statement)

You could process the sas dataset "temp" to dat defining a fixed structure of your txt file,

As you are indicating db2. It could be a mainframe dbms. What machine-types are you working on? .

Re: Data truncation of rows while running proc sql in sas macro, please help.

Ashfaq_BOA — Sun, 15 Sep 2013 17:34:46 GMT

Sir, i am trying this requirement on AIX (unix) machine. And the db2 database i am connecting to in proc sql is also hosted on AIX server. ..

i have mostly worked with oracle databases over unix , and generating a flat file by querying db tables does not have such limitations. SAS is deemed much roboust and i am needing to study more to achieve a often generic need.

With Regards

Re: Data truncation of rows while running proc sql in sas macro, please help.

jakarman — Sun, 15 Sep 2013 18:46:02 GMT

Using Unix (Aix Linux IOS Android etc) is a little bit different as working on Windows. As:

- no drive-letters but mount-points.

- using the / instead of the \

- case sensitive as file namings in upper and lower case are really different files

For advice: use just lower case within file names.

- cr-lf (Windows) convention as being lf (or newline nl) in Unix

- using terminal emulators like putty and winscp (filetransfer) being normal to go to the black hole access.

- a simple effective but mostly very bad understood security approach.

When being a newby in this it is very easy to maker failures on those Unix limitations. Adding some Sas behavior on top of that can become confusing.

With SAS EGuide (BI/DI server) there is a good transparant approach for most common tasks.

Building and testing the wanted sas code should be easy with that.