I only include the first data step so I had your dataset.  You can just start with the second step.   DATETIME and TIME values are in seconds.  You can easily convert them into hours using basic arithmetic. The only question is what to do with the minutes and seconds.  Do you want the HOURS to include the fraction of an hour?  Like 23.5 for '23:30:00't ? If not do you want to eliminate the fraction of an hour? hours=int(time/'01:00:00't); Do you want to round to the closest hour? hours=round(time/'01:00:00't); Do you want to round UP to the next hour? hours=ceil(time/'01:00:00't); ... View more

proc copy in=in out=out memtype=data; run; could only copy sas datasets from IN libname to OUT libname. ... View more

The most convenient and succinct way is using PROC IML.   data have; input abc def ghi jkl 8.; datalines; 1 4 2 3 6 99 0 8 3 99 6 5 2 4 0 99 ; run; proc iml; use have; read all var _all_ into x[c=vname]; close; idx=loc((x=99)[<>,]); want=vname[idx]; print want; quit;   ... View more

Hi @Patrick,   Yes I'm reading the CSV to a SAS table, however, that data of the CSV is a pipe enclosed with a comma delimiter. That is why I initially referenced the solution from Tom when reading the raw CSV. I added replaced the compress with the following code and it also worked:     ... View more

No, its still removes the observation if there are more than one transaction for an account. I've resorted to doing the below. It creates duplicates of observations that only have one transaction per account, but I'm just deduping by the "newkey" which is a unique id and that cleans it up just fine. So if anyone has any other ideas, I'm all ears. I appreciate the proc sql statement, exactly what I was looking for on that part. data newcalc balexception doadjustments; set want; by CONSUMERNO; if first. CONSUMERNO then do; if (bal + Total) ne EOD_BALANCE then output balexception; else output doadjustments; end; if last. CONSUMERNO then do; if (bal + Total) ne EOD_BALANCE then output balexception; else output doadjustments; end; run;   ... View more

In an earlier post in this thread you wrote: I am not addition another variable or row after this. And now you are requesting just that: @CathyVI wrote: if you can add an ID variable to the data and some will have 2 records, this will be nice.  You have also opened a second thread find the count of variable by group where, as far as I see, the same problem is being discussed.   In this situation I am not going to give another precise shot at a moving target, sorry, but suggest a general four-step approach: Aggregate the data to one observation per ID. Classify the aggregated observations into the categories of interest. Count the observations per category. Create a report from the counts. Example solutions for steps 2 (before aggregation, though) through 4 have been presented in this thread. Whether a DATA step, PROC SQL, PROC SUMMARY or other techniques are most appropriate for step 1 depends on the data structure and on the aggregation rules, neither of which we have seen entirely. For instance, if two records provide different pieces of information about forms that have been filled, their (e.g. chronological) order may or may not be relevant. ... View more

...and below how you could call the macro in case Region contains a comma separated list of values. /*real file will come from an Excel import*/ data driver; input if_statement $ 1-36 Region $ 37-60; datalines; if Make = 'BMW' then TEST2 = 'OK' 'Asia','USA' if Make = 'Ford' then TEST3 = 'OK' 'USA' 'Asia' if Make = 'Audi' then TEST1 = 'OK' 'Europe' 'Asia' 'USA' ; run; %macro makereport(Var=, Check=); data OUTPUT ; set SASHELP.CARS; where Origin IN (&Check); &Var; run; %mend makereport; data _null_; set driver; *builds string to execute macro; str = catt('%makereport(Var=',if_statement,',Check=%nrbquote(',Region,'));'); *execute macro; call execute(str); run; ... View more

Take advantage of using the IN operator with an array name to simplify the direct approach. data have; input (Subject old_var1-old_var4 new_var1-new_var4) ($); cards; 1 A C B . C D . . 2 X Z . . . . . . 3 C D X . X C D . ; data want; set have; array old old_var: new_var: ; array combine[8] $8 ; do i=1 to dim(old); if not missing(old[i]) then do; if old[i] not in combine then do; j=sum(j,1); combine[j]=old[i]; end; end; end; drop i j; run; Result   ... View more

