topic Re: Compress files in New SAS User

Compress files

VALLY — Thu, 11 Jun 2020 10:45:50 GMT

Good day Expects,

i have 2 data set and i wanna join/compress , however they do not have a common denominator. when i run the bellow script i am only getting 5 records, and i was expecting 19m records , please assist

PROC SQL;
CREATE TABLE SAP_WI AS
SELECT *
'W&I' AS BU, A.*
FROM DTA.WI_ALL_BA_SAP_ACCOUNTS B
INNER JOIN VBM.SAP_BUT_MATCH A
ON INPUT(PUT(COMPRESS(INPUT(PUT(ACC_NUM,16.),$16.)||PUT(SERIAL_NUM,Z3.)),$16.),16.) = INPUT(PUT(ZZ0011,$16.),16.)
;
QUIT;

Re: Compress files

ed_sas_member — Thu, 11 Jun 2020 12:03:50 GMT

Hi @VALLY

Could you please provide some sample data in DATALINES?

Best,

Re: Compress files

Kurt_Bremser — Thu, 11 Jun 2020 13:04:14 GMT

Your code as posted won't run anyway, because it misses a comma.

If you want a macro variable resolved in

'W&I' AS BU

you must use double quotes.

With 16 digits, you are in the area where numeric precision comes into play.

You concatenate a 16-digit string (result of PUT with $16.) and a 3-digit string (result of PUT with Z3.), and INPUT it with 16. That makes no sense to me. Even with the COMPRESS, once you have more than 13 digits in the first string, this will fail.

I suggest you do all that complicated data cleaning in a separate step first and inspect the results (use OBS= options while testing). In all your datasets, keep identification codes, serial numbers, account numbers and such as left-aligned character. Do NOT store such values as numbers. DON'T.