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;
