About Tom

Tom

Since you are running a macro. And a macro that already generates steps (PROC REPORT) you can add the commands to shut down listing to your macro. You could just close the listing and open it again. .... ods listing close; proc report ..... ods listing ; ... But that would open the listing if it wasn't open before. Instead why not use ODS SELECT instead and only generate the commands when the listing destination is active. data _null_; set sashelp.vdest; where destination='LISTING'; call execute('ods listing select none;'); run; proc report .... ... data _null_; set sashelp.vdest; where destination='LISTING'; call execute('ods listing select all;'); run;

Tom

If you want PROC REPORT not to complain when the column width is too long for the linesize then specify shorter column widths in the DEFINE statements. Example: data test; set sashelp.class(obs=3); length all $200; all=catx('|',of _all_); run; proc report data=test; run; proc report data=test; define all / width=20 flow ; run; 7 proc report data=test; NOTE: Writing HTML Body file: sashtml.htm 8 run; ERROR: The width of all is not between 1 and 78. Adjust the column width or line size. NOTE: This affects LISTING output. NOTE: The SAS System stopped processing this step because of errors. NOTE: There were 3 observations read from the data set WORK.TEST. NOTE: PROCEDURE REPORT used (Total process time): real time 0.64 seconds cpu time 0.14 seconds 9 proc report data=test; 10 define all / width=20 flow ; 11 run; NOTE: There were 3 observations read from the data set WORK.TEST. NOTE: PROCEDURE REPORT used (Total process time): real time 0.01 seconds cpu time 0.00 seconds

Tom

What PROC CONTENTS presents is the encoding setting for the dataset, not the encoding setting for the current SAS session. To see the session encoding setting get the system option ENCODING. %put ENCODING=%SYSFUNC(GETOPTION(ENCODING)); But if you made that dataset using the same type of SAS session you are currently running then most likely your SAS session is also using UTF-8 as the encoding. In which case the the %3B directive cannot be used, per the documentation I posted before.

Tom

What is your session ENCODING setting? The %3B will not work with Unicode session. https://documentation.sas.com/doc/en/proc/1.0/p0n990vq8gxca6n1vnsracr6jp2c.htm %b abbreviated month name (for example, JAN or Jan). The character casing is determined by the SAS session locale. Tip For the English language, to always create an abbreviated month with only an uppercase initial letter (for example, Jan), use the directive %3B. %<n>B the full month name (for example, January) if n is not included in the directive. n specifies the number of characters that appear for the month name. In comparison, the %b directive writes a three-character month abbreviation in uppercase letters for some locales. Restrictions The directives argument is not supported if CASFMTLIB is specified. n is not supported in DBCS and Unicode SAS sessions. Example %3B would write Oct for the month of October

Tom

