Hi   I realised that I made an error. The "Description" on the Treatment is a user-entered free-text field. SAS CI Studio does not construct the message.   I have to think of another way to get that information.   Thanks! ... View more

Thanks Koen   I was trying to avoid using a data step to append the latest month's data to my historical dataset (36 months worth of data) as that process drops the indexes on the BASE dataset, and it takes about 4 hours to re-create the indexes.  Proc append does not drop the indexes but then I have the issue that any new variables, not on the BASE dataset, are not added.   So it looks like I have to revert to my original code : /*********************************************************/ /* Add Current SASMA Snapshot to the History Dataset     */ /*********************************************************/ data HISTORY.SASMA_HISTORY; set HISTORY.SASMA_HISTORY      SASMA.CURRENT_SASMA; run;   /*********************************************************/ /* Add Index's                                                       */ /*********************************************************/   proc datasets library=HISTORY; modify SASMA_HISTORY; index create ID; index create OBS_DATE; index create STATUS; index create REGISTERED_BRAND; index create ID_NUMBER / nomiss; index create PASSPORT_NUMBER / nomiss; run; /*********************************************************/ /* Add Composite Index's                                 */ /*********************************************************/ proc datasets library=HISTORY; modify SASMA_HISTORY; index create numname=(ID OBS_DATE) / nomiss; run; ... View more

Thanks Koen   I was trying to avoid using a data step to append the latest month's data to my historical dataset (36 months worth of data) as that process drops the indexes on the BASE dataset, and it takes about 4 hours to re-create the indexes.  Proc append does not drop the indexes but then I have the issue that any new variables, not on the BASE dataset, are not added.   So it looks like I have to revert to my original code : /*********************************************************/ /* Add Current SASMA Snapshot to the History Dataset     */ /*********************************************************/ data HISTORY.SASMA_HISTORY; set HISTORY.SASMA_HISTORY      SASMA.CURRENT_SASMA; run;   /*********************************************************/ /* Add Index's                                                       */ /*********************************************************/   proc datasets library=HISTORY; modify SASMA_HISTORY; index create ID; index create OBS_DATE; index create STATUS; index create REGISTERED_BRAND; index create ID_NUMBER / nomiss; index create PASSPORT_NUMBER / nomiss; run; /*********************************************************/ /* Add Composite Index's                                 */ /*********************************************************/ proc datasets library=HISTORY; modify SASMA_HISTORY; index create numname=(ID OBS_DATE) / nomiss; run; ... View more

Good day   I tried your method (keep statement) but the variables ( in the "DATA"  dataset but not on the "BASE" dataset) are still dropped in the PROC Append. I have 2 SAS datasets and want to append the records in the dataset "CURR" to the dataset "HIST" Too many variables to list in the proc append code and the list of variables might change from month to month, so I extracted all the variable names from the dictionary.columns list. proc sql; select name into :SASMA_LIST separated by ' ' from dictionary.columns where libname="WORK" and memtype="DATA" and memname = "CURR"; quit; PROC APPEND BASE=HIST DATA=CURR (keep=&SASMA_LIST) FORCE; RUN; Log 35 36 PROC APPEND BASE=HIST 37 DATA=CURR (keep=&SASMA_LIST) FORCE; 38 RUN; NOTE: Appending WORK.CURR to WORK.HIST. WARNING: Variable p_rcosmetics1 was not found on BASE file. The variable will not be added to the BASE file. WARNING: Variable p_raswiss1 was not found on BASE file. The variable will not be added to the BASE file. WARNING: Variable p_rathome1 was not found on BASE file. The variable will not be added to the BASE file.     ... View more

