topic Re: export to file text without headers issue in SAS Programming

export to file text without headers issue

dcortell — Thu, 27 Oct 2016 15:36:59 GMT

Hi People

In few words, I'm trying to export a table composed by characters column to a unique text file with no headers and with each column assigned to a specific position in the text file, which has a ficed lenght

the code is below. the first part of the code create the table, the second one is supposed to export the table to the text file

the issue is related to the first four columns. for some reason I get the following in the log:

NOTE: Variable 'Type_of_file 'n is uninitialized.
NOTE: Variable ' Bank_no 'n is uninitialized.
NOTE: Variable ' Branch_no 'n is uninitialized.
NOTE: Variable ' Product'n is uninitialized.
NOTE: The file '/intl/iccs_emea/dcortell/facturacion2.txt' is:
Filename=/intl/iccs_emea/dcortell/facturacion2.txt,
Owner Name=dcortell,Group Name=bis,
Access Permission=-rw-r--r--,
Last Modified=27Oct2016:08:10:16

NOTE: 14314 records were written to the file '/intl/iccs_emea/dcortell/facturacion2.txt'.
The minimum record length was 147.
The maximum record length was 147.
NOTE: There were 14314 observations read from the data set WORK.FACTURACIONTEXT.
NOTE: DATA statement used (Total process time):
real time 0.07 seconds
cpu time 0.03 seconds

So that the first four columns are not initialized and so the corresponding spaces in the txt files are not populated.

The weird thing is that this happen also for others tables which I have tried to export in the same way to a text file.

The export goes fine for all the others columns except as said for the first four.

really no idea about what could cause this issue as

a. the source table is created correctly

b. other colleagues of mine have run the same code without any problem regarding the first four colums

Any hints or help are welcomed

Bests

Davide

%_eg_conditional_dropds(WORK.facturaciontext);
proc sql;
create table facturaciontext as
select 
Type_of_file length=2 format=$2. informat=$char2. as Type_of_file,
Bank_no length=4 format=$4. informat=$char4. as Bank_no,
Branch_no length=4 format=$4. informat=$char4. as Branch_no,
Product length=3 format=$3. informat=$char3. as Product ,
Num_Contrato,
('00'||put(Sub_Tipo_Producto, z1.)) length=3 format=$3. informat=$char3. as Sub_tipo_Producto ,
Referencia_TRC,
currency length=3 format=$3. informat=$char3. as currency,
(put(year(Fecha_Alta),z4.) || put(month(Fecha_Alta),z2.) || put(day(Fecha_Alta), z2.)) length=8 format=$8. informat=$char8. as Fecha_Alta,
Monthly_spend length=15 format=$15. informat=$char15. as Monthly_spend ,
('+'||case when Average_spend is missing then "000000000000" else substr(put(Average_spend, z15.2),1,12) end||case when Average_spend is missing then "00" else
substr(put(Average_spend, z15.2),14,2)end)  length=15 format=$15. informat=$char15. as Average_spend ,
('+'||case when Last_year_annual_spend is missing then "000000000000" else substr(put(Last_year_annual_spend, z15.2),1,12)end||case when Last_year_annual_spend is missing then "00" else
substr(put(Last_year_annual_spend, z15.2),14,2) end) length=15 format=$15. informat=$char15. as Last_year_annual_spend,
('+'||case when Acumulated_annual_spend is missing then "000000000000" else substr(put(Acumulated_annual_spend, z15.2),1,12) end||case when Acumulated_annual_spend is missing then "00" else
substr(put(Acumulated_annual_spend, z15.2),14,2) end) length=15 format=$15. informat=$char15. as Acumulated_annual_spend  ,
('+'||case when Faturacion_Acumulada_Mes is missing then "000000000000" else substr(put(Faturacion_Acumulada_Mes, z15.2),1,12) end||
case when Faturacion_Acumulada_Mes is missing then "00" else substr(put(Faturacion_Acumulada_Mes, z15.2),14,2) end) length=15 format=$15. informat=$char15. as Faturacion_Acumulada_Mes,
Periodicty length=1 format=$1. informat=$char1. as Periodicity,
Fill_in,
Billing_date length=8 format=$8. informat=$char8. as Billing_date,
End_of_file
from work.facturacion t1;
quit;