No. Both are not required. And for that particular example neither is needed since the variable K used in the HASH object must already exist in dataset B for the code to make any sense. 81 data a b ; 82 do k=1 to 2; output a; end; 83 do k=1 to 4; output b; end; 84 stop; 85 run; NOTE: The data set WORK.A has 2 observations and 1 variables. NOTE: The data set WORK.B has 4 observations and 1 variables. NOTE: DATA statement used (Total process time): real time 0.01 seconds cpu time 0.01 seconds 86 87 data _null_; 88 if _N_ = 1 then do; 89 declare hash h(dataset:'A'); 90 h.defineKey('k'); 91 h.defineDone(); 92 end; 93 94 set B; 95 if h.find()=0 then put "Key exists in A: " k=; 96 else put "Key does NOT exist in A: " k=; 97 run; NOTE: There were 2 observations read from the data set WORK.A. Key exists in A: k=1 Key exists in A: k=2 Key does NOT exist in A: k=3 Key does NOT exist in A: k=4 NOTE: There were 4 observations read from the data set WORK.B. NOTE: DATA statement used (Total process time): real time 0.01 seconds cpu time 0.01 seconds The purpose of the unexecuted SET statement is to let the data step compiler see the variables you are using from that dataset. That is needed because any variables you are referencing in the DEFINEKEY() and DEFINEDATA() method calls are "invisible" to the data step compiler. They just look like sting literals. So if those variables are never defined some where in the data step then you get a RUN TIME error when the hash object is created. 4 data test; 5 set sashelp.class(obs=1); 6 declare hash h(); 7 h.definekey('fred'); 8 h.definedone(); 9 run; ERROR: Undeclared key symbol fred for hash object at line 8 column 3. ERROR: DATA STEP Component Object failure. Aborted during the EXECUTION phase. NOTE: The SAS System stopped processing this step because of errors. NOTE: There were 1 observations read from the data set SASHELP.CLASS. WARNING: The data set WORK.TEST may be incomplete. When this step was stopped there were 0 observations and 5 variables. WARNING: Data set WORK.TEST was not replaced because this step was stopped. NOTE: DATA statement used (Total process time): real time 0.01 seconds cpu time 0.00 seconds You don't need to use the SET statement to define the variables. You could actual define the variables using LENGTH or ATTRIB statements. One use of the CALL MISSING() statement is to avoid a message about a variable not be initiated. There are other ways to do that. You could initiate the variable with an assignment statement instead. Or you could include the variable in an array definition. The use of CALL MISSING() just make it easier. But another use of CALL MISSING() statement is important when using this type of HASH() lookup. And that is using CALL MISSING() for its original purpose, to set variable to missing values. You should use that when the FIND() method did not find an entry. That is especially important when using the unexecuted SET statement to define the variable because variables that appear in source datasets are RETAINED (that is not set to missing at start of each iteration of the dataset loop). So to prevent lookup values from being carried forward it is important to clear those values when the lookup fails. Try this example: data newclass ; length name $8 ; do name='Alfred','Sam'; output; end; run; data want; if 0 then set sashelp.class(keep=name age); if _N_ = 1 then do; declare hash h(dataset:'sashelp.class'); h.defineKey('name'); h.definedata('age'); h.defineDone(); end; set newclass; put name= @; if h.find()=0 then put age=; else do; call missing(age); put age= " Could not find age"; end; run; proc print; run; Now try running it without the CALL MISSING() statement. What value of AGE did you get for NAME="Sam"? Now add remove the IF 0 ... statement and add a LENGTH AGE 8; statement after the SET NEWCLASS statement. What value do you get now?

Tom

It should be flagging the extra ) in the statement and not the date literal. 46 data _null_; 47 if (Incident_Date=.) or (Incident_Date<'01JAN2002'd or Incident_Date>'31DEC2017'd)) - 22 200 ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, -, /, ;, <, <=, <>, =, >, ><, >=, AND, EQ, GE, GT, IN, LE, LT, MAX, MIN, NE, NG, NL, NOT, NOTIN, OR, THEN, ^, ^=, |, ||, ~, ~=. ERROR 200-322: The symbol is not recognized and will be ignored. 48 then Date_Issues='Needs Review'; 49 run; But I was able to get SAS into a state where it did flag the date literal as an error. 825 data _null_; 826 if (Incident_Date=.) or (Incident_Date<'01JAN2002'd or Incident_Date>'31DEC2017'd)) - 390 200 76 ERROR 390-185: Expecting an relational or arithmetic operator. ERROR 200-322: The symbol is not recognized and will be ignored. ERROR 76-322: Syntax error, statement will be ignored. 827 then Date_Issues='Needs Review'; 828 run; Not sure if it was because of some hidden character I copied from your example code or some other previous syntax errors I had made in the same SAS session. But starting a new SAS session got it back to normal behavior.

Tom

