I have a SAS macro that always indicates the end of the past month. It currently has this value: 31AUG2022:23:59:59   I want a SAS macro that indicates this in a YYYYMM format. It should have this value: 202208   So far I've got this code:   %let PrevPeriod_yyyy = %sysfunc(year(%sysfunc(datepart(&PrevPeriod_dttm)))); %let PrevPeriod_m = %sysfunc(month(%sysfunc(datepart(&PrevPeriod_dttm)))); %let PrevPeriod_mm = %sysfunc(put(%sysfunc(input(&PrevPeriod_m, best2.)), z2.)); Line 1 returns 2022. Line 2 returns 7 Line 3 returns null The idea with line 3 was to force the single-digit month to have 2 digits. So that'd it return 07 instead of 7 (while maintaining monthly values like 10/11/12).   If I can make line 3 work, then I can easily create 202207. Any idea what I'm doing wrong?   ... View more

We've got a reusable SAS-script that runs 1 time per source table. It distributes each table to a corresponding target table. Some source tables have 1 primary key, others have 2-3. But we want to set 1 key per table (primarily because the reusable script can be simplified when the keys don't differ from table to table).   I've used the primary keys from the source tables to create 1 unique hex value per row. Example value: "C0EA54AAB21573E07D46E9BB9E9B1C73".   Ideally we wanted to change the hex into a numeric field (faster joins). But as Tom said: "The MD5() function returns 128 bits. So at 8 bits per byte that is 16 bytes. That is waaaaaay too many to convert into a single number. So leave it as character and use the $HEX32 format to display it."   Proper naming standard is important to us, and I'm stumped on what to call the field. Considered calling it a Retained Key (w/RK-suffix) because it's set based on primary keys from the source tables. But RKs are only supposed to be integers I think. And it seems wrong to call it a Primary Key (w/PK-suffix), because each target table has a composite key that will consist of this hex-variable and a date variable -- so the hex isn't unique by itself.   I'm thankful for any naming advice. 🙂 ... View more

We've got a series of large tables in PostgreSQL (over 500 million rows in some). Each table has an attribute called REF_ID, and one called OBJECT_CD. Both are varchar(32). OBJECT_CD is always NULL, but now we need to give it a value (on all rows). Let's call one of these large tables T1.   We've also got the table T2. It's a smaller table that contains 1 row per REF_ID. The attribute OBJECT_CD is always filled out here.   I want to update T1.OBJECT_CD with values from T2.OBJECT_CD (where T1.REF_ID = T2.REF_ID).   Sounds simple, but I have a hard time finding an effective way to do it. The PostgreSQL tables are so large, and the code I've tried runs "forever" and times out.   An example of something I've tried: proc sql; update pgsql.T1 set OBJECT_CD = (select distinct OBJECT_CD from T2 where T1.REF_ID = T2.REF_ID); quit; Any advice on the most effective way to update huge PostgreSQL tables? ... View more

This code creates a concatenated string based on primary key values in the source table. This in turn is used to create a hex value.   %let source_keys = ORGNR PRODUCT_ID OBJECT_ID; data want; set have; source_keys_concat = catx('_',of &source_keys.); attrib target_hex length=$16 format=$hex32.; target_hex = md5(source_keys_concat); run; The MD5 function mostly seems to work as intended. But it also generates some bizarre outputs (including NULL values): The input values (source_keys_concat) are consistent, so why doesn't the MD5 function generate consistent output values? ... View more

We've got several large tables where the primary key consists of multiple char variables. These are used in multiple heavy joins. My example table uses these 5 chars as PK: CUSTOMER_ID, CUSTOMER_SYSTEM_ID, PRODUCT_ID, COVERAGE_ID, OBJECT_ID.   I'd like to create 1 numeric composite key instead. Primarily to make the joins run faster. But also because our reusable script (used to process multiple tables) can be simplified by always having 1 PK per table.   My plan is to append the char variables together, and then use MD5 & HEX to create the numeric composite key value. Like this:   data want; set have; attrib pk_char format=$200.; attrib pk_nr format=20.0; pk_char = CUSTOMER_ID || '_' || CUSTOMER_SYSTEM_ID || '_' || PRODUCT_ID || '_' || COVERAGE_ID || '_' || OBJECT_ID; pk_nr = input(put(md5(pk_char),$hex13.),hex13.); run; 1) Is this a sensible approach, or do you have better suggestions? 2) I'm satisfied with format=$200 for the concatinated character string. This value should never be exceeded for any of the tables. An example value it might get today (from the example table) is "123684525218_562448_V48_26457_3". But I'm clueless about what hex format to go for. Would hex13 be fine for creating a uniqe numerical value for a string consisting of up to 200 symbols? ... View more

This is in regards to insurance offer reporting. Due to flaws in the source system processes, duplicate offers can be generated. But they're not true duplicates, because they do get unique offer IDs, and can be generated up to 3 days apart. They're only duplicate in the sense that they should be considered the same offer from a business perspective.   Our current reporting counts these as individual offers. But now we need to ensure that the "duplicates" are no longer included.   HAVE OFFER_ID OFFER_DATE CUSTOMER_ID 101 2022-05-19 12345 117 2022-05-19 12345 127 2022-05-21 12345 133 2022-06-13 12345 139 2022-05-01 84637 144 2022-06-03 87609 146 2022-06-03 87609 151 2022-05-14 24556 159 2022-05-18 24556 161 2022-05-18 24556 165 2022-05-19 24556 167 2022-05-22 24556 177 2022-06-27 12345   WANT OFFER_ID OFFER_DATE CUSTOMER_ID 101 2022-05-19 12345 133 2022-06-13 12345 139 2022-05-01 84637 144 2022-06-03 87609 151 2022-05-14 24556 159 2022-05-18 24556 177 2022-06-27 12345   I basically want to select the first OFFER_ID & OFFER_DATE per CUSTOMER_ID. But only when the OFFER_DATE of the offers are within max 3 days of each other. If an offer was generated after 4+ days, it should be considered a unique new offer. This rule has been agreed upon among the business users.   Consider the WANT-rows for CUSTOMER_ID=24556. The offers generated on 2022-05-18 and 2022-05-22 are 4 days apart, yet they should still be treated as the same offer because they're "linked" by the offer generated on 2022-05-19. So basically: "2022-05-18 is within 3 days of 2022-05-19, which is within 3 days of 2022-05-22, therefore these 3 offers should be counted as 1".   Any advice on how to implement this? I imagine a self-join on CUSTOMER_ID is involved, but I'm unsure on how to group based on the 3-day-rule. ... View more

I feel like I'm going insane trying to figure something out. Hoping that someone can explain the logic for me. 😩   We've got a data step with this code: if &create_testvar_flg. = 1 then do; attrib testvar_nr format=11.0; testvar_nr = input(testvar.,best.); end; When &create_testvar_flg is set to 1, this runs as intended. The variable is created and filled with values.   However if &create_testvar_flg isn't 1, then the variable is still created. It just isn't loaded. And the log does say "create_testvar_flg resolves to 0".   I've tried removing the that whole part of the code, and can confirm that the new variable isn't created at all then. So there's definitely something iffy about the IF-statement above (pun intended). Is there any reason why it would run the attrib-line even though &create_testvar_flg=0? How can that be prevented?   This is part of reusable code used to load multiple tables. We want the new attribute to be created conditionally, only for some of them. ... View more