Utilizing these test datasets:
DATA TESTDATA_1;
FORMAT ID 8. DATE DATE9. Code $7.;
INFORMAT ID 8. DATE DATE9. Code $7.;
INPUT ID DATE Code;
CARDS;
100 13MAY2019 HCC-5
100 13MAY2019 HCC-9
100 13MAY2019 HCC-36
101 13MAY2019 HCC-4
101 13MAY2019 HCC-101
;
DATA TESTDATA_2;
FORMAT ID 8. DATE DATE9. Code $5.;
INFORMAT ID 8. DATE DATE9. Code $5.;
INPUT ID DATE Code;
CARDS;
100 14JUN2019 HCC-5
100 14JUN2019 HCC-9
101 14JUN2019 HCC-4
;
I created two tables that contained the same fields but the "Code" variable is different lengths/formats.
Then I combined the two tables so that I could see the values get truncated.
DATA WORK.Combine_PreFix;
SET WORK.TESTDATA_2
WORK.TESTDATA_1;
RUN;
Name Type Length Format Informat
ID Numeric 8 8. 8.
DATE Date 8 DATE9. DATE9.
Code Character 5 $5. $5.
A warning message was generated:
WARNING: Multiple lengths were specified for the variable Code by input data set(s). This can cause truncation of data.
And this table was the result (where we can clearly see the values were truncated):
ID DATE Code
100 14JUN2019 HCC-5
100 14JUN2019 HCC-9
101 14JUN2019 HCC-4
100 13MAY2019 HCC-5
100 13MAY2019 HCC-9
100 13MAY2019 HCC-3
101 13MAY2019 HCC-4
101 13MAY2019 HCC-1
So I put together this logic, to identify the max(length) for every value and their formats, and then put them into a macro containing a running list for all of the variables on the tables.
PROC SQL NOPRINT;
SELECT DISTINCT
CAT(STRIP(a.Name), ' ', STRIP(CASE b.TYPE WHEN 'num' THEN ' ' WHEN 'char' THEN '$' ELSE ' ' END), a.Mlen)
, CAT(STRIP(a.Name), ' ', b.Format)
, a.VarNum
INTO :LngthDtls SEPARATED BY ' '
, :FrmtDtls SEPARATED BY ' '
, :Varnum
FROM (SELECT Libname, VarNum, Name, MAX(Length) AS Mlen
FROM SASHELP.VCOLUMN
WHERE LIBNAME='WORK'
AND MemName IN("TESTDATA_1", "TESTDATA_2")
GROUP BY Libname, Name, VarNum) AS a
INNER JOIN SASHELP.VCOLUMN AS b ON a.LIBNAME=b.LIBNAME
AND a.Name=b.Name
AND a.Mlen=b.Length
WHERE b.LIBNAME='WORK'
AND b.MemName IN("TESTDATA_1", "TESTDATA_2")
ORDER BY a.VarNum;
QUIT;
*I only include the VarNum details, in an attempt to retain the variable order as they currently appear in the tables reviewed).
To test the newly created macro variables:
%PUT &=FrmtDtls.;
%PUT &=LngthDtls.;
FRMTDTLS=ID 8. DATE DATE9. Code $7.
LNGTHDTLS=ID 8 DATE 8 Code $7
*(as per the log).
Then we use this simple datastep to set the details (using the macros we just generated for all variables involved):
DATA WORK.Combine_PostFix;
LENGTH &LNGTHDtls.;
FORMAT &FrmtDtls.;
SET WORK.TESTDATA_2
WORK.TESTDATA_1;
RUN;
Which produces no warning message, and this resulting output table:
ID DATE Code
100 14JUN2019 HCC-5
100 14JUN2019 HCC-9
101 14JUN2019 HCC-4
100 13MAY2019 HCC-5
100 13MAY2019 HCC-9
100 13MAY2019 HCC-36
101 13MAY2019 HCC-4
101 13MAY2019 HCC-101
Name Type Length Format Informat
ID Numeric 8 8. 8.
DATE Date 8 DATE9. DATE9.
Code Character 7 $7. $7.
And now we can clearly see that none of the values are being truncated.
For the purposes of your process, you will need to populated the libname and memname values in the where criteria locations with your specific libname and table names. Personally I would wrap this logic into a Macro statement with some positional values. And if the tables being used sometime fluctuates, I would use additional logic to generate a flexible macro list of all the table names that should be included (depending on daily/weekly/monthly criteria, etc). I didn't want to over complicate the answer that I provide with all of that information.
Hope this helps.