How are the variables defined? Make sure those are the actual variable names. Make sure they are actually numeric variables that contain date values. Not character variables. Not datetime values. Try looking at the current values. proc freq data=tsa_nodupkey ; tables Date_Received*Incident_Date / list; format Date_Received Incident_Date year4.; run; Make sure you actually have some cases that fall into the years 2002 to 2017. Note that if the variables contain DATETIME values then the YEAR() function will return a missing value, which in comparison operations will be considered smaller than any actual number. 1 data _null_; 2 x=datetime(); 3 if year(x) < 2017 then put 'Less than 2017 ' x= datetime19. x=dtdate9. x=dtyear4.; 4 run; NOTE: Invalid argument to function YEAR(2071614560.5) at line 3 column 6. Less than 2017 x=24AUG2025:00:29:21 x=24AUG2025 x=2025 x=2071614560.5 _ERROR_=1 _N_=1 NOTE: Mathematical operations could not be performed at the following places. The results of the operations have been set to missing values. Each place is given by: (Number of times) at (Line):(Column). 1 at 3:6 NOTE: DATA statement used (Total process time): real time 0.01 seconds cpu time 0.01 seconds Try recoding the IF condition to be simpler. You seem to want this condition. if not (Incident_Date <= Date_Received and year(Incident_Date) in (2002:2017) and year(Date_Received) in (2002:2017) ) then Date_Issues='Needs Review';

Tom

First let's convert your example listing into an actual dataset so we have something to code against. data have; input section $ (start end) (:datetime.); format start end datetime19.; cards; Aaa 01Jun2025:00:01:10 01Jun2025:00:09:02 Aaa 01Jun2025:00:03:10 01Jun2025:00:12:00 Aaa 01Jun2025:00:10:09 01Jun2025:00:18:08 Bbb 01Jun2025:00:01:02 01Jun2025:00:03:01 Bbb 01Jun2025:00:02:10 01Jun2025:00:09:08 ; Sound like you want to remember the END timestamp from the first of the record in a block. You can use a new retained variable to do that. data want; set have; by section start; retain first_end; format first_end datetime19.; if first.section or (start > first_end) then do; group=1; first_end=end; end; else group+1; run; Results: Obs section start end first_end group 1 Aaa 01JUN2025:00:01:10 01JUN2025:00:09:02 01JUN2025:00:09:02 1 2 Aaa 01JUN2025:00:03:10 01JUN2025:00:12:00 01JUN2025:00:09:02 2 3 Aaa 01JUN2025:00:10:09 01JUN2025:00:18:08 01JUN2025:00:18:08 1 4 Bbb 01JUN2025:00:01:02 01JUN2025:00:03:01 01JUN2025:00:03:01 1 5 Bbb 01JUN2025:00:02:10 01JUN2025:00:09:08 01JUN2025:00:03:01 2

Tom

Macro programming is fun, but don't use it when it is not needed. Looks to me like you want to run this data step to create your two output datasets. data paths; infile datalines truncover; input path $200.; datalines; /dwh_actuariat/sasprocess/prodmens/HYFIprep/output /dwh_actuariat/sasprocess/prodmens/HYFIp/output ; %let prefix=HyFIPrems; %let substring=prep; data &prefix. &prefix._org ; set paths; do year=2025 to 2025 ; do month = 1 to 7 ; length fname $256 ; fname=catx('/',path,cats("&prefix",year,put(month,z2.),'.txt')); infile text filevar=fname truncover end=eof; do while(not eof); INPUT Cie $ 1 province $ 2-3 branch $ 4-7 priorcur $ 8 polsrc $ 9 mois $ 10-15 lob $ 16-23 Trans $ 24-26 WP 27-41 EP 42-56 EU 57-71 WU 72-86 POLCNT 87-102 POLEARN 103-117 UW_CIE $ 118 DIST_CIE $ 119 OB_IND $ 120-124 BROKER_GROUP_IND $ 125-126 GCNA_BRANCH $ 127-128 POLICY_CATEGORY $ 129 NETWORK_PROVIDER $ 130-134 AFFINITY_IND $ 135 ; if find(path,"&substring") then output &prefix.; else output &prefix._org ; end; end; end; run;