@Patrick wrote: Your column CT_value is likely still of type numeric (Proc Contents would tell you). The value 4.00000000001E-3 is likely just scientific display of a numerical value. I'm rather sure that it is character because the values appear to be left-aligned in the Viewtable and also "4.0000000000000001E-3" (15 zeros) is impossible as a value of a numeric variable in SAS (under Windows). The same holds for "7.0000000000000007E-2" in NT_value.   While 4.0000000000000001E-3 means 0.004 + 1E-19 mathematically, the least significant bit in the internal representation of 0.004 has a place value of 2**-60=8.67...E-19. So, those 1E-19 (and even 4E-19, still less than half that place value) are rounded off, which means that an import as a numeric value should result in the clean value 0.004 automatically.   It's a bit surprising, though, that the numeric literal 7.0000000000000007E-2 (mathematically: 0.07+7E-18) in SAS is equal to 0.07 (on my Windows workstation, regardless of the DECIMALCONV system option setting) and not rounded up, although 7E-18 is greater than half the place value 2**-56=1.38...E-17 of the least significant bit. However, SAS correctly rounds the sum 0.07+7E-18 up and the sum 0.07+6E-18 down.   Your example 4.00000000001E-3 (0.004+1E-14) and the number 4.00000000000001E-3 (0.004+1E-17) mentioned by the OP are far enough apart from 0.004 so that, as you rightly suggest, explicit rounding would be required to obtain 0.004. ... View more

thx!! DM'LOG; CLEAR; OUT; CLEAR; ODSRESULTS; CLEAR;'; %LET FOLDER=%STR(C:\USERS\ALAIN\ONEDRIVE\桌面\HIGH); LIBNAME HIGH "&FOLDER"; DATA HH; SET HIGH.HH; IF CFACPR=0 THEN DELETE; RUN; PROC SORT DATA=HH (KEEP =SHRCD EXCHCD PERMNO DATE PRC CFACPR SHROUT CFACSHR WHERE =( SHRCD IN (10,11) AND EXCHCD IN (1,2,3,31,32,33) ) ) OUT=HL(DROP=SHRCD EXCHCD ); BY PERMNO DATE; RUN; DATA HL; SET HL; BY PERMNO DATE; RETAIN H L 0; P=ABS(DIVIDE (PRC, CFACPR)); IF FIRST.PERMNO THEN H = P; IF FIRST.PERMNO THEN L = P; H = MAX(H, P); L = MIN(L, P); DROP PRC CFACPR; RUN; /* PERMNO DATE SHROUT CFACSHR H L P */ DATA MK (DROP=_:); SET HL; BY PERMNO; RETAIN _FIRST_DATE_QUALIFIED . _H . _H_DATE . _L . _L_DATE . PORTFOLIO $11. ; IF FIRST.PERMNO THEN DO; CALL MISSING(OF _:); _FIRST_DATE_QUALIFIED=INTNX('YEAR', DATE, 1, 'SAME')+1; PORTFOLIO = ' '; END; IF _H=. THEN _H=P; IF _L=. THEN _L=P; IF P ^=. THEN DO; IF P > _H AND NOT (PORTFOLIO='MAX' AND DATE < INTNX('MONTH', _H_DATE, 1, 'SAME')) THEN DO; _H=P; _H_DATE=DATE; PORTFOLIO='MAX'; END; ELSE IF P < _L AND NOT (PORTFOLIO='MIN' AND DATE < INTNX('MONTH', _L_DATE, 1, 'SAME')) THEN DO; _L=P; _L_DATE=DATE; PORTFOLIO='MIN'; END; END; IF DATE >= _FIRST_DATE_QUALIFIED THEN DO; H=_H; H_DATE=_H_DATE; L=_L; L_DATE=_L_DATE; IF PORTFOLIO=' ' THEN PORTFOLIO='COMPARISON'; END; ELSE PORTFOLIO='UNQUALIFIED'; FORMAT H_DATE L_DATE YYMMDD10. ; RUN; DATA MK; SET MK(OBS=2000); PUT (_ALL_) (+0); RUN; ... View more

Looking at @yabwon 's code, I think it should be quadratic as you have 2 %do-loops with the same counter inside each other. Nice to see that theory and reality aligns. 🙂 And it still confirms the rather large penalty for using MFILE...   @Tom : Do you know something about the inner workings of the MFILE? ... View more

It matters a lot what type of file your "XPT" file is.  The XPT extension has no fixed definition.  SAS can create V5 XPORT files using the XPORT libref engine.  It can make CPORT files using PROC CPORT.  You can even now make a modified/enhanced version of the XPORT file format that supports V7/V8/V9 enhancements to the SAS dataset by using the SAS supplied %LOC2XPT() macro.   So what type of file do you have?  If you don't know you can use this macro to test your file: https://github.com/sasutils/macros/blob/master/xpttype.sas ... View more

Anyone know how I can use this and not output all of the FREQ of the variables? Thank you! ... View more

@mmaxiu wrote: Thanks, Koen! I tried the code you suggested, but what happens is that the merged table only contains the same number of rows as the smaller table, not the larger one.  Koen's code as posted could not subset the rows to be only the rows from the smaller table.  Please show the full log from running that step.  The log will show the actual code ran, the number of records read from each dataset, and the number of records written. ... View more