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; ... View more

Hi All dears   I'm in the need to check if within a dataset a specif column exists, and depending on the existance, selecting the variable or execute a case if step.   The check must be used within a proc sql     I had the idea of doing the checks in  Macros ( first macro to check the existance, second macro including the two action depending on the presence or not of the variable), and then utilize the same macro in the final  proc sql to define a variable related to the specifc variable/month   the idea would be   1. Macro - checking or not the existance of the column LIBNAME EC1 "/intl/iimis4/prod/cgf/emea/spain"; %let bom=200; %macro varexist /*---------------------------------------------------------------------- Check for the existence of a specified variable. ----------------------------------------------------------------------*/ (ds = ec1.cust_seg ,var = cust_seg_mth&bom ); /*---------------------------------------------------------------------- Usage Notes: %if %varexist(&data,NAME) %then %put input data set contains variable NAME; The macro calls resolves to 0 when either the data set does not exist or the variable is not in the specified data set. ----------------------------------------------------------------------*/ %local dsid rc ; %*---------------------------------------------------------------------- Use SYSFUNC to execute OPEN, VARNUM, and CLOSE functions. -----------------------------------------------------------------------; %let dsid = %sysfunc(open(&ds)); %if (&dsid) %then %do; %if %sysfunc(varnum(&dsid,&var)) %then 1; %else 0 ; %let rc = %sysfunc(close(&dsid)); %end; %else 0; %mend varexist; 2. Macro - dependign on the existance or not select one of the two option (1 select the variable / 2 execute the case if) %macro test;     %if %varexist = 1 %then %do;        proc sql ;            select cust_seg_mth&eom from ec1.cust_seg; quit;          %end; %else %do;      proc sql; select (case when (intck('month',t2.cust_estab_dt, '31aug2016'd )>=12 and cust_rel_ind&eom=1) then "Organic" when (intck('month',t2.cust_estab_dt, '31aug2016'd)>=12 and cust_rel_ind&eom=0) then "Attrition" when intck('month',t2.cust_estab_dt, '31aug2016'd)<=11 then "Acquired" end) from ec1.cust_seg; quit;    %end;  %mend test;  3. Recall the macro in the fianl proc sql; proc sql; create table histcust as select t1.acct11, t2.cust_estab_dt, cust_seg_mth181 as jan2015, cust_seg_mth182 as feb2015, cust_seg_mth183 as mar2015, cust_seg_mth184 as apr2015, cust_seg_mth185 as may2015, cust_seg_mth186 as jun2015, cust_seg_mth187 as jul2015, cust_seg_mth188 as aug2015, cust_seg_mth189 as sep2015, cust_seg_mth190 as oct2015, cust_seg_mth191 as nov2015, cust_seg_mth192 as dec2015, cust_seg_mth193 as jan2016, cust_seg_mth194 as feb2016, cust_seg_mth195 as mar2016, cust_seg_mth196 as apr2016, cust_seg_mth197 as may2016, cust_seg_mth198 as jun2016, cust_seg_mth199 as jul2016, %test as aug2016 from ec1.acct_attr t1 left join ec1.cust_seg t2 on t1.acct11=t2.acct11 left join ec1.cust_attr t3 on t1.amex_cust_id=t3.amex_cust_id order by t1.acct11, t1.amex_cust_id ; quit;   Now, I'm not a macro user so I have tried something but getting as natural error   I suppose the first macro give a 1 or 0 depending on the variable presence but not sure on the second one   The second macro should be supposed to give a specific value (the corresponding variable record or the result of the case if), related to each ID (acct11) of each record derived in the final proc sql   Anyone could help ??   Thanks in advance for the precious support   Bests ... View more

Hi all people   I'm encountering a weird issue in exporting a sas dataset into a txt file   Details   In a first step, I assign to a series of text variables a specific length. In other word I want that each of these variable will have a specifi dimension and that in case a value is inferior to that dimension then blank spaces will fill the remaining space.   then I concatenate all these varialbes in a unique variable wth the aim to have a fixed total length of 129 character   then I export this one column dataset to txt. And here it comes the problem. While "in between" blank spaces are keep, the trailing ones, referred to the last variable concatenated (Nombre), get removed during the export, while I would keep them, in order to have same overall length   Below the code of the first two step. the export is made through SAs EG user interface (nothing complicated). any idea why sas cut the trailing final spaces and how i can preserve them in the export??   Many thanks D   %_eg_conditional_dropds(WORK.accounts); proc sql; create table finalv1 as select Tipo_persona length=1 format=$1. informat=$char1. , Codigo_persona length=9 format=$9. informat=$char9. , Empresa_Banco length=4 format=$4. informat=$char4., Centro_Sucursal length=4 format=$4. informat=$char4., Producto length=3 format=$3. informat=$char3., ("00" || put(Sub_Tipo_Producto, z1.)) length=3 format=$3. informat=$char3. as Sub_Tipo_Producto, put(Contract, z7.) length=7 format=$7. informat=$char7. as Num_Contrato, Fecha_Alta2 length=8 format=$8. informat=$char8. as Fecha_Alta, Indicador_Alta_Baja length=1 format=$1. informat=$char1., Fecha_Baja2 length=8 format=$8. informat=$char8. as Fecha_Baja, Id_fiscal length=9 format=$9. informat=$char9. as Id_Fiscal, t2.Apellido_1 length=26 format=$26. informat=$char26. as Apellido_1 , t2.Apellido_2 length=26 format=$26. informat=$char26. as Apellido_2 , t2.Nombre length=20 format=$20. informat=$char20. as Nombre , " " length=2 format=$2. informat=$2. as Final_de_registro from work.new_contract t1 left join work.idselected t2 on (t1.acct11=t2.acct11) order by Num_Contrato ; quit; proc sql; create table unified as select cat(Tipo_persona, Codigo_persona, Empresa_Banco, Centro_Sucursal, Producto, Sub_Tipo_Producto, Num_Contrato, Fecha_Alta, Indicador_Alta_Baja, Fecha_Baja, Id_Fiscal, Apellido_1, Apellido_2, Nombre) length=129 format=$129. informat=$char129. as finals from work.finalv1; quit; ... View more