Tom

So are you saying you want help reading data that looks like the lines in your attached file? BTP04 Patients Mags 1=23 2=15 3=9 4=6 5=4 ___________________________________________________________________________________ ID Code fts |LOS|BH |pcE |pcEP|pcLP|pcSR|Spd |X| CONSm|Heal|pc| 1 xys H9| | | | | | | || | | | 322|11| 2 v3bwwwr15 m2| | | | 3 | 3 | 3 | || 3 | | 3| 95| 5| 3 qd23 H4| 3 | 2 | 3 | | 1 | 1 | 3 || 21 | | 2 1 3| 228| 3| 4 FRt5 m3| 2 | 3 | 2 | 1 | | | 1 || 23 | | 2 2 1| 142| 3| 5 p002Ft H4| | | | | | | || | | | 361| 9| 6 NNNz13 d1| 1 | 1 | 1 | 2 | 2 | 2 | 2 || 37 | | 3 1 | 48| 1| RRR02 Patients Mags 1=23 2=15 3=9 4=6 5=4 ___________________________________________________________________________________ ID Code fts |LOS|BH |pcE |pcEP|pcLP|pcSR|Spd||X| CONSm|Heal|pc| 1 qus H7| | | | | | | || || | 204|11| 2 w752 m3| | | | 3 | 3 | 3 | || 3 || 3| 95| 5| 3 rsss PT| 3 | 2 | 3 | | 1 | 1 | 3 || 21 || 2 1 3| 228| 3| 4 4562 m3| 2 | 3 | 2 | 1 | | | 1 || 23 || 2 2 1| 142| 3| 5 4862 H4| | | | | | | || || | 361| 9| 6 bVf6RW d1| 1 | 1 | 1 | 2 | 2 | 2 | 2 || 37 || 3 4 | 122| 1| 7 YyPpk8 M3| 2 | 3 | 2 | 1 | 1 | 2 | 3 || 922 || 2 2 3| 100|12| That looks like a pretty standard report file. I am not sure why there seem to be more columns of data than names in the header row, but hopefully you know what that means. To figure out what kind of line you are on so you know how to read the line take advantage of the trailing @ on the INPUT statement. To capture the information in the "by" line between the blocks BTP04 Patients Mags 1=23 2=15 3=9 4=6 5=4 take advantage of the RETAIN statement. Something like this should work: data want; infile text dlm=' |' truncover; length site $8 id 8; retain site; input id ?? @; if _infile_ = ' ' or _infile_ in: ('_' 'ID') then delete; if missing(id) then do; input @1 site ; delete; end; input code $ fts $; heal = input(scan(_infile_,-2,'|'),??32.); pc = input(scan(_infile_,-1,'|'),??32.); run; Results: Obs site id code fts heal pc 1 BTP04 1 xys H9 322 11 2 BTP04 2 v3bwwwr1 m2 95 5 3 BTP04 3 qd23 H4 228 3 4 BTP04 4 FRt5 m3 142 3 5 BTP04 5 p002Ft H4 361 9 6 BTP04 6 NNNz13 d1 48 1 7 RRR02 1 qus H7 204 11 8 RRR02 2 w752 m3 95 5 9 RRR02 3 rsss PT 228 3 10 RRR02 4 4562 m3 142 3 11 RRR02 5 4862 H4 361 9 12 RRR02 6 bVf6RW d1 122 1 13 RRR02 7 YyPpk8 M3 100 12

Tom

@alepage wrote: Well I want to use the macro process to passed as parameter the two different paths. Then I will replace the %put statement by another macro function that is loading all the txt files into that folder into a sas dataset. So skip the macro and the macro variables and just use a data step to generate the calls to the macro. So if your macro is named %MYMACRO() and takes the path as the first positional argument you could generate one call to the macro for each observation in the data PATHS with this data step: data _null_; set paths; call execute(cats('%nrstr(%mymacro)(',path,')')); run;