data _null_ ;
set work.facturaciontext ;
file '/intl/iccs_emea/dcortell/facturacion2.txt' lrecl=148;

put @1 Type_of_file +(-1) @3 Bank_no +(-1) @7 Branch_no +(-1) @11 Product +(-1) @14 Num_contrato +(-1) @21 Sub_Tipo_Producto +(-1) @24 Referencia_TRC
+(-1) @44 currency +(-1) @47 Fecha_Alta +(-1) @55 Monthly_spend +(-1) @70 Average_spend +(-1) @85 Last_year_annual_spend +(-1) @100 Acumulated_annual_spend +(-1)
+(-1) @115 Faturacion_Acumulada_Mes +(-1) @130 Periodicity +(-1) @131 Fill_in +(-1) @139 Billing_date +(-1) @147 End_of_file +(-1) ; 

run;

Re: export to file text without headers issue

Shmuel — Thu, 27 Oct 2016 17:22:12 GMT

The 4 variables are probably alphanumeric, then their length sholud be defined as $2 , $4 etc.

your code with sliht change may do the work:

select 
Type_of_file length=$2 format=$2. informat=$char2. as Type_of_file,
Bank_no length=$4 format=$4. informat=$char4. as Bank_no,
Branch_no length=$4 format=$4. informat=$char4. as Branch_no,
Product length=$3 format=$3. informat=$char3. as Product ,

pay attention to the 'n after the variable name in the message:

NOTE: Variable 'Type_of_file 'n is uninitialized.

SAS assumed the variable is of numeric type because you defined length=2 (instead $2);

Re: export to file text without headers issue

data_null__ — Thu, 27 Oct 2016 17:45:18 GMT

NOTE: Variable 'Type_of_file 'n is uninitialized.

SAS assumed the variable is of numeric type because you defined length=2 (instead $2);

pay attention to the 'n after the variable name in the message:

That syntax is for a name literal, not an indication of type. Notice below that there appears to be a blank at the end of Type_of_file and at the beginning and end of Bank_no etc. Those are actually 'A0'X the non-breaking space. Where they came from and what it means to the OPs question I have no idea. 🙂

NOTE: Variable 'Type_of_file 'n is uninitialized.
NOTE: Variable ' Bank_no 'n is uninitialized.
NOTE: Variable ' Branch_no 'n is uninitialized.
NOTE: Variable ' Product'n is uninitialized.

Re: export to file text without headers issue

dcortell — Thu, 27 Oct 2016 18:00:16 GMT

Hi Samule thank you for the reply but your suggestion seems not working: The log gives me error like

27 Type_of_file length=$2 format=$2. informat=$char2. as Type_of_file,
_
22
200
28 Bank_no length=$4 format=$4. informat=$char4. as Bank_no,
_
22
200
29 Branch_no length=$4 format=$4. informat=$char4. as Branch_no,
_
22
200
30 Product length=$3 format=$3. informat=$char3. as Product ,
_
22
200
ERROR 22-322: Expecting an integer constant.

ERROR 200-322: The symbol is not recognized and will be ignored.

In addition, the four column which are creating problems were created with the following code:

proc sql;
create table facturacion as
select
('50') length=2 format=$2. informat=$char2. as Type_of_file,
case when t1.santander=1 then '0049' else t1.Empresa_Banco end length=4 format=$4. informat=$char4. as Bank_no,
case when t1.santander=0 then '8840' else t1.Centro_Sucursal end length=4 format=$4. informat=$char4. as Branch_no,
('530') length=3 format=$3. informat=$char3. as Product,

So pretty sure they should have been identified as character by the code.

what do you think?

Re: export to file text without headers issue