Good day   I tried listing all the variables in the "DATA" dataset in the PROC Append but it still does not add the columns to the "BASE" dataset in the PROC Append code.   I tried a datastep but then it drops the indexes on the "BASE" table, and then I need to re-add the indexes and that step takes over 2-4 hours as the dataset has 100's of millions of rows.   data HIST; set HIST        CURR; run;   How else can I achieve this objective?   I have 2 datasets: 1. HIST (852 variables) 2. CURR (723 variables)   Put all the variable names from "CURR" into a list (723 variables)   proc sql; select name into :SASMA_LIST separated by ' ' from dictionary.columns where libname="WORK" and memtype="DATA" and memname = "CURR"; quit;   PROC APPEND BASE=HIST DATA=CURR (keep=&SASMA_LIST) FORCE; RUN;   Results: NOTE: Writing HTML5(EGHTML) Body file: EGHTML 27 28 PROC APPEND BASE=HIST 29 DATA=CURR (keep=&SASMA_LIST) FORCE; 30 RUN; NOTE: Appending WORK.CURR to WORK.HIST. WARNING: Variable SME_DATE_KEY was not found on BASE file. The variable will not be added to the BASE file. WARNING: Variable TOT_PAYMENT_VALUE_AMT was not found on BASE file. The variable will not be added to the BASE file. WARNING: Variable ACCOUNT_BALANCE_AMT was not found on BASE file. The variable will not be added to the BASE file. WARNING: Variable TOT_INSTALMENT_AMT was not found on BASE file. The variable will not be added to the BASE file. WARNING: Variable TOT_DUE_AMT was not found on BASE file. The variable will not be added to the BASE file. WARNING: Variable Prev_TOT_DUE_AMT was not found on BASE file. The variable will not be added to the BASE file. WARNING: Variable TOT_PAYMENT_VALUE_AMT0 was not found on BASE file. The variable will not be added to the BASE file. .... NOTE: FORCE is specified, so dropping/truncating will occur. NOTE: There were 10 observations read from the data set WORK.CURR. NOTE: 10 observations added. NOTE: The data set WORK.HIST has 20 observations and 852 variables. NOTE: PROCEDURE APPEND used (Total process time): real time 0.01 seconds cpu time 0.02 seconds   ... View more

I added the index option and the options - options fullstimer msglevel=I;    Log: No difference in the runtime   28 Data credit_sasma; 29 set SASMAHIS.SASMA_ACCOUNT_HISTORY (idxname=obs_date where=(Obs_Date = '12JAN2023'd)); INFO: Index OBS_DATE selected for WHERE clause optimization. 30 run; NOTE: There were 2789161 observations read from the data set SASMAHIS.SASMA_ACCOUNT_HISTORY. WHERE Obs_Date='12JAN2023'D; NOTE: The data set WORK.CREDIT_SASMA has 2789161 observations and 850 variables. NOTE: Compressing data set WORK.CREDIT_SASMA decreased size by 67.65 percent. Compressed is 60148 pages; un-compressed would require 185945 pages. NOTE: DATA statement used (Total process time): real time 1:20.43 user cpu time 1:12.15 system cpu time 7.75 seconds memory 5980.75k OS Memory 31400.00k Timestamp 03/02/2023 12:16:33 PM Step Count 17 Switch Count 133 Page Faults 2 Page Reclaims 1127 Page Swaps 0 Voluntary Context Switches 412 Involuntary Context Switches 535 Block Input Operations 12532969 Block Output Operations 15409176 ... View more