Tom

@alepage wrote: Why did you replace the %scan by %pscan and the '' by %str() and ne ) Please explain. Your script work perfectly %QSCAN() adds macro quoting to the path so that %EVAL() does not see the / as division symbols. You do not need to enclose strings in quotes for the macro processor. The reason you need the quotes in SAS code is so SAS knows what to treat as strings and what to treat as variable names or keywords. But to the macro processor everything is a string. So the quotes become part of the string.

Tom

Informats convert text to values. Formats convert values to text. data want; set have; string = put(1000*input(string,32.),best32.-L); run; Change HAVE , STRING and WANT to match the names of the datasets and variable you have. If STRING is shorter than 32 bytes long then adjust the format specification used with the PUT() function accordingly. You don't need to adjust the informat since the INPUT() function dose not care if the width used in the informat specification is larger than the length of the string being read.

Tom

Why did you tell SQL to use space as the delimiter and tell %SCAN() to also include single quote as a delimiter? And why did you compare the value to something that does contain single quote characters? That will never match as the result of the %SCAN() function could never contain any single quotes since they are being used as delimiters. Note since you want to use %SCAN() to split the macro variable it will work better to use some character that cannot appear in a path as the delimiter, such as a | character. Also SAS will not like those / characters in the strings generated by the %SCAN() call. PATH_LIST=/dwh_actuariat/sasprocess/prodmens/HYFIprep/output /dwh_actuariat/sasprocess/prodmens/HYFIp/output ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric operand is required. The condition was: %scan(&path_list, &i. , ) ne ERROR: The condition in the %DO %WHILE loop, %scan(&path_list, &i. , ) ne, yielded an invalid or missing value, . The macro will stop executing. ERROR: The macro PROCESS will stop executing. If you want to get your complex looping code to work make sure to use the right delimiter and compare to and actual empty value and use macro quoting to protect the value of the path from confusing the %EVAL() used by the %WHILE(). %do %while (%qscan(&path_list, &i. , %str( )) ne ); But why would you use such a complex loop when you can just use a NORMAL iterative do loop? %macro process; %local path_list i n current_path; proc sql noprint; select path into :path_list separated by '|' from paths; quit; %let n=&sqlobs; %do i=1 %to &n; %let current_path = %scan(&path_list, &i. ,|); %put &=i &=current_path; %end; %mend process; Results I=1 CURRENT_PATH=/dwh_actuariat/sasprocess/prodmens/HYFIprep/output I=2 CURRENT_PATH=/dwh_actuariat/sasprocess/prodmens/HYFIp/output And if you want to loop over a delimited list where you don't know the number of items in advance just count them. %do i=1 %to %sysfunc(countw(&path_list,|)); %let current_path = %scan(&path_list, &i. ,|); %put &=i &=current_path; %end;

Tom

@Ronein wrote: Sometimes there are some problems in data and have duplications. Is it better to select all rows in the query that create sas data set from tera table (not using distinct) and only then use sas proc sort nodupkey? I think the point @Kurt_Bremser was trying to make is that eliminating the duplicates can take time. That is especially true of a system like Teradata that tries to split the task across multiple modules running in parallel. To eliminate the duplicates it would need to have those modules communicate, thus reducing some (or all) of the performance gained by splitting up the task, even when there are no duplicates to be eliminated. So if you know there will not be duplicates then do not use the DISTINCT keyword so that Teradata knows it does not have to worry about eliminating them. Which is better depends on the performance of the two systems. But in general I would say if eliminating duplicates reduces the number of observations that need to move from Teradata to SAS then it is probably worth asking Teradata to do it. You could ask your local Teradata DBA for help with finding the best Teradata code to use.

Online Status	Offline
Date Last Visited	Monday