Tom — Thu, 27 Oct 2016 18:32:53 GMT

Your errors are caused by non 7bit ASCII characters in your source code. Change the 'A0'x characters back to blanks and you perhaps can eliminate that strange messages about invalid or uninitalized varaibles.

But you are working way too hard for this problem. Eliminate the SQL step and just write the text file directly from the source data.

If your actual values are never too long for the space reserved for them then you might just need to use the @ pointer control.

data _null_ ;
  set work.facturacion ;
  file '/intl/iccs_emea/dcortell/facturacion2.txt' lrecl=148;  
put
@1 Type_of_file
@3 Bank_no
@7 Branch_no
@11 Product
@14 Num_contrato
@21 Sub_Tipo_Producto
@24 Referencia_TRC
@44 currency
@47 Fecha_Alta
@55 Monthly_spend
@70 Average_spend
@85 Last_year_annual_spend
@100 Acumulated_annual_spend
@115 Faturacion_Acumulada_Mes
@130 Periodicity
@131 Fill_in
@139 Billing_date
@147 End_of_file
;

Or you could use formatted PUT statement to force it to write the values in the space reserved or style desired.

put
...
@14 Num_Contrato Z3.
...

Re: export to file text without headers issue

dcortell — Thu, 27 Oct 2016 18:55:56 GMT

Hi Tom

Sorry but from my point of view the sql step in necessary in order to convert some numeric columns to specific text values I need in the output file. Like, for isntance

(put(year(Fecha_Alta),z4.) || put(month(Fecha_Alta),z2.) || put(day(Fecha_Alta), z2.)) length=8 format=$8. informat=$char8. as Fecha_Alta,
Monthly_spend length=15 format=$15. informat=$char15. as Monthly_spend ,
('+'||case when Average_spend is missing then "000000000000" else substr(put(Average_spend, z15.2),1,12) end||case when Average_spend is missing then "00" else
substr(put(Average_spend, z15.2),14,2)end) length=15 format=$15. informat=$char15. as Average_spend

These examples would be difficult to manage in a data step

About the non 7bit ASCII characters in my code, can you provide some examples?

As for my last post the 4 variables involved in this unitialized errors are defined like ex.

('01') length=2 format=$2. informat=$char2. as Type,
case when t1.santander=1 then '0049' else t1.Empresa_Banco end length=4 format=$4. informat=$char4. as Bank_no,
case when t1.santander=0 then '8840' else t1.Centro_Sucursal end length=4 format=$4. informat=$char4. as Branch_no,
('530') length=3 format=$3. informat=$char3. as Product,

So not sure where the the characters your are mentiong are located. To me it seems pretty a declaration of character columns

in any case I post here the full code but pretty sure the code is fine

proc sql;
create table facturacion as
select 
('50') length=2 format=$2. informat=$char2. as Type_of_file,
case when t1.santander=1 then '0049' else t1.Empresa_Banco end length=4 format=$4. informat=$char4. as Bank_no,
case when t1.santander=0 then '8840' else t1.Centro_Sucursal end length=4 format=$4. informat=$char4. as Branch_no,
('530') length=3 format=$3. informat=$char3. as Product,
case when santander=0 then "9999999" else put(t1.Contract, z7.) end length=7 format=$7. informat=$char7. as Num_Contrato,
Sub_Tipo_Producto,
('99999999999999999999') length=20 format=$20. as Referencia_TRC,
('EUR') as currency,
t1.Fecha_Alta,
('+00000000000000') as Monthly_spend,
t2.Average_spend format=15.2,
t3.Last_year_annual_spend format=15.2,
t2.Acumulated_annual_spend format=15.2,
t4.Faturacion_Acumulada_Mes format=15.2,
('M') as Periodicty,
('        ') length=8 format=$8. as Fill_in,
(put(year(intnx('month', today(),&pastmonth,'e')),z4.) || put(month(intnx('month', today(),&pastmonth,'e')),z2.) || put(day(intnx('month', today(),&pastmonth,'e')), z2.)) as Billing_date,
('  ') length=2 format=$2. as End_of_file
from work.new_contract t1
left join work.ytdavg t2 on t1.acct11=t2.acct11 and t1.Fecha_Alta=t2.Fecha_Alta and t1.Fecha_Baja=t2.Fecha_Baja and t1.id_revised_fiscal2=t2.id_revised_fiscal2
left join work.lybb t3 on t1.acct11=t3.acct11 and t1.Fecha_Alta=t3.Fecha_Alta and t1.Fecha_Baja=t3.Fecha_Baja and t1.id_revised_fiscal2=t3.id_revised_fiscal2
left join work.mm t4 on t1.acct11=t4.acct11 and t1.Fecha_Alta=t4.Fecha_Alta and t1.Fecha_Baja=t4.Fecha_Baja and t1.id_revised_fiscal2=t4.id_revised_fiscal2;
quit;