Hi    We followed the instructions to add the MAS_M2PATH and MAS_PYPATH environment variables but the code still fails.   See log below and hotfixes installed for Base SAS.     Are we missing a hotfix or is there something else that needs to be done.   We are running SAS Enterprise guide connecting to Linux.   NOTE: Writing HTML5(EGHTML) Body file: EGHTML 27         28         proc fcmp outlib=work.fcmp.pyfuncs; 29         function MyPyFunc(FCMParg); 30         declare object py(python); 31         submit into py; 32         def TimesFive(PythonArg): 33             "Output: MyKey" 34             newvar = PythonArg * 5 35             return newvar 36         endsubmit; 37         rc = py.publish(); 38         rc = py.call("TimesFive",FCMParg); 39         MyFCMPResult = py.results["MyKey"]; 40         return(MyFCMPResult); 41         endsub; 42         run;   NOTE: Function MyPyFunc saved to work.fcmp.pyfuncs. NOTE: PROCEDURE FCMP used (Total process time):       real time           0.03 seconds      cpu time            0.03 seconds         43         44         options cmplib=work.fcmp; 45         data _null_; 46            x = MyPyFunc(5); 47            put x=; 48         run;   2                                                          The SAS System                           10:30 Thursday, January 26, 2023   ERROR: Unable to publish program for foreign language execution. ERROR: Error reported in function 'python:PUBLISH' in statement number 5 at line 12 column 2.        The statement was:     0     (12:2)     rc = python:PUBLISH(  ) x=. NOTE: DATA statement used (Total process time):       real time           0.02 seconds       cpu time            0.03 seconds         Host: lax Product: base Version: 9.4 Display Name: Base SAS Hot Fix Entry: Hotfix D9T001 Hot Fix Entry: Hotfix D9T004 Hot Fix Entry: Hotfix D9T006 Hot Fix Entry: Hotfix D9T014 Hot Fix Entry: Hotfix D9T015 Hot Fix Entry: Hotfix D9T018 Hot Fix Entry: Hotfix D9T021 Hot Fix Entry: Hotfix D9T023 Hot Fix Entry: Hotfix D9T024 Hot Fix Entry: Hotfix D9T029 Hot Fix Entry: Hotfix D9T031 Hot Fix Entry: Hotfix D9T033 Hot Fix Entry: Hotfix D9T034 Hot Fix Entry: Hotfix D9T036 Hot Fix Entry: Hotfix D9T038 Hot Fix Entry: Hotfix D9T041 Hot Fix Entry: Hotfix D9T042 Hot Fix Entry: Hotfix D9T043 Hot Fix Entry: Hotfix D9T044 Hot Fix Entry: Hotfix D9T046 Hot Fix Entry: Hotfix D9T048 Hot Fix Entry: Hotfix D9T051 Hot Fix Entry: Hotfix D9T039 Hot Fix Entry: Hotfix D9T052 Hot Fix Entry: Hotfix D9T053 Hot Fix Entry: Hotfix D9T057 Hot Fix Entry: Hotfix D9T058 Hot Fix Entry: Hotfix D9T059 Hot Fix Entry: Hotfix D9T061 Hot Fix Entry: Hotfix D9T062 Hot Fix Entry: Hotfix D9T063 Hot Fix Entry: Hotfix D9T067 Hot Fix Entry: Hotfix D9T068 Hot Fix Entry: Hotfix D9T071 Hot Fix Entry: Hotfix D9T073 Hot Fix Entry: Hotfix D9T075 Hot Fix Entry: Hotfix D9T076 Hot Fix Entry: Hotfix D9T078 Hot Fix Entry: Hotfix D9T079 Hot Fix Entry: Hotfix D9T080 Hot Fix Entry: Hotfix D9T083 Hot Fix Entry: Hotfix D9T082 Hot Fix Entry: Hotfix D9T084 Hot Fix Entry: Hotfix D9T085 Hot Fix Entry: Hotfix D9T087 Hot Fix Entry: Hotfix D9T089 Hot Fix Entry: Hotfix D9T090 Hot Fix Entry: Hotfix D9T093 Hot Fix Entry: Hotfix D9T095 Hot Fix Entry: Hotfix D9T098 Hot Fix Entry: Hotfix D9T099 Hot Fix Entry: Hotfix D9T100 Hot Fix Entry: Hotfix D9T101 Hot Fix Entry: Hotfix D9T104 Hot Fix Entry: Hotfix D9T110 Hot Fix Entry: Hotfix D9T117 Hot Fix Entry: Hotfix D9T123 Hot Fix Entry: Hotfix D9T127 Hot Fix Entry: Hotfix D9T129 Hot Fix Entry: Hotfix D9T130 Hot Fix Entry: Hotfix D9T131 Hot Fix Entry: Hotfix D9T132 Hot Fix Entry: Hotfix D9T134 Hot Fix Entry: Hotfix D9T135 Hot Fix Entry: Hotfix D9T137 Hot Fix Entry: Hotfix D9T139 Hot Fix Entry: Hotfix D9T140 Hot Fix Entry: Hotfix D9T141 Hot Fix Entry: Hotfix D9T142 Hot Fix Entry: Hotfix D9T143 Hot Fix Entry: Hotfix D9T144 Display Version: 9.4_M6     ... View more

Hi   This is how it will work.   We use SAS Custiomer Intelligence Studio to generate files that are loaded into a call centre application. We cannot determine beforehand how many records will be in every file, but in every file, there must be a unique, random number assigned to every row in the file.   The ideal solution is to add the function in a Calculated variable in SAS Customer Intelligence Studio, which is added into the file at runtime.    I cannot insert the logic that you sent to check if the number has been used. I can only insert the function.   I thought "random" meant that the random number would not be repeated in a file/table.    ... View more

Hi  Thanks for the tip - it worked, but we discovered that the RAND function assigns the same random number to more than one record in a dataset. I have a file of 15000 customer records - see screenshot below - there are many duplicate random numbers.   My output file cannot have duplicate random numbers as the generated string is used in a key in the target system that the file is loaded into.   How do I prevent duplicate random numbers?     ... View more