@Steelers_In_DC: It's true that informat ANYDTDTM11. can read time values of the form HH:MM:SS AM/PM, but your data step does not prove this. You are using modified list input with the default delimiter ' ' (blank). That is, your input statement reads "12:02:27", but not the "AM" after the blank. If you change the "AM" in your data line to "PM" (or to an invalid string such as "XY"), you will see that TIME will continue to show as 12:02:27 AM. You could use the "&" modifier (input time &;) or change the delimiter (infile cards dlm=',';) or use formatted input (input time anydtdtm11.;) to read the value correctly.   @fengyuwuzu: Your code is syntactically correct. Informat ANYDTTME. is a good choice to read values like 12:02:27 AM. (There is no date part to be read, hence no need to apply a datetime informat such as ANYDTDTM., although the result would be the same. You are also right not using a length specification, e.g. "11" in the informat. This would be ignored anyway in the reading process, as you don't use formatted input [cf. documentation of the INFORMAT statement].)   So, the reason why you obtain only missing values for the CreatedDT and EndDT variables will be found in your data. Please note that the ANYDTTME. informat is very tolerant: Unlike, e.g., the TIME. informat, it does not create "Invalid data ..." messages even if your time value is the string "blahblah". Could you please attach a single line (or a few lines) of your csv file with data, so we can see why it is not being read correctly?   Alternatively, you could temporarily replace anydttme. with time. in your INFORMAT statements and then post the log messages for your data step. The TIME. informat is also suitable to read values like 12:02:27 AM, but it's more likely to produce an "Invalid data ..." message, if the data are in fact invalid. ... View more

@chemph: Now you've got three solutions. If you go for a PROC SQL approach, I would recommend @Ksharp's, because it's more elegant than mine. (It lets PROC SQL perform the join of grouped and original data automatically.) Please note that his definition of TOS_CAT2_CD is slightly different from the definition used in the other two suggestions: He requires all observations with the same INT_CLM_NUM to have TOS_CAT1_CD='FAC_IP', whereas in the other approaches a single occurrence of 'FAC_IP' in that group of observations would trigger the "MATER" designation for the group. But this could be adapted easily by deleting "=count(*)" in the WHEN condition. Also, an ORDER BY clause (as in my code) could be added to sort the output dataset.   @Astounding's DOW loop approach would need only a minor adaptation if it were to work with grouped, but unsorted input data (like 888... before 777...): You would just need to add the NOTSORTED option to the two BY statements: by int_clm_num notsorted;. As a benefit, it would maintain the same sort order in the output dataset (unlike PROC SQL). ... View more

Would this be good? /* Create test data */ data tmp1; input INT_CLM_NUM :$15. CLM_LINE_NUM $ MAP_CD $ TOS_CAT1_CD $; cards; 123456789012345 0010 173 FAC_IP 123456789012345 0020 174 FAC_IP 123456789012345 0030 250 FAC_IP 123456789012345 0040 300 FAC_IP 123456789012345 0050 410 FAC_IP 123456789012346 0010 273 FAC_IP 123456789012346 0020 274 FAC_IP 123456789012347 0010 273 ABC_IP 123456789012347 0020 179 ABC_IP ; /* Create dataset with the desired additional variable */ proc sql; create table tmp1a as select a.*, coalesce(tmpvar, 'UNKNOWN') as TOS_CAT2_CD length=7 from tmp1 a left join (select distinct INT_CLM_NUM, 'MATER' as tmpvar from tmp1 where TOS_CAT1_CD = 'FAC_IP' and MAP_CD in ('170','171','172','173','174','175','179')) b on a.INT_CLM_NUM=b.INT_CLM_NUM order by a.INT_CLM_NUM, CLM_LINE_NUM; quit; proc print data=tmp1a; run; ... View more

@RW9: This is a nice technique to implement the first efficiency tip of the macro documentation: "use a macro only when necessary." Thank you very much for bringing this to my attention by similar posts in the recent past. (I had not been aware that CALL EXECUTE has been around since SAS 6.07.)   I suggest two minor corrections: Inserting length i $4; at the beginning would avoid the truncation of the second and third value of i to "R11". The variable name EMP in the SELECT statement should be replaced by '||strip(i)||'. ... View more

Hallo, Frank,   wenn TEST nach Y sortiert ist und X keine Missings enthält, lässt sich die Selektion m. E. noch weiter vereinfachen:   data cleaned; set test; by y; if x & (lag(x) | first.y); run; Ein Satz wird also genau dann ausgegeben, wenn X weder 0 noch missing ist und dies auch für das X des vorigen Satzes gilt, wobei diese Zusatzforderung für den jeweils ersten Satz einer Gruppe fallengelassen wird.   Viele Grüße   Reinhard ... View more

Hallo, Herr Müller,   ich denke, in vielen Fällen geht es auch ohne Makro. Hier ein Beispiel mit ARRAY-Deklaration, RETAIN-, PUT- und KEEP-Statement:   %let O=4; %let U=2; data test; array ay x&U-x&O; retain x&U-x&O 2016; put (x&U-x&O)(=); keep x&U-x&O; /* nur zur Demonstration */ run; Da die Variablenliste doch durchgehend nummeriert sein soll, kann man die Variablenlistenschreibweise "Xm-Xn" verwenden, braucht also keine %DO-Schleife. In der Array-Deklaration ergibt sich die Dimension des Arrays automatisch aus der Anzahl der Variablen in der Liste (hier: 3) und steht danach bei Bedarf auch als dim(ay) zur Verfügung. ... View more

The SAS 9.4 version of the documentation linked by @Reeza has been improved considerably compared to the earlier SAS versions: http://support.sas.com/documentation/cdl/en/lrcon/68089/HTML/default/viewer.htm#p0ji1unv6thm0dn1gp4t01a1u0g6.htm ... View more