%_eg_conditional_dropds(WORK.facturaciontext);
proc sql;
create table facturaciontext as
select 
Type_of_file length=2 format=$2. informat=$char2. as Type_of_file,
Bank_no length=4 format=$4. informat=$char4. as Bank_no,
Branch_no length=4 format=$4. informat=$char4. as Branch_no,
Product length=3 format=$3. informat=$char3. as Product ,
Num_Contrato,
('00'||put(Sub_Tipo_Producto, z1.)) length=3 format=$3. informat=$char3. as Sub_tipo_Producto ,
Referencia_TRC,
currency length=3 format=$3. informat=$char3. as currency,
(put(year(Fecha_Alta),z4.) || put(month(Fecha_Alta),z2.) || put(day(Fecha_Alta), z2.)) length=8 format=$8. informat=$char8. as Fecha_Alta,
Monthly_spend length=15 format=$15. informat=$char15. as Monthly_spend ,
('+'||case when Average_spend is missing then "000000000000" else substr(put(Average_spend, z15.2),1,12) end||case when Average_spend is missing then "00" else
substr(put(Average_spend, z15.2),14,2)end)  length=15 format=$15. informat=$char15. as Average_spend ,
('+'||case when Last_year_annual_spend is missing then "000000000000" else substr(put(Last_year_annual_spend, z15.2),1,12)end||case when Last_year_annual_spend is missing then "00" else
substr(put(Last_year_annual_spend, z15.2),14,2) end) length=15 format=$15. informat=$char15. as Last_year_annual_spend,
('+'||case when Acumulated_annual_spend is missing then "000000000000" else substr(put(Acumulated_annual_spend, z15.2),1,12) end||case when Acumulated_annual_spend is missing then "00" else
substr(put(Acumulated_annual_spend, z15.2),14,2) end) length=15 format=$15. informat=$char15. as Acumulated_annual_spend  ,
('+'||case when Faturacion_Acumulada_Mes is missing then "000000000000" else substr(put(Faturacion_Acumulada_Mes, z15.2),1,12) end||
case when Faturacion_Acumulada_Mes is missing then "00" else substr(put(Faturacion_Acumulada_Mes, z15.2),14,2) end) length=15 format=$15. informat=$char15. as Faturacion_Acumulada_Mes,
Periodicty length=1 format=$1. informat=$char1. as Periodicity,
Fill_in,
Billing_date length=8 format=$8. informat=$char8. as Billing_date,
End_of_file
from work.facturacion t1;
quit;


data _null_ ;
set work.facturaciontext ;
file '/intl/iccs_emea/dcortell/facturacion2.txt' lrecl=148;

put @1 Type_of_file +(-1) @3 Bank_no +(-1) @7 Branch_no +(-1) @11 Product +(-1) @14 Num_contrato +(-1) @21 Sub_Tipo_Producto +(-1) @24 Referencia_TRC
+(-1) @44 currency +(-1) @47 Fecha_Alta +(-1) @55 Monthly_spend +(-1) @70 Average_spend +(-1) @85 Last_year_annual_spend +(-1) @100 Acumulated_annual_spend +(-1)
+(-1) @115 Faturacion_Acumulada_Mes +(-1) @130 Periodicity +(-1) @131 Fill_in +(-1) @139 Billing_date +(-1) @147 End_of_file +(-1) ; 

run;

Re: export to file text without headers issue

dcortell — Thu, 27 Oct 2016 19:14:21 GMT

Furthermore the weird stuff is that the issue persists aslo utilizing different data sources, still on the first four columns only !!

Example

Table 1

NOTE: Variable 'Tipo_persona 'n is uninitialized.
NOTE: Variable ' Codigo_persona 'n is uninitialized.
NOTE: Variable ' Empresa_Banco 'n is uninitialized.
NOTE: Variable ' Centro_Sucursal'n is uninitialized.
NOTE: The file '/intl/iccs_emea/dcortell/contracto2.txt' is:
Filename=/intl/iccs_emea/dcortell/contracto2.txt,
Owner Name=dcortell,Group Name=bis,
Access Permission=-rw-r--r--,
Last Modified=27Oct2016:11:59:42

Table 2

NOTE: Variable 'Type_of_file 'n is uninitialized.
NOTE: Variable ' Bank_no 'n is uninitialized.
NOTE: Variable ' Branch_no 'n is uninitialized.
NOTE: Variable ' Product'n is uninitialized.
NOTE: The file '/intl/iccs_emea/dcortell/comision.txt' is:
Filename=/intl/iccs_emea/dcortell/comision.txt,
Owner Name=dcortell,Group Name=bis,
Access Permission=-rw-r--r--,
Last Modified=27Oct2016:11:42:47

Re: export to file text without headers issue

dcortell — Mon, 31 Oct 2016 10:32:16 GMT

Hi People

no one with some solution in mind???

Many thanks

Davide

Re: export to file text without headers issue

Tom — Mon, 31 Oct 2016 11:52:30 GMT

Have you tried the solutions that were posted?

Re: export to file text without headers issue

dcortell — Mon, 31 Oct 2016 13:22:45 GMT

Hi Tom

You said "Your errors are caused by non 7bit ASCII characters in your source code. Change the 'A0'x characters back to blanks "; however not sure where the character are, if any, in my code. They are pretty standard text/number values.

Regarding the part of skipping the sql step I can't , as per my code posted above it is not just a mere "text" conversion of some columns, but include too some "case when" logics which apply to the final columns.

furthermore, as hilighted there are some "details" (only first four columns of the table involved in the issue; the issue persisting also utilizing different text tables; the code working fine in sas base (i'm using EG)) that are make me wondering if it is not something related to the use of SAS EG only

Re: export to file text without headers issue

Tom — Mon, 31 Oct 2016 14:12:07 GMT

It is hard to read the SQL code you posted, but the error message does not appear to be related to either the logic of your SQL step or the logic in your attempt to write the fixed column data file. Instead it looks like you have just referenced variables that do not exist in your data.

As @data_null__ pointed out your SAS log shows variable names using name literals that appear to have spaces or other non-printing characters as part of their names. Why do your variable names need name literals? What is the source of these names? Can you rename them to more normal names that use only alphanumerics and underscores?

You need to find the source of the invalid variable names that the error is complaining about.

Usually you do this by trying to reduce your example to miminal set of code that generates the error.

Can you run a PROC CONTENTS on the data and see what variable names the data actual have? You might try just running a simple report like PROC MEANS or PROC FREQ on one or two of the variables to make sure you can actually reference them in any way.

Once you have gotten past that issue then you can look into how to generate fixed format text file. Note that you do NOT need to use SQL to manipulate data. Manipulating data is the purpose of a DATA step! Using conditional logic to manipulate data is not limited to CASE statements in SQL code. You might want to use SQL if you are joining multiple tables in a complex way to create the data that you want to export or if you are using some drag and drop tool in EG to generate